首页 » ORACLE » oracle 缩小表空间大小(减小数据文件)

oracle 缩小表空间大小(减小数据文件)

已分配好的表空间,可能开始分的很大,后来怎么让它释放掉一部分还不影响已存在的数据?很多人都有这个疑问,方法很多,exp/expdp 表空间数据,建个小的表空间,imp/impdp,还有可以在原来的表空间直接做,往大了resize 都知道,可以,但往小了resize 可以么?答案是可以的,只要是未使用的空间。下面开始实验

思路

建个新的表空间100M,在新建的表空间建个表,插入大约3.6MB数据,把表空间的数据文件resize 10MB成功,resize 2MB失败因为已使用,再证明delete \truncate\后空间释放问题,move,shrink 个降低hwm

SQL> create tablespace tt datafile ‘/oracle/oradata/ICME/tt.dbf’ size 100M;

Tablespace created.
SQL> create table tt tablespace tt  as select * from dba_objects
2  ;

Table created.
SQL> select bytes/1024/1024 from dba_data_files where tablespace_name=’TT’;

BYTES/1024/1024
—————
100
SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
96.9375
SQL> alter database datafile ‘/oracle/oradata/ICME/tt.dbf’ resize 10m
2  ;

Database altered.

SQL> alter database datafile ‘/oracle/oradata/ICME/tt.dbf’ resize 2m
2  ;
alter database datafile ‘/oracle/oradata/ICME/tt.dbf’ resize 2m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> delete  tt ;

19159 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
6.9375
SQL> truncate table tt;

Table truncated.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
9.875
SQL> insert into  tt select * from dba_objects;

19159 rows created.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
6.9375
SQL> alter table tt move;

Table altered.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
3
3.9375
SQL> insert into  tt select * from dba_objects;

19159 rows created.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
1
3.9375
SQL> delete from tt;

38318 rows deleted.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
1
3.9375
SQL> alter table tt enable row movement;

Table altered.

SQL> alter table tt shrink space;

Table altered.

SQL> select bytes/1024/1024 from dba_free_space  where tablespace_name=’TT’;

BYTES/1024/1024
—————
3
6.875

SQL> analyze table tt compute statistics;

Table analyzed.

SQL> alter database datafile ‘/oracle/oradata/ICME/tt.dbf’ resize 2m;

Database altered.

SQL> drop table tt purge;

Table dropped.

SQL> drop tablespace tt;

Tablespace dropped.

SQL>

–end

现在考考你

如果 insert 4M数据,用delete 全部删掉,然后alter table tt move; 这样做了以后 datafile 可以resize 到2M么?

打赏

目前这篇文章有2条评论(Rss)评论关闭。

  1. Granville Biagi | #1
    2011-12-21 at 04:59

    Thx due to this article. Good.

  2. Kairi | #2
    2011-06-23 at 03:22

    I am totlaly wowed and prepared to take the next step now.