[20131017]11G下truncate的新特性.txt
http://www.askmaclean.com/archives/know-high-water-mark-truncate.html
11.2.0.2中出现truncate的新特性，截断表目前有了新选项：即drop all storage。
测试看看：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id ,'test' name from dual connect by level Table created.
SCOTT@test> create index i_t_id on t(id);
Index created.
SCOTT@test> exec dbms_stats.gather_table_stats('','T',cascade=>TRUE);
PL/SQL procedure successfully completed.
SCOTT@test> analyze index i_t_id validate structure;
Index analyzed.
SCOTT@test> @i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
2 56 I_T_ID 20000 44 309700 7996 43 1 496 8028 0 0 20000
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
1 359852 310196 87 1 3 0 0 0 0
SCOTT@test> select segment_name,blocks from dba_segments where segment_name in ('T','I_T_ID') and wner=user;
SEGMENT_NAME BLOCKS
-------------------- ----------
T 48
I_T_ID 56
SCOTT@test> truncate table t drop all storage;
Table truncated.
SCOTT@test> select segment_name,blocks from dba_segments where segment_name in ('T','I_T_ID') and wner=user;
no rows selected
--以上可以看到在普通用户模式下对表truncate drop all storage后，该表相关的segment均被事实上的drop了。