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