一、TRUNCATE TABLE 语法
TRUNCATE TABLE [schema_name.]table_name
? [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
? [ DROP STORAGE | REUSE STORAGE ] ;1
--下面仅列出reuse storage的说明部分?
REUSE STORAGE
Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings.

This setting is useful as an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future. TRUNCATE TABLE with REUSE STORAGE performs several orders of magnitude faster than deleting all rows, but has the following drawbacks:

?You cannot roll back a TRUNCATE TABLE statement.

?All cursors are invalidated.

?You cannot flash back to the state of the table before the truncate operation.

This clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped.

If you have specified more than one free list for the object you are truncating, then the REUSE STORAGE clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
二、演示truncate table .. reuse storage(11g)
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create table tb_reuse as select * from dba_objects;

Table created.

SQL> /? ? --多次执行

37200896 rows created.

SQL> create table tb_noreuse as select * from tb_reuse;

Table created.

SQL> select count(*) from tb_reuse;

? COUNT(*)
----------
? 37200896

SQL>? select count(*) from tb_noreuse;

? COUNT(*)
----------
? 37200896

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');

SEGMENT_NAME? ? ? ? ? ? ? ? ? ? ? ? BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 4165? --占用空间接近4GB
TB_NOREUSE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 4172

SQL> truncate table tb_noreuse;? --直接truncate，速度很快? ?

Table truncated.

Elapsed: 00:00:00.25
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');

SEGMENT_NAME? ? ? ? ? ? ? ? ? ? ? ? BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 4165
TB_NOREUSE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? .0625? -- 空间已回收

Elapsed: 00:00:00.03

SQL> truncate table tb_reuse reuse storage;? ? ? ? ? --使用reuse storage方式，并无太多性能提升

Table truncated.

Elapsed: 00:00:00.07
SQL> alter table tb_reuse deallocate unused keep 2048;? --这里漏掉了指定m，缺省为byte

Table altered.

Elapsed: 00:00:00.36
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');

SEGMENT_NAME? ? ? ? ? ? ? ? ? ? ? ? BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? .0625
TB_NOREUSE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? .0625

Elapsed: 00:00:00.03

三、演示truncate table .. reuse storage(12g)

SQL> select * from v$version where rownum=1;