近期查看数据库对象时发现某些段大的离谱。查看总记录数和空间后，确定是HWM造成的。查询这张表需要1056秒
使用存储过程查看碎片情况

SQL> exec show_space('XXXXXXXXX','XXXX');
Total Blocks  ..........................4960120
Total Bytes   ..........................40633303040
Total MBytes  ..........................38750.9375
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................4960120
Used Bytes    ..........................40633303040
Used KBytes   ..........................39680960
Last Used Ext FileId....................20
Last Used Ext BlockId...................3768329
Last Used Block.........................8192


The segment is analyzed below
FS1 Blocks (0-25)   ....................3
FS2 Blocks (25-50)  ....................0
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................260
Unformatted Blocks  ....................6368
Full Blocks         ....................4948137
PL/SQL procedure successfully completed


SQL> exec show_space('XXXXXXXXXXXX','XXXX','INDEX');
Total Blocks  ..........................5045760
Total Bytes   ..........................41334865920
Total MBytes  ..........................39420
Unused Blocks ..........................0
Unused Bytes  ..........................0
Unused KBytes ..........................0
Used Blocks   ..........................5045760
Used Bytes    ..........................41334865920
Used KBytes   ..........................40366080
Last Used Ext FileId....................20
Last Used Ext BlockId...................3719177
Last Used Block.........................8192


The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................2326070
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................1272
Full Blocks         ....................2712987
PL/SQL procedure successfully completed

将表进行shrink space 后，对索引进行操作
操作进行中时，发现空间使用率急速上升，原因是在shrink space 过程中有大量dml操作，导致undo 疯长
解决方法：停止shrink space 命令，对该索引进行rebuild

SQL> exec show_space('XXXXXXXX','XXX','INDEX');
Total Blocks  ..........................2158464
Total Bytes   ..........................17682137088
Total MBytes  ..........................16863
Unused Blocks ..........................4382
Unused Bytes  ..........................35897344
Unused KBytes ..........................35056
Used Blocks   ..........................2154082
Used Bytes    ..........................17646239744
Used KBytes   ..........................17232656
Last Used Ext FileId....................23
Last Used Ext BlockId...................1720329
Last Used Block.........................3810


The segment is analyzed below
FS1 Blocks (0-25)   ....................0
FS2 Blocks (25-50)  ....................1
FS3 Blocks (50-75)  ....................0
FS4 Blocks (75-100) ....................0
Unformatted Blocks  ....................0
Full Blocks         ....................2151477
PL/SQL procedure successfully completed