透過move tablespace來完成resize datafile。
　　
　　HWM的概念就不在此闡述了（參考： http://afy.itpub.net/post/48/70865 ）。
　　
　　測試環境為Oracle10g for Linux，其它版本的一樣。
　　
　　我們先建立兩個表空間，分別為t_tbs和t_tbs1，分別有一個資料檔案，大小都是5M

　　
　　再建立一個test_user使用者，給這個使用者上述兩個表空間的無限限額，並且設定預設表空間是t_tbs。
　　
　　[zhangleyi@as zhangleyi]$ sqlplus / as sysdba
　　
　　SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 13 21:01:25 2004
　　
　　Copyright (c) 1982, 2004, Oracle.　All rights reserved.
　　
　　Connected to:
　　Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
　　With the Partitioning, OLAP and Data Mining options
　　
　　SYS at orcl10>alter user test_user default tablespace t_tbs;
　　
　　User altered.
　　
　　SYS at orcl10>alter user test_user quota unlimited on t_tbs;
　　
　　User altered.
　　
　　SYS at orcl10>alter user test_user quota unlimited on t_tbs1;
　　
　　User altered
　　
　　用test_user登入，建立表
　　
　　TEST_USER at orcl10>create table t_obj as select * from dba_objects where rownuminsert into t_obj select * from t_obj;
　　
　　9999 rows created.
　　
　　TEST_USER at orcl10>/
　　
　　19998 rows created.
　　
　　TEST_USER at orcl10>/
　　insert into t_obj select * from t_obj
　　*
　　ERROR at line 1:
　　ORA-01653: unable to extend table TEST_USER.T_OBJ by 128 in tablespace T_TBS
　　
　　TEST_USER at orcl10>commit;
　　
　　Commit complete.
　　
　　TEST_USER at orcl10>select sum(blocks) Total Blocks,sum(bytes) Total Size from dba_extents where owner=TEST_USER and segment_name=T_OBJ;
　　
　　Total Blocks Total Size
　　------------ ----------
　　　　 512　　4194304
　　
　　好，上面我們建立了一個表，並且插入了很多資料，透過dba_extents檢視我們可以看到總共用的block數和總共的大小。
　　
　　下面我們用delete刪除全部資料，並且插入新的9999條資料
　　
　　TEST_USER at orcl10>delete from t_obj;
　　
　　39996 rows deleted.
　　
　　TEST_USER at orcl10>insert into t_obj select * from dba_objects where rownumcommit;
　　
　　Commit complete.
　　
　　TEST_USER at orcl10>select sum(blocks) Total Blocks,sum(bytes) Total Size from dba_extents
　　　2　where owner=TEST_USER and segment_name=T_OBJ;
　　
　　Total Blocks Total Size
　　------------ ----------
　　　　 512　　4194304
　　
　　再次檢視dba_extents檢視，發現佔用的空間並沒有減少。
　　
　　我們嘗試resize這個資料檔案，file#為6的是t_tbs表空間下面的資料檔案
　　
　　SYS at orcl10>alter database datafile 6 resize 4M;
　　alter database datafile 6 resize 4M
　　*
　　ERROR at line 1:
　　ORA-03297: file contains used data beyond requested RESIZE value
　　
　　SYS at orcl10>alter database datafile 6 resize 4500000;
　　
　　Database altered.
　　
　　我們發現想resize到4M不可以，但是resize到4500000就可以了，因為上面檢視出來的Total Size是4194304，這個值大於4M而小於4500000。
　　
　　然後我們move這張表到t_tbs1表空間，這個表空間下面的資料檔案file#是8
　　
　　EST_USER at orcl10>alter table t_obj move tablespace t_tbs1;
　　
　　Table altered.
　　
　　TEST_USER at orcl10>select sum(blocks) Total Blocks,sum(bytes) Total Size from dba_extents
　　　2　where owner=TEST_USER and segment_name=T_OBJ;
　　
　　Total Blocks Total Size
　　------------ ----------
　　　　 128　　1048576
　　
　　我們檢查dba_extents檢視，發現Total Size已經變化了，此時已經可以說明move表是會重新進行block的整理的，同時也重置了HWM。
　　
　　下面我們resize這個資料檔案。
　　SYS at orcl10>alter database datafile 8 resize 3M;
　　
　　Database altered.
　　
　　SYS at orcl10>host
　　[zhangleyi@as ORCL10]$ cd /oracle/oradata/ORCL10/datafile/
　　[zhangleyi@as datafile]$ ls -l
　　總用量 1419076
　　-rw-r-----　　1 zhangleyi dba　　　20979712　4月 13 21:17 cattbs01.dbf
　　-rw-r-----　　1 zhangleyi dba　　　157294592　4月 13 21:17 o1_mf_example_02p0gpoj_.dbf
　　-rw-r-----　　1 zhangleyi dba　　　419438592　4月 13 21:20 o1_mf_sysaux_02p09kny_.dbf
　　-rw-r-----　　1 zhangleyi dba　　　555753472　4月 13 21:17 o1_mf_system_02p09kno_.dbf
　　-rw-r-----　　1 zhangleyi dba　　　20979712　4月 13 21:02 o1_mf_temp_02p0fzsd_.tmp
　　-rw-r-----　　1 zhangleyi dba　　　62922752　4月 13 21:20 o1_mf_undotbs1_02p09kog_.dbf
　　-rw-r-----　　1 zhangleyi dba　　　209723392　4月 13 21:17 o1_mf_users_02p09kqv_.dbf
　　-rw-r-----　　1 zhangleyi dba　　　 3153920　4月 13 21:21 TEST01.DBF
　　-rw-r-----　　1 zhangleyi dba　　　 4513792　4月 13 21:20 test.dbf
　　
　　可以看到我們的目的已經達到了。
　　
　　在真實應用中，我們可以將一個表空間中的所有object，全部move到一個新的表空間中，然後drop掉原來的表空間，再從磁碟上刪除原來表空間中的資料檔案。
　　
　　至於如何得知HWM，我們可以透過analyze之後的資料字典得到，那麼如果不進行analyze的話，我們也可以執行下面這個指令碼。
　　
　　這個指令碼可以用於檢查一個object佔有的總共block數和處於HWM之上的block數，這當然也就知道了HWM是在什麼位置。
　　
　　DECLARE
　　v_total_blocks NUMBER;
　　v_total_bytes NUMBER;
　　v_unused_blocks NUMBER;
　　v_unused_bytes NUMBER;
　　v_last_used_extent_file_id NUMBER;
　　v_last_used_extent_block_id NUMBER;
　　v_last_used_block NUMBER;
　　BEGIN
　　dbms_space.unused_space(SCOTT,BIGEMP,TABLE,v_total_blocks,v_total_bytes,v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id,v_last_used_extent_block_id,v_last_used_block);
　　dbms_output.put_line(Total Blocks: ||TO_CHAR(v_total_blocks));
　　dbms_output.put_line(Blocks above HWM: ||TO_CHAR(v_unused_blocks));
　　END;
　　/
　　
　　Total Blocks: 256
　　Blocks above HWM: 0
　　
　　PL/SQL procedure successfully completed
　　
　　Executed in 0.01 seconds

Oracle中移動表 -- 達到縮小資料檔案大小的目的

相關文章