收缩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么？

打赏

微信扫一扫，打赏作者吧～