收缩，建议 ``` sql --压缩建议分两步，第一次带compact，第二次不带compat alter table/index/materialized view object_name shrink space [cascade] [compact]; --cascade：是指压缩所有依赖的对象，比如压缩表语句加上cascade，表上所有的索引都会被压缩 --compact：把压缩过程分为两个阶段：第一个阶段的语句带compact，压缩段空间，在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。；第二个阶段语句不带compact，调整高水位并释放收回的空间。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。对于大表，建议采用compact选项 SQL> alter table employees enable row movement; SQL> ALTER TABLE employees SHRINK SPACE COMPACT; SQL> ALTER TABLE employees SHRINK SPACE; ``` 后台执行命令参考 ``` sql sqlplus / as sysdba <<EOF spool '/home/oracle/script/table_shrink20171124.log'; set time on alter table /*+APPEND*/ UserName.TableName enable row movement nologging parallel 4; alter table UserName.TableName shrink space compact; alter table UserName.TableName shrink space cascade; alter table UserName.TableName noparallel logging; set time off spool off exit EOF ``` 查询表大小：压缩后会释放空间，可对比空间大小 ``` sql --$vi cacl.sql ACCEPT SCHEMA PROMPT 'Table Owner: ' ACCEPT TABNAME PROMPT 'Table Name: ' SELECT (SELECT SUM(S.BYTES) -- The table segment size FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('&SCHEMA') AND (S.SEGMENT_NAME = UPPER('&TABNAME'))) + (SELECT SUM(S.BYTES) -- The Lob Segment Size FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) + (SELECT SUM(S.BYTES) -- The Lob Index size FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA'))) "TOTAL TABLE SIZE" FROM DUAL; ``` 其他命令参考： ``` sql --Shrink a table and all of its dependent segments (including BASICFILE LOB segments): alter table t1 shrink space compact; alter table t1 enable row movement nologging parallel 2; alter table /*+APPEND*/ t1 enable row movement nologging parallel 4; alter table t1 shrink space compact; ALTER TABLE t1 SHRINK SPACE CASCADE; alter table t1 noparallel logging; --Shrink a BASICFILE LOB segment only: ALTER TABLE employees MODIFY LOB (perf_review) (SHRINK SPACE); --Shrink a single partition of a partitioned table: ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE; --Shrink an IOT index segment and the overflow segment: ALTER TABLE cities SHRINK SPACE CASCADE; --Shrink an IOT overflow segment only: ALTER TABLE cities OVERFLOW SHRINK SPACE; ```