通常我们认为truncate和drop都是ddl语句,都会释放表占用的空间,且不可回退;而他们的之间的区别在于我们平时忽略的reuse/drop storage子句.
  reuse storage不会立即释放表的extent,我们可以先使用truncate table tableName reuse storage,然后分批释放表的extent.这在删除大表时非常有用,避免大量的io操作,影响整体性能.如果使用默认的drop storage就会立即释放extent,删除的表如果非常大,这对系统有时候这可能是灾难性的.
  实验过程如下:
SQL> connect zlhis/his;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as zlhis
SQL> create table test_truncate as select * from dba_objects;
Table created
SQL> insert into test_truncate select * from test_truncate;
55157 rows inserted
SQL> insert into test_truncate select * from test_truncate;
110314 rows inserted
SQL> insert into test_truncate select * from test_truncate;
220628 rows inserted
SQL> insert into test_truncate select * from test_truncate;
441256 rows inserted
SQL> insert into test_truncate select * from test_truncate;
882512 rows inserted
SQL> commit;
Commit complete
SQL> select Extents,blocks from dba_segments where segment_name='TEST_TRUNCATE';
   EXTENTS     BLOCKS
---------- ----------
        96      24576
SQL> truncate table test_truncate reuse storage;
Table truncated
SQL> select Extents,blocks from dba_segments where segment_name='TEST_TRUNCATE';
   EXTENTS     BLOCKS
---------- ----------
        96      24576
SQL> select bytes/1024/1024 as space_m  from dba_segments where segment_name='TEST_TRUNCATE';
   SPACE_M
----------
       192
SQL> ALTER table test_truncate DEALLOCATE UNUSED KEEP 100M;
Table altered
SQL> select Extents,blocks from dba_segments where segment_name='TEST_TRUNCATE';
   EXTENTS     BLOCKS
---------- ----------
        84      12808
SQL> select bytes/1024/1024 as space_m  from dba_segments where segment_name='TEST_TRUNCATE';
   SPACE_M
----------
  100.0625
SQL> ALTER table test_truncate DEALLOCATE UNUSED KEEP 0M;
Table altered
SQL> select bytes/1024/1024 as space_m  from dba_segments where segment_name='TEST_TRUNCATE';
   SPACE_M
----------
    0.0625