缩小表空间（缩小datafile）

1.如何缩小datafile

ALTER DATABASE DATAFILE '' RESIZE [K|M|G];
用这个命令可以缩小datafile的size,从而缩小tablespace的size。

2.如何确定resize的值

通过DBA_FREE_SPACE视图可以查看数据文件的空闲空间

SELECT * FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='' ORDER BY BLOCK_ID;

 

例如：

SQL> select * from dba_free_space where tablespace_name = 'TEST_T' order by block_id;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
RELATIVE_FNO
------------
TEST_T                                  8        136   51314688       6264
           8

SQL> select bytes,name from v$datafile where file#=8;

     BYTES
----------
NAME
--------------------------------------------------------------------------------
  52428800
/u01/app/oracle/oradata/orcl/test_t01.dbf

方法1：datafile中空闲的BYTES free_size=51314688/1024=50112k,縮小可能サイズ=50112k
       datafile的size datafile_size=52428800/1024=51200k
       datafile可以从新设定的值 resize=51200-50112=1088k

方法2：datafile可以从新设定的值 resize=（136-1）*8=1080k

当datafile的size设置成合适的大小时，datafile的size才会改变
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test_t01.dbf' resize 1080k;

Database altered.

SQL> select bytes,name from v$datafile where file#=8;

     BYTES
----------
NAME
--------------------------------------------------------------------------------
   1105920
/u01/app/oracle/oradata/orcl/test_t01.dbf

 

 

参考文档：

krownid[11919]
How to Resize a Datafile [ID 1029252.6]

 

相关文档 ：

SEGMENT SHRINK and Details. [ID 242090.1]

Why is no space released after an ALTER TABLE ... SHRINK? [ID 820043.1]

Difference Between Freespace, Size Of Tablespace And Usedspace [ID 268574.1]

How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark  [Article ID 130866.1]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756186/viewspace-740575/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756186/viewspace-740575/