0 redo size
1183 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
###############DELETE删除后的DUMP信息和上一个未删除时的没啥区别，不贴了。
DELETE删除后对表做SHRINK：--可以回收空间，降低高水位
BYS@ bys3>alter table test12 enable row movement;Table altered.
BYS@ bys3>alter table test12 shrink space; --SHRINK与MOVE，详见：shrink合并数据块--解决数据块碎片问题
Table altered.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 3784 0 0
BYS@ bys3>analyze table test12 compute statistics; 对表做分析后，tabs里的BLOCKS信息才会变。
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 1 0 0
BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26432.trc
######################### DUMP信息如下：--可以看到空间已经回收了。高水位也已经下降
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100039c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Highwater:: 0x0100039c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000398
Level 1 BMB for Low HWM block: 0x01000398
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01000398
Last Level II BMB: 0x01000399
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 23303 flag: 0x10000000
Inc # 1
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000398 Data dba: 0x0100039b
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
End dump data blocks tsn: 4 file#: 4 minblk 922 maxblk 922
##################################
2.TRUNCATE操作直接就可以回收空间，改变高水位--但是如果非分区表，就无法用了，没有DELETE应用场景多。
create table test13 as select * from dba_objects;BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#############DUMP文件信息：
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 256
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01001