ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表空间(数据文件shrink)收缩示例

表空间(数据文件shrink)收缩示例

原创 Linux操作系统 作者:comebackdog 时间:2012-03-02 11:10:44 0 删除 编辑
正常的数据库管理中会碰到空间不足的情况,一些表空间因为总的磁盘空间有限影响了其正常的扩展,但还有些表空间空余空间很大却不能分给别的表空间使用,这里进行一个表空间的收缩测试。
以收缩数据文件方式为例,吧数据文件里空余的空间shrink出来给磁盘,供其他表空间扩展使用。

参考
http://www.oracleonlinux.cn/2011/05/25/10g_rac_shrink_tablespace/
http://www.34hap.com/database/space/how_to_shrink_datafile.html
#################################################################################################################
测试环境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/20575781/viewspace-717501/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 检查pga
请登录后发表评论 登录
全部评论

注册时间:2011-09-27

  • 博文量
    19
  • 访问量
    81578