oracle 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