oracle table 碎片化消除 shrink space

os: centos 7.4
db: oracle 12.1.0.2.0

版本

# cat /etc/redhat-release 
CentOS Linux release 7.4.1708 (Core)


# su - oracle
$ sqlplus / as sysdba;

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 15 14:02:23 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set lines 200;
set pages 200;
SQL> 
SQL> select * from v$version;

BANNER										     CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production		  0
PL/SQL Release 12.1.0.2.0 - Production							  0
CORE	12.1.0.2.0	Production								  0
TNS for Linux: Version 12.1.0.2.0 - Production						  0
NLSRTL Version 12.1.0.2.0 - Production							  0

使用

shrink是delete/insert相结合，这样会产生非常多的 UNDO 和 REDO 。
这点需要注意。

SQL> alter table <table_name> shrink space [ <null> | compact | cascade ];

shrink 必须开启行迁移功能

SQL> alter table table_name enable row movement ;

同时禁用在该表上的触发器

select 'ALTER TRIGGER '||tri.owner||'.'||tri.trigger_name||' disable;',tri.*
from dba_triggers tri
where 1=1
and tri.table_owner=''
and tri.table_name=''
;

ALTER TRIGGER owner_name.table_name trigger_name {ENABLE|DISABLE};

回缩索引与降低HWM

SQL> alter index index_name shrink space;
 

保持HWM，相当于把块中数据打结实了

SQL> alter table table_name shrink space compact;
 

回缩表与相关索引，降低HWM, (包括 BASICFILE LOB segments):

SQL> alter table table_name shrink space cascade;
 

只回缩LOB字段

SQL> alter table table_name MODIFY LOB (lob_col_example) (SHRINK SPACE);
 

只回缩某个分区

SQL> alter table table_name MODIFY PARTITION table_name_P1 SHRINK SPACE;
 

回缩 IOT 的 overflow 段

SQL>  alter table table_name OVERFLOW SHRINK SPACE;
 

参考:
https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF01001