SQL> create table t1 as select * from dba_objects;

表已创建。

SQL> create table t2 as select * from dba_objects;

表已创建。

SQL> create table t3 as select * from dba_objects;

表已创建。
查看表的大小 SQL> select t.bytes from user_segments t where t.segment_name=upper('t1')
   2   /

    BYTES
----------
6291456
或者 SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t1');

SUM(T.BYTES)
------------
      6291456

条件一:
The owner of a table or cluster, or a user with the DELETE ANY privilege, can
truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.

代码:SQL> truncate table t1 drop storage;

表被截断。

SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t1');

SUM(T.BYTES)
------------
65536

注意：truncate删除原来表的所有数据,需要重建索引。
条件二
A database administrator (DBA) can deallocate unused extents using the following
SQL syntax:
ALTER TABLE table_name DEALLOCATE UNUSED;

复制内容到剪贴板代码:SQL> delete from t2;

已删除49375行。

SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t2');

SUM(T.BYTES)
------------
6291456

SQL> alter table t2 deallocate unused;

表已更改。

SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t2');

SUM(T.BYTES)
------------
5767168

注意：随软释放空间，但是释放的很少
条件三：

复制内容到剪贴板代码:SQL> alter table t2 enable row movement;

表已更改。

SQL> alter table t2 shrink space;

表已更改。

SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t2');

SUM(T.BYTES)
------------
   65536
或者 SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t3');

SUM(T.BYTES)
------------
    6291456

SQL> alter table t3 move;

表已更改。

SQL> select sum(t.bytes) from user_extents t where t.segment_name=upper('t3');

SUM(T.BYTES)
------------
   65536

注意：进行上述操作，要保证有两倍的可用空间，且需要重建索引；对于shrink，需要row movement。
条件四：
Periodically, Oracle deallocates one or more extents of a rollback segment if it has
the OPTIMAL size specified.
错误之处敬请指正

ITPub博客

When Extents Are Deallocated

最新文章