表空間（資料檔案shrink）收縮示例
正常的資料庫管理中會碰到空間不足的情況，一些表空間因為總的磁碟空間有限影響了其正常的擴充套件，但還有些表空間空餘空間很大卻不能分給別的表空間使用，這裡進行一個表空間的收縮測試。
以收縮資料檔案方式為例，吧資料檔案裡空餘的空間shrink出來給磁碟，供其他表空間擴充套件使用。
參考
#################################################################################################################
測試環境oracle10g2
表空間收縮測試
建立測試表空間test
分配3個資料檔案,每個資料檔案100M
SQL> create tablespace test datafile 'D:\oracle\product\10.2.0\rldb\rldb\test01.dbf' size 100M;
表空間已建立。
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test02.dbf' size 100M;
表空間已更改。
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test03.dbf' size 100M;
表空間已更改。
--------------------------------------------------------------------------------------------------------
建立測試用的表5張
SQL> create table test1 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test2 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test3 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test4 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test5 tablespace TEST as select * from dba_objects;
表已建立。
表1：
SQL> insert into test1 select * from test1;
已建立50316行。
SQL> /
已建立100632行。
SQL> /
已建立201264行。
SQL> commit;
提交完成。
-----------------------------------------------------------------------------------------------
查詢test表空間用量，每個資料檔案用23M左右，加起來有80M左右。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
8 76.9375
6 76.9375
7 77.9375
採集該表資訊
SQL> exec dbms_stats.gather_table_stats(user,'test1',estimate_percent=>100);
PL/SQL 過程已成功完成。
查詢表的行數和記錄長度用的塊多少。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 402560 5556
表2：
SQL> insert into test2 select * from test1;
已建立402560行。
SQL> insert into test2 select * from test2;
已建立452881行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
8 48.9375
6 41.9375
7 42.9375
SQL> exec dbms_stats.gather_table_stats(user,'test2',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 905762 12665
表3：
SQL> insert into test3 select * from test2;
已建立905762行。
SQL> insert into test3 select * from test1;
insert into test3 select * from test1
*
第 1 行出現錯誤:
ORA-01653: 表 SYS.TEST3 無法透過 1024 (在表空間 TEST 中) 擴充套件
表空間不夠了，新增加記錄失敗，先提交前面插入的資料。
SQL> commit;
提交完成。
增加表空間
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test04.dbf' size 100M;
表空間已更改。
SQL> insert into test3 select * from test1;
已建立402560行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='T
EST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 6.9375
7 7.9375
8 4.9375
9 67.9375
SQL> exec dbms_stats.gather_table_stats(user,'test3',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3
';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 1358644 18785
表4；
SQL> insert into test4 select * from test1;
已建立402560行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 40.9375
SQL> exec dbms_stats.gather_table_stats(user,'test4',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452883 6248
表5：
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test05.dbf' size 100M;
表空間已更改。
SQL> insert into test5 select * from test1;
已建立402560行。
SQL> insert into test5 select * from test1;
已建立402560行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test5',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 855444 17257
至此已經創立5個表和5個資料檔案，我們檢查一下每個資料檔案上分佈的資料段（表）的情況。
先查詢表空間裡的物件，可以看出有的表已經大於100M（單個資料檔案
SQL> select owner,segment_name,segment_type,bytes/1024/1024 as Mbtys from dba_se
gments where tablespace_name='TEST';
OWNER SEGMENT_NA SEGMENT_TYPE MBTYS
---------- ---------- ------------------ ----------
SYS TEST1 TABLE 44
SYS TEST2 TABLE 104
SYS TEST3 TABLE 152
SYS TEST4 TABLE 50
SYS TEST5 TABLE 136
檢視資料檔案的裡包含的段及其大小
SQL> select SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id = 6
3 group by SEGMENT_NAME,PARTITION_NAME
4 order by segment_name;
SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ------------------------------ -----------
TEST1 1920
TEST2 4736
TEST3 4736
TEST4 1024
TEST5 256
SQL> select file_id,SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id in (6,7,8,9,10)
3 group by file_id,SEGMENT_NAME,PARTITION_NAME
4 order by file_id,SEGMENT_NAME;
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
6 TEST1 1920
6 TEST2 4736
6 TEST3 4736
6 TEST4 1024
6 TEST5 256
7 TEST1 1792
7 TEST2 4736
7 TEST3 4736
7 TEST4 1152
7 TEST5 256
8 TEST1 1920
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
8 TEST2 3840
8 TEST3 5888
8 TEST4 768
8 TEST5 256
9 TEST3 4096
9 TEST4 3456
9 TEST5 4736
10 TEST5 11904
已選擇19行。
現在刪除5張表的一部分資料，造成一些資料碎片。
表1：
SQL> delete test1 where mod(object_id,3) in (0,1);
已刪除268600行。
SQL> commit;
提交完成。
SQL> select count(*) from test1;
COUNT(*)
----------
133960
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test1',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL>
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 5556
表2：
SQL> delete test2 where mod(object_id,3) in (0,1);
已刪除604350行。
SQL> commit;
提交完成。
SQL> select count(*) from test2;
COUNT(*)
----------
301412
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test2',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 12665
表3：
SQL> delete test3 where mod(object_id,3) in (0,1);
已刪除906526行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test3',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 18785
表4：
SQL> delete test4 where mod(object_id,3) in (0,1);
已刪除302177行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test4',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 6248
表5：
SQL> delete test5 where mod(object_id,3) in (0,1);
已刪除570777行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test5',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 17257
檢查發現，雖然表刪除了資料但是，總體來看錶空間使用量並未減少。
SQL> select a.tablespace_name,total,free,total-free used, (total-free)/total*100
"USED(%)" from
2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
3 group by tablespace_name) a,
4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
5 group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7 order by (total-free)/total*100 desc ;
TABLESPACE_NAME TOTAL FREE USED USED(%)
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 240 .0625 239.9375 99.9739583
SYSTEM 480 3.25 476.75 99.3229167
UNDOTBS1 430 6.5625 423.4375 98.4738372
TEST 500 13.6875 486.3125 97.2625
USERS 5 1.75 3.25 65
EXAMPLE 146.875 69.1875 77.6875 52.893617
已選擇6行。
資料檔案一直保持原有的使用
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
以下把刪除資料前和資料後的資料庫使用對比如下：
表1
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 402560 5556
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 5556
使用塊數不變，但是行數減少。
表2；
刪除前：
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 905762 12665
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 12665
表3：
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 1358644 18785
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 18785
表4
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452883 6248
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 6248
表5：
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 855444 17257
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 17257
看得出來，刪除之後資料庫並沒有釋放。
SQL> select file_id,SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id in (6,7,8,9,10)
3 group by file_id,SEGMENT_NAME,PARTITION_NAME
4 order by file_id,SEGMENT_NAME;
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
6 TEST1 1920
6 TEST2 4736
6 TEST3 4736
6 TEST4 1024
6 TEST5 256
7 TEST1 1792
7 TEST2 4736
7 TEST3 4736
7 TEST4 1152
7 TEST5 256
8 TEST1 1920
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
8 TEST2 3840
8 TEST3 5888
8 TEST4 768
8 TEST5 256
9 TEST3 4096
9 TEST4 3456
9 TEST5 4736
10 TEST5 11904
已選擇19行。
SQL> /
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
6 TEST1 1920
6 TEST2 4736
6 TEST3 4736
6 TEST4 1024
6 TEST5 256
7 TEST1 1792
7 TEST2 4736
7 TEST3 4736
7 TEST4 1152
7 TEST5 256
8 TEST1 1920
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
8 TEST2 3840
8 TEST3 5888
8 TEST4 768
8 TEST5 256
9 TEST3 4096
9 TEST4 3456
9 TEST5 4736
10 TEST5 11904
已選擇19行。
現在查詢可收回的空間有多少：
SQL> select FILE_ID,BLOCK_ID,shrink_mb
2 from (select FILE_ID,BLOCK_ID,BYTES/1024/1024 shrink_mb,
3 row_number() over(partition by file_id order by BLOCK_ID desc) rn
4 from dba_free_space where file_id in (6,7,8,9,10) ) t
5 where rn =1 ;
FILE_ID BLOCK_ID SHRINK_MB
---------- ---------- ----------
6 12681 .9375
7 12681 .9375
8 12681 .9375
9 12297 3.9375
10 11913 6.9375
和剩餘的資料檔案空間一樣，雖然刪除了但是並沒有可以收回的空間，因為佔用的資料塊並沒有釋放。
查詢每個資料檔案空閒塊和起始位置
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id in (6,7,8,9,10)
4 order by 1;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
6 12681 12800 120
7 12681 12800 120
8 12681 12800 120
9 12297 12800 504
10 11913 12800 888
先對資料段（表）進行整理，收縮這些表。
表1：
SQL> alter table test1 enable row movement;
表已更改。
SQL> alter table test1 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST1',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 1652
對比以前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 5556
表2：
SQL> alter table test2 enable row movement;
表已更改。
SQL> alter table test2 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST2',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 3711
對比之前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 12665
表3：
SQL> alter table test3 enable row movement;
表已更改。
SQL> alter table test3 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST3',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 5565
對比之前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 18785
表4：
SQL> alter table test4 enable row movement;
表已更改。
SQL> alter table test4 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST4',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 1858
對比之前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 6248
表5：
SQL> alter table test5 enable row movement;
表已更改。
SQL> alter table test5 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST5',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 3505
對比之前資料快減少了，空間收縮了
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 17257
5張表都收縮完了，
以下是每個資料檔案資料庫空閒和起始位置
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=6;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
6 1569 2952 1384
6 3977 7432 3456
6 9097 11912 2816
6 12169 12800 632
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=7;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
7 1545 2824 1280
7 3793 7304 3512
7 8873 11784 2912
7 12041 12800 760
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=8;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
8 1673 2952 1280
8 3977 6536 2560
8 8201 12168 3968
8 12529 12800 272
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=9;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
9 9 4104 4096
9 4361 7560 3200
9 8969 12800 3832
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=10;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
10 1409 12800 11392
可以看得出來空閒資料塊都不連續。
我們看一下表空間的資料檔案的空餘使用量。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 10.8125
6 27
6 22
6 4.9375
7 10
7 27.4375
7 22.75
7 5.9375
8 10
8 20
8 31
FILE_ID BYTES/1024/1024
---------- ---------------
8 2.125
9 32
9 25
9 29.9375
10 89
已選擇16行。
可以看出空餘的量很多了，但是都是分散的。
檢視每個資料檔案裡表佔用的資料塊
SQL> select file_id,SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id in (6,7,8,9,10)
3 group by file_id,SEGMENT_NAME,PARTITION_NAME
4 order by file_id,SEGMENT_NAME;
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
6 TEST1 536
6 TEST2 1280
6 TEST3 1920
6 TEST4 512
6 TEST5 256
7 TEST1 512
7 TEST2 1224
7 TEST3 1824
7 TEST4 512
7 TEST5 256
8 TEST1 640
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
8 TEST2 1280
8 TEST3 1920
8 TEST4 616
8 TEST5 256
9 TEST4 256
9 TEST5 1408
10 TEST5 1400
已選擇18行。
計算檢視每個資料檔案可以收回的空間大小
SQL>
SQL> select FILE_ID,BLOCK_ID,shrink_mb
2 from (select FILE_ID,BLOCK_ID,BYTES/1024/1024 shrink_mb,
3 row_number() over(partition by file_id order by BLOCK_ID desc) rn
4 from dba_free_space where file_id in (6,7,8,9,10) ) t
5 where rn =1 ;
FILE_ID BLOCK_ID SHRINK_MB
---------- ---------- ----------
6 12169 4.9375
7 12041 5.9375
8 12529 2.125
9 8969 29.9375
10 1409 89
除了檔案10外其他收回的量都比較小，這個應該是因為資料檔案高水位線的原因，資料檔案只能收縮最高水位線以上的資料塊，在檔案最高水位線以下各個分佈的空閒塊無法收回。
問題來了，怎麼能讓資料檔案裡高水位線以下的很多空閒資料庫合在一起呢？讓他們連續呢？
網上看到的一個計算生成收縮資料檔案的查詢。
SQL> select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
2 ceil(HWM * a.block_size)/1024/1024 ResizeTo,
3 (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
4 'alter database datafile '''||a.name||''' resize '||
5 ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
6 from v$datafile a,
7 (select file_id,max(block_id+blocks-1) HWM
8 from dba_extents
9 group by file_id) b
10 where a.file# = b.file_id(+)
11 and (a.bytes - HWM *block_size)>0
12 order by 5 ;
FILE# NAME CURRENTMB RESIZETO RELEASEMB RESIZECMD
-----------------------------------------------------------------------------------------------------------------------------------------------
4 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\USERS01.DBF 5 3.251.75 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\USERS01.DBF' resize 4M;
8 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF 100 97.8752.125 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF' resize 98M;
1 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSTEM01.DBF 480 477.06252.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSTEM01.DBF' resize 478M;
6 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF 100 95.06254.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF' resize 96M;
7 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF 100 94.06255.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF' resize 95M;
3 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSAUX01.DBF 250 240.43759.5625 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSAUX01.DBF' resize 241M;
9 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF 100 70.062529.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF' resize 71M;
5 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\EXAMPLE01.DBF 146.875 80.187566.6875 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\EXAMPLE01.DBF' resize 81M;
10 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF 100 1189 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF' resize 11M;
已選擇9行。
如果只是想對某個表個間的datafile resize,可採用:
SQL> select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
2 ceil(HWM * a.block_size)/1024/1024 ResizeTo,
3 (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
4 'alter database datafile '''||a.name||''' resize '||
5 ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
6 from v$datafile a,
7 (select file_id,max(block_id+blocks-1) HWM
8 from dba_extents where file_id in
9 (select b.file# From v$tablespace a ,v$datafile b
10 where a.ts#=b.ts# and a.name='TEST')
11 group by file_id) b
12 where a.file# = b.file_id(+)
13 and (a.bytes - HWM *block_size)>0
14 order by 5
15 ;
FILE# NAME CURRENTMB RESIZETO RELEASEMB RESIZECMD
8 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF 100 97.8752.125 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF' resize 98M;
6 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF 100 95.06254.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF' resize 96M;
7 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF 100 94.06255.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF' resize 95M;
9 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF 100 70.062529.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF' resize 71M;
10 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF 100 1189 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF' resize 11M;
根據以上生產的執行語句執行對資料檔案的收縮操作
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF' resize 98M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF' resize 96M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF' resize 95M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF' resize 71M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF' resize 11M;
資料庫已更改。
收縮成功無報錯
檢視資料檔案大小
SQL> select file_id,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_ID FILE_NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
6 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF 96
7 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF 95
8 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF 98
9 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF 71
10 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF 11
資料檔案成功收縮至目標大小。
檢視資料正常
SQL> select count(*) from test1;
COUNT(*)
----------
133960
SQL> select count(*) from test2;
COUNT(*)
----------
301412
SQL> select count(*) from test3;
COUNT(*)
----------
452118
SQL> select count(*) from test4;
COUNT(*)
----------
150706
SQL> select count(*) from test5;
COUNT(*)
----------
284667
至此測試完成，資料檔案收縮完畢。
結論，資料檔案收縮大大小取決於高水位線（HWM）的位置，前期對資料表檔案裡所在表的shrink有一定的作用，但是如果表shrink的部分在高水位線一下則無明顯效果。
####################################################################################################################################
以收縮資料檔案方式為例，吧資料檔案裡空餘的空間shrink出來給磁碟，供其他表空間擴充套件使用。
參考
#################################################################################################################
測試環境oracle10g2
表空間收縮測試
建立測試表空間test
分配3個資料檔案,每個資料檔案100M
SQL> create tablespace test datafile 'D:\oracle\product\10.2.0\rldb\rldb\test01.dbf' size 100M;
表空間已建立。
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test02.dbf' size 100M;
表空間已更改。
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test03.dbf' size 100M;
表空間已更改。
--------------------------------------------------------------------------------------------------------
建立測試用的表5張
SQL> create table test1 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test2 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test3 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test4 tablespace TEST as select * from dba_objects;
表已建立。
SQL> create table test5 tablespace TEST as select * from dba_objects;
表已建立。
表1：
SQL> insert into test1 select * from test1;
已建立50316行。
SQL> /
已建立100632行。
SQL> /
已建立201264行。
SQL> commit;
提交完成。
-----------------------------------------------------------------------------------------------
查詢test表空間用量，每個資料檔案用23M左右，加起來有80M左右。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
8 76.9375
6 76.9375
7 77.9375
採集該表資訊
SQL> exec dbms_stats.gather_table_stats(user,'test1',estimate_percent=>100);
PL/SQL 過程已成功完成。
查詢表的行數和記錄長度用的塊多少。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 402560 5556
表2：
SQL> insert into test2 select * from test1;
已建立402560行。
SQL> insert into test2 select * from test2;
已建立452881行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
8 48.9375
6 41.9375
7 42.9375
SQL> exec dbms_stats.gather_table_stats(user,'test2',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 905762 12665
表3：
SQL> insert into test3 select * from test2;
已建立905762行。
SQL> insert into test3 select * from test1;
insert into test3 select * from test1
*
第 1 行出現錯誤:
ORA-01653: 表 SYS.TEST3 無法透過 1024 (在表空間 TEST 中) 擴充套件
表空間不夠了，新增加記錄失敗，先提交前面插入的資料。
SQL> commit;
提交完成。
增加表空間
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test04.dbf' size 100M;
表空間已更改。
SQL> insert into test3 select * from test1;
已建立402560行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='T
EST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 6.9375
7 7.9375
8 4.9375
9 67.9375
SQL> exec dbms_stats.gather_table_stats(user,'test3',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3
';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 1358644 18785
表4；
SQL> insert into test4 select * from test1;
已建立402560行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 40.9375
SQL> exec dbms_stats.gather_table_stats(user,'test4',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452883 6248
表5：
SQL> alter tablespace test add datafile 'D:\oracle\product\10.2.0\rldb\rldb\test05.dbf' size 100M;
表空間已更改。
SQL> insert into test5 select * from test1;
已建立402560行。
SQL> insert into test5 select * from test1;
已建立402560行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test5',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 855444 17257
至此已經創立5個表和5個資料檔案，我們檢查一下每個資料檔案上分佈的資料段（表）的情況。
先查詢表空間裡的物件，可以看出有的表已經大於100M（單個資料檔案
SQL> select owner,segment_name,segment_type,bytes/1024/1024 as Mbtys from dba_se
gments where tablespace_name='TEST';
OWNER SEGMENT_NA SEGMENT_TYPE MBTYS
---------- ---------- ------------------ ----------
SYS TEST1 TABLE 44
SYS TEST2 TABLE 104
SYS TEST3 TABLE 152
SYS TEST4 TABLE 50
SYS TEST5 TABLE 136
檢視資料檔案的裡包含的段及其大小
SQL> select SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id = 6
3 group by SEGMENT_NAME,PARTITION_NAME
4 order by segment_name;
SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ------------------------------ -----------
TEST1 1920
TEST2 4736
TEST3 4736
TEST4 1024
TEST5 256
SQL> select file_id,SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id in (6,7,8,9,10)
3 group by file_id,SEGMENT_NAME,PARTITION_NAME
4 order by file_id,SEGMENT_NAME;
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
6 TEST1 1920
6 TEST2 4736
6 TEST3 4736
6 TEST4 1024
6 TEST5 256
7 TEST1 1792
7 TEST2 4736
7 TEST3 4736
7 TEST4 1152
7 TEST5 256
8 TEST1 1920
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
8 TEST2 3840
8 TEST3 5888
8 TEST4 768
8 TEST5 256
9 TEST3 4096
9 TEST4 3456
9 TEST5 4736
10 TEST5 11904
已選擇19行。
現在刪除5張表的一部分資料，造成一些資料碎片。
表1：
SQL> delete test1 where mod(object_id,3) in (0,1);
已刪除268600行。
SQL> commit;
提交完成。
SQL> select count(*) from test1;
COUNT(*)
----------
133960
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test1',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL>
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 5556
表2：
SQL> delete test2 where mod(object_id,3) in (0,1);
已刪除604350行。
SQL> commit;
提交完成。
SQL> select count(*) from test2;
COUNT(*)
----------
301412
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test2',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 12665
表3：
SQL> delete test3 where mod(object_id,3) in (0,1);
已刪除906526行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test3',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 18785
表4：
SQL> delete test4 where mod(object_id,3) in (0,1);
已刪除302177行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test4',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 6248
表5：
SQL> delete test5 where mod(object_id,3) in (0,1);
已刪除570777行。
SQL> commit;
提交完成。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
SQL> exec dbms_stats.gather_table_stats(user,'test5',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 17257
檢查發現，雖然表刪除了資料但是，總體來看錶空間使用量並未減少。
SQL> select a.tablespace_name,total,free,total-free used, (total-free)/total*100
"USED(%)" from
2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
3 group by tablespace_name) a,
4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
5 group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name
7 order by (total-free)/total*100 desc ;
TABLESPACE_NAME TOTAL FREE USED USED(%)
------------------------------ ---------- ---------- ---------- ----------
SYSAUX 240 .0625 239.9375 99.9739583
SYSTEM 480 3.25 476.75 99.3229167
UNDOTBS1 430 6.5625 423.4375 98.4738372
TEST 500 13.6875 486.3125 97.2625
USERS 5 1.75 3.25 65
EXAMPLE 146.875 69.1875 77.6875 52.893617
已選擇6行。
資料檔案一直保持原有的使用
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 .9375
7 .9375
8 .9375
9 3.9375
10 6.9375
以下把刪除資料前和資料後的資料庫使用對比如下：
表1
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 402560 5556
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 5556
使用塊數不變，但是行數減少。
表2；
刪除前：
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 905762 12665
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 12665
表3：
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 1358644 18785
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 18785
表4
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452883 6248
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 6248
表5：
刪除前
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 855444 17257
刪除後
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 17257
看得出來，刪除之後資料庫並沒有釋放。
SQL> select file_id,SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id in (6,7,8,9,10)
3 group by file_id,SEGMENT_NAME,PARTITION_NAME
4 order by file_id,SEGMENT_NAME;
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
6 TEST1 1920
6 TEST2 4736
6 TEST3 4736
6 TEST4 1024
6 TEST5 256
7 TEST1 1792
7 TEST2 4736
7 TEST3 4736
7 TEST4 1152
7 TEST5 256
8 TEST1 1920
FILE_ID SEGMENT_NA PARTITION_NAME SUM(BLOCKS)
---------- ---------- ------------------------------ -----------
8 TEST2 3840
8 TEST3 5888
8 TEST4 768
8 TEST5 256
9 TEST3 4096
9 TEST4 3456
9 TEST5 4736
10 TEST5 11904
已選擇19行。
SQL> /
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
6 TEST1 1920
6 TEST2 4736
6 TEST3 4736
6 TEST4 1024
6 TEST5 256
7 TEST1 1792
7 TEST2 4736
7 TEST3 4736
7 TEST4 1152
7 TEST5 256
8 TEST1 1920
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
8 TEST2 3840
8 TEST3 5888
8 TEST4 768
8 TEST5 256
9 TEST3 4096
9 TEST4 3456
9 TEST5 4736
10 TEST5 11904
已選擇19行。
現在查詢可收回的空間有多少：
SQL> select FILE_ID,BLOCK_ID,shrink_mb
2 from (select FILE_ID,BLOCK_ID,BYTES/1024/1024 shrink_mb,
3 row_number() over(partition by file_id order by BLOCK_ID desc) rn
4 from dba_free_space where file_id in (6,7,8,9,10) ) t
5 where rn =1 ;
FILE_ID BLOCK_ID SHRINK_MB
---------- ---------- ----------
6 12681 .9375
7 12681 .9375
8 12681 .9375
9 12297 3.9375
10 11913 6.9375
和剩餘的資料檔案空間一樣，雖然刪除了但是並沒有可以收回的空間，因為佔用的資料塊並沒有釋放。
查詢每個資料檔案空閒塊和起始位置
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id in (6,7,8,9,10)
4 order by 1;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
6 12681 12800 120
7 12681 12800 120
8 12681 12800 120
9 12297 12800 504
10 11913 12800 888
先對資料段（表）進行整理，收縮這些表。
表1：
SQL> alter table test1 enable row movement;
表已更改。
SQL> alter table test1 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST1',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 1652
對比以前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST1';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 133960 5556
表2：
SQL> alter table test2 enable row movement;
表已更改。
SQL> alter table test2 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST2',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 3711
對比之前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST2';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 301412 12665
表3：
SQL> alter table test3 enable row movement;
表已更改。
SQL> alter table test3 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST3',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 5565
對比之前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST3';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 452118 18785
表4：
SQL> alter table test4 enable row movement;
表已更改。
SQL> alter table test4 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST4',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 1858
對比之前資料快減少了，空間收縮了。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST4';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 150706 6248
表5：
SQL> alter table test5 enable row movement;
表已更改。
SQL> alter table test5 shrink space;
表已更改。
SQL> exec dbms_stats.gather_table_stats(user,'TEST5',estimate_percent=>100);
PL/SQL 過程已成功完成。
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 3505
對比之前資料快減少了，空間收縮了
SQL> select avg_row_len,num_rows,blocks from user_tables where table_name='TEST5';
AVG_ROW_LEN NUM_ROWS BLOCKS
----------- ---------- ----------
93 284667 17257
5張表都收縮完了，
以下是每個資料檔案資料庫空閒和起始位置
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=6;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
6 1569 2952 1384
6 3977 7432 3456
6 9097 11912 2816
6 12169 12800 632
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=7;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
7 1545 2824 1280
7 3793 7304 3512
7 8873 11784 2912
7 12041 12800 760
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=8;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
8 1673 2952 1280
8 3977 6536 2560
8 8201 12168 3968
8 12529 12800 272
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=9;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
9 9 4104 4096
9 4361 7560 3200
9 8969 12800 3832
SQL> select FILE_ID,BLOCK_ID start_block,BLOCK_ID+BLOCKS-1 end_block,BLOCKS
2 from dba_free_space
3 where file_id=10;
FILE_ID START_BLOCK END_BLOCK BLOCKS
---------- ----------- ---------- ----------
10 1409 12800 11392
可以看得出來空閒資料塊都不連續。
我們看一下表空間的資料檔案的空餘使用量。
SQL> select file_id,bytes/1024/1024 from dba_free_space where tablespace_name='TEST';
FILE_ID BYTES/1024/1024
---------- ---------------
6 10.8125
6 27
6 22
6 4.9375
7 10
7 27.4375
7 22.75
7 5.9375
8 10
8 20
8 31
FILE_ID BYTES/1024/1024
---------- ---------------
8 2.125
9 32
9 25
9 29.9375
10 89
已選擇16行。
可以看出空餘的量很多了，但是都是分散的。
檢視每個資料檔案裡表佔用的資料塊
SQL> select file_id,SEGMENT_NAME,PARTITION_NAME,sum(blocks)
2 from dba_extents where file_id in (6,7,8,9,10)
3 group by file_id,SEGMENT_NAME,PARTITION_NAME
4 order by file_id,SEGMENT_NAME;
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
6 TEST1 536
6 TEST2 1280
6 TEST3 1920
6 TEST4 512
6 TEST5 256
7 TEST1 512
7 TEST2 1224
7 TEST3 1824
7 TEST4 512
7 TEST5 256
8 TEST1 640
FILE_ID SEGMENT_NAME PARTI SUM(BLOCKS)
---------- -------------------- ----- -----------
8 TEST2 1280
8 TEST3 1920
8 TEST4 616
8 TEST5 256
9 TEST4 256
9 TEST5 1408
10 TEST5 1400
已選擇18行。
計算檢視每個資料檔案可以收回的空間大小
SQL>
SQL> select FILE_ID,BLOCK_ID,shrink_mb
2 from (select FILE_ID,BLOCK_ID,BYTES/1024/1024 shrink_mb,
3 row_number() over(partition by file_id order by BLOCK_ID desc) rn
4 from dba_free_space where file_id in (6,7,8,9,10) ) t
5 where rn =1 ;
FILE_ID BLOCK_ID SHRINK_MB
---------- ---------- ----------
6 12169 4.9375
7 12041 5.9375
8 12529 2.125
9 8969 29.9375
10 1409 89
除了檔案10外其他收回的量都比較小，這個應該是因為資料檔案高水位線的原因，資料檔案只能收縮最高水位線以上的資料塊，在檔案最高水位線以下各個分佈的空閒塊無法收回。
問題來了，怎麼能讓資料檔案裡高水位線以下的很多空閒資料庫合在一起呢？讓他們連續呢？
網上看到的一個計算生成收縮資料檔案的查詢。
SQL> select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
2 ceil(HWM * a.block_size)/1024/1024 ResizeTo,
3 (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
4 'alter database datafile '''||a.name||''' resize '||
5 ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
6 from v$datafile a,
7 (select file_id,max(block_id+blocks-1) HWM
8 from dba_extents
9 group by file_id) b
10 where a.file# = b.file_id(+)
11 and (a.bytes - HWM *block_size)>0
12 order by 5 ;
FILE# NAME CURRENTMB RESIZETO RELEASEMB RESIZECMD
-----------------------------------------------------------------------------------------------------------------------------------------------
4 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\USERS01.DBF 5 3.251.75 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\USERS01.DBF' resize 4M;
8 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF 100 97.8752.125 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF' resize 98M;
1 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSTEM01.DBF 480 477.06252.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSTEM01.DBF' resize 478M;
6 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF 100 95.06254.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF' resize 96M;
7 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF 100 94.06255.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF' resize 95M;
3 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSAUX01.DBF 250 240.43759.5625 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\SYSAUX01.DBF' resize 241M;
9 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF 100 70.062529.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF' resize 71M;
5 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\EXAMPLE01.DBF 146.875 80.187566.6875 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\EXAMPLE01.DBF' resize 81M;
10 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF 100 1189 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF' resize 11M;
已選擇9行。
如果只是想對某個表個間的datafile resize,可採用:
SQL> select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
2 ceil(HWM * a.block_size)/1024/1024 ResizeTo,
3 (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
4 'alter database datafile '''||a.name||''' resize '||
5 ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
6 from v$datafile a,
7 (select file_id,max(block_id+blocks-1) HWM
8 from dba_extents where file_id in
9 (select b.file# From v$tablespace a ,v$datafile b
10 where a.ts#=b.ts# and a.name='TEST')
11 group by file_id) b
12 where a.file# = b.file_id(+)
13 and (a.bytes - HWM *block_size)>0
14 order by 5
15 ;
FILE# NAME CURRENTMB RESIZETO RELEASEMB RESIZECMD
8 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF 100 97.8752.125 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF' resize 98M;
6 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF 100 95.06254.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF' resize 96M;
7 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF 100 94.06255.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF' resize 95M;
9 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF 100 70.062529.9375 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF' resize 71M;
10 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF 100 1189 alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF' resize 11M;
根據以上生產的執行語句執行對資料檔案的收縮操作
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF' resize 98M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF' resize 96M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF' resize 95M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF' resize 71M;
資料庫已更改。
SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF' resize 11M;
資料庫已更改。
收縮成功無報錯
檢視資料檔案大小
SQL> select file_id,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='TEST';
FILE_ID FILE_NAME BYTES/1024/1024
---------- -------------------------------------------------- ---------------
6 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST01.DBF 96
7 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST02.DBF 95
8 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST03.DBF 98
9 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST04.DBF 71
10 D:\ORACLE\PRODUCT\10.2.0\RLDB\RLDB\TEST05.DBF 11
資料檔案成功收縮至目標大小。
檢視資料正常
SQL> select count(*) from test1;
COUNT(*)
----------
133960
SQL> select count(*) from test2;
COUNT(*)
----------
301412
SQL> select count(*) from test3;
COUNT(*)
----------
452118
SQL> select count(*) from test4;
COUNT(*)
----------
150706
SQL> select count(*) from test5;
COUNT(*)
----------
284667
至此測試完成，資料檔案收縮完畢。
結論，資料檔案收縮大大小取決於高水位線（HWM）的位置，前期對資料表檔案裡所在表的shrink有一定的作用，但是如果表shrink的部分在高水位線一下則無明顯效果。
####################################################################################################################################
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31444259/viewspace-2153198/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- shrink收縮檔案空間
- Oracle案例11——Oracle表空間資料庫檔案收縮Oracle資料庫
- Oracle 10g Shrink Table - Shrink Space 收縮空間Oracle 10g
- 表空間中有資料也可以壓縮表空間（資料檔案）大小
- 分析表空間空閒率並收縮表空間
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案
- Oracle表空間收縮方案Oracle
- mysql收縮共享表空間MySql
- 【臨時表空間】11g中使用 SHRINK方法縮小臨時表空間和臨時檔案（續）
- 資料檔案收縮
- DB2_收縮表空間DB2
- 移動資料檔案、系統表空間檔案、臨時表空間檔案
- sqlserver收縮資料庫、收縮資料檔案的操作SQLServer資料庫
- 收縮Oracle資料檔案Oracle
- ORACLE 收縮資料檔案Oracle
- 收縮表空間 for Oracle 10gOracle 10g
- Oracle 表空間與資料檔案Oracle
- 表空間和資料檔案管理
- oracle 資料檔案表空間管理Oracle
- MySQL innodb共享表空間新增表空間資料檔案方法MySql
- 表空間&資料檔案和控制檔案(zt)
- oracle空間收縮Oracle
- 收縮ORACLE的資料檔案Oracle
- [zt] 收縮使用者資料庫 (shrink database)資料庫Database
- 【RESIZE】Oracle收縮表空間主要命令Oracle
- oracle之臨時表空間的收縮Oracle
- 表空間和資料檔案的管理
- oracle 表空間 資料檔案 筆記Oracle筆記
- 資料檔案,表空間的移動
- 表空間資料檔案故障處理
- 線上遷移表空間資料檔案
- Oracle 表空間資料檔案遷移Oracle
- 表空間新增資料檔案的疑惑
- Oracle資料檔案收縮例項Oracle
- 檢視Oracle資料庫表空間大小，是否需要增加表空間的資料檔案Oracle資料庫
- mysql共享表空間擴容,收縮,遷移MySql
- Oracle效能優化：收縮臨時表空間Oracle優化
- oracle 普通表空間資料檔案壞塊Oracle