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
------------------------------ ---------- ---------- ---------- ----------
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
----------
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
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
----------
NAME
--------------------------------------------------------------------------------
1105920
/u01/app/oracle/oradata/orcl/test_t01.dbf
参考文档:
krownid[11919]
How to Resize a Datafile [ID 1029252.6]
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/