收缩，建议

--压缩建议分两步，第一次带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;

后台执行命令参考

sqlplus / as sysdba <


查询表大小：压缩后会释放空间，可对比空间大小
--$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;

其他命令参考：
--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;

【SHRINK】Oracle收缩表的详细命令参考