SHRINK SPACE：在线收缩表、LOB、IOT段的大小

转自墨天轮，原文地址https://www.modb.pro/db/11354

从10g开始，可以通过alter table shrink space命令来在线收缩段空间，相对于MOVE来说，对应用的影响更小。

示例

以下是alter table shrink space示例。

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

该COMPACT选项允许收缩操作分为两个阶段。首先，使用COMPACT选项移动行，但不调整高水位线（HWM），因此不会使解析的SQL语句失效。之后再通过不带COMPACT命令来调整HWM，此时相关的SQL语句都需要重新解析。

LOB、IOT收缩命令如下：

-- Shrink a LOB segment (basicfile only).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;

相关内容

找出大的segment

通过DBA|ALL|USER_SEGMENTS视图找到较大的segment。以下示例使用top-n查询来显示20个最大的segment。

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 20;

以下top-n查询来获取有关LOB段的大小。

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;

行迁移

ALTER TABLE … SHRINK SPACE命令在现有块之间移动行以压缩数据，因此在尝试收缩段之前，需要启用行迁移。您可以通过查询[DBA|ALL|USER]_TABLES视图的ROW_MOVEMENT列来检查是否已启用行迁移。

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
DISABLED

SQL>

使用以下命令启用行迁移：

ALTER TABLE emp ENABLE ROW MOVEMENT;

再次查询是否启用：

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
ENABLED

SQL>

SecureFile LOBs

使用basicfile LOB时，收缩命令可以正常运行。为了证明这一点，我们需要创建一个包含basicfile LOB列的表。

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS BASICFILE (DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

我们可以看到两个收缩命令正常工作。

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);

Table altered.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

现在，使用securefile LOB列重新创建表。

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS SECUREFILE (DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

现在，第一个命令失败，但是添加该CASCADE选项似乎使其起作用。

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);
ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE)
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

不过第二个命令不起作用，securefile LOB段未缩小。
如果要收缩securefile LOB段，需要使用move或者在线重定义。在以下示例中，将MOVE到相同的表空间。

ALTER TABLE lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);

注意事项和限制

在执行收缩操作之前，需要考虑以下事项。

移动行可能会导致基于Rowid的触发器出现问题。
Rowid的物化视图必须在收缩操作后重新构建。
收缩过程仅适用于启用了自动段空间管理的表空间中的对象。
不能将SHRINK SPACE子句与其他任何ALTER TABLE子句组合使用。
不能收缩cluster或cluster table。
不能收缩包含LONG类型字段的对象。
不能收缩包含函数索引、domain索引或位图联接索引的表。
不能收缩包含ON COMMIT物化视图的表
索引组织表的映射表不受收缩的影响。
不能收缩压缩表，除非使用高级行压缩（ROW STORE COMPRESS ADVANCED）。
对表的收缩操作不会级联到LOB字段。需要分开处理。
不能收缩securefile LOB段。
在某些情况下，更改表中行的排列可能会对性能产生负面影响。在做出任何决定之前，请彻底测试。
在进行任何结构性更改（例如移动）之后，请记住检查不可用的索引。您可以使用unusuable_indexes.sql脚本来查找它们。如果有，需要重建。