CMP3$96874 表（Oracle 压缩顾问临时表）

在数据库数据导出过程中，发现名称格式为 CMP3$xxx 的表（如 CMP3$96874），这类表并非业务表，而是 Oracle 压缩顾问运行时生成的临时中间表，以下是完整的解析与操作指南。

一、现象与基础信息

1. 表的查询结果

SQL> select t.last_analyzed, t.owner, t.table_name from dba_tables t where t.table_name like 'CMP%';

LAST_ANALYZED	    OWNER			   TABLE_NAME
------------------- ------------------------------ ------------------------------
2024-06-02 06:00:13 RM				   CMP3$96874
2024-01-19 22:12:54 RM				   CMP3$93830
2021-05-22 22:05:50 RM				   CMP3$93816
2024-07-19 22:00:16 RM				   CMP3$90752
2024-05-26 06:00:19 RM				   CMP3$90456
2024-01-19 22:12:54 RM				   CMP3$90377
2024-01-19 22:12:54 RM				   CMP3$90370
2024-01-19 22:12:54 RM				   CMP3$111853
2024-01-19 22:12:54 RM				   CMP3$111839

9 rows selected.

SQL> 

注：SELECT 语句可正常查询表内数据，但其数据为源表副本，无独立业务价值。

2. 表名含义

CMP3$96874 是 Oracle 数据库中 DBMS_COMPRESSION（压缩顾问）的临时产物，命名规则拆解：

• CMP：Compression Advisor（压缩顾问）的缩写，标识表的归属模块；
• 3：压缩顾问内部流程编号，对应压缩测试的第3阶段；
• $96874：随机生成的进程/会话ID后缀，每次执行压缩顾问都会生成不同数值。

二、表的生成逻辑与生命周期

1. 生成背景

CMP3$xxx 是 Oracle 压缩顾问执行 GET_COMPRESSION_RATIO 或 ADVISE_COMPRESSION 存储过程时的中间产物，核心用于在不影响生产数据的前提下，计算表的压缩比、测试压缩算法效果。

2. 压缩顾问完整执行流程

CMP3$xxx 是 Oracle 压缩顾问（DBMS_COMPRESSION）执行 GET_COMPRESSION_RATIO 或 ADVISE_COMPRESSION 存储过程时的中间产物：

• 生成逻辑：压缩顾问会先创建源表的子集/全量副本（即 CMP1→CMP2→CMP3→CMP4），依次完成数据采样、压缩算法测试、压缩比计算，最终生成报告后自动清理这些临时表。
• 残留原因：除了进程中断/断电，若执行时指定 keep_temp_tables => TRUE（默认 FALSE），也会主动保留这些表用于后续分析。

压缩顾问通过多阶段生成临时表完成压缩测试，各阶段表的作用与数据特征如下：

graph TD
    A[源表] -->|1. 数据采样| B[CMP1$xxx：采样临时表，默认取10%数据]
    B -->|2. 格式转换| C[CMP2$xxx：标准化数据格式，适配压缩算法]
    C -->|3. 全量复制| D[CMP3$xxx：源表完整副本，核心压缩测试表]
    D -->|4. 压缩计算| E[CMP4$xxx：存储压缩后数据，用于计算压缩比]
    E -->|计算完成| F[生成压缩比报告]
    F -->|默认清理| G[删除所有CMP$系列临时表]
    F -->|异常中断| H[残留CMP$系列表]

3. 各阶段临时表核心差异

4. 表残留的原因

正常情况下，压缩顾问生成报告后会自动清理所有 CMP$ 系列临时表，残留仅见于以下场景：

• 压缩顾问进程异常中断（如执行报错、服务器断电、手动kill会话）；
• 执行存储过程时显式指定 keep_temp_tables => TRUE（默认值为 FALSE），主动保留临时表用于后续分析；
• 数据库权限异常，导致压缩顾问无权限执行清理操作。

5. 残留表的影响

• 空间占用：CMP3$xxx 为源表全量副本，若源表为TB级，会直接占用等量存储空间；
• 元数据冗余：大量残留表会增加数据字典（dba_tables/dba_objects）的查询负担，降低元数据操作效率；
• 无业务风险：所有 CMP$ 系列表均为临时测试表，无业务逻辑依赖，删除后不影响源表及业务功能。

三、表结构与数据特征

• CMP3$xxx 表的结构与源表完全一致（字段名、数据类型、约束、索引等）；
• 数据内容为源表的副本，默认场景下是全量数据，若调整 sample_percent 参数，也可能为采样子集；
• 可通过以下方式追溯对应源表：

-- 方式1：对比表行数/存储空间，匹配源表候选（RM用户为例）
SELECT t.table_name, t.num_rows, t.blocks, t.bytes/1024/1024 AS size_mb
FROM dba_tables t
WHERE t.owner = 'RM' 
  AND t.num_rows > 0
  AND t.table_name NOT LIKE 'CMP%'  -- 排除临时表，筛选源表
ORDER BY t.bytes DESC;

-- 方式2：查询表的创建审计日志（需开启数据库审计功能）
SELECT owner, obj_name, action_name, timestamp, os_username
FROM dba_audit_object
WHERE obj_name = 'CMP3$96874' 
  AND action_name = 'CREATE TABLE';

-- 方式3：对比表结构，精准匹配源表
SELECT a.table_name AS 源表名
FROM (
    -- 提取非CMP表的结构特征
    SELECT table_name, 
           LISTAGG(column_name||' '||data_type, ',') WITHIN GROUP (ORDER BY column_id) AS struct_hash
    FROM dba_tab_columns
    WHERE owner = 'RM' AND table_name NOT LIKE 'CMP%'
    GROUP BY table_name
) a
JOIN (
    -- 提取CMP3$96874的结构特征
    SELECT LISTAGG(column_name||' '||data_type, ',') WITHIN GROUP (ORDER BY column_id) AS struct_hash
    FROM dba_tab_columns
    WHERE owner = 'RM' AND table_name = 'CMP3$96874'
    GROUP BY table_name
) b ON a.struct_hash = b.struct_hash;

四、残留表的确认与清理

1. 确认残留的CMP$系列表

执行以下SQL可全量查询数据库中所有压缩顾问临时表：

SELECT owner, 
       table_name, 
       tablespace_name, 
       num_rows,
       bytes/1024/1024 AS size_mb,  -- 表占用空间（MB）
       last_analyzed               -- 最后分析时间
  FROM dba_tables
 WHERE table_name REGEXP_LIKE('^CMP[1-4]?\$\d+$', 'i')  -- 匹配CMP$xxx/CMP1$xxx等格式
 ORDER BY owner, size_mb DESC;

2. 安全清理残留表

CMP$ 系列表无业务数据，可直接删除，建议添加 PURGE 关键字跳过回收站，避免二次占用空间：

（1）单表删除（示例）

-- 以SYS或RM用户执行，替换为实际表名
DROP TABLE "RM"."CMP3$96874" PURGE;

（2）批量删除（推荐）

先生成删除脚本，验证无误后执行，避免误删：

-- 步骤1：生成批量删除脚本
SELECT 'DROP TABLE "'||owner||'"."'||table_name||'" PURGE;' AS delete_sql
FROM dba_tables
WHERE owner = 'RM'  -- 限定目标用户，缩小范围
  AND table_name REGEXP_LIKE('^CMP[1-4]?\$\d+$', 'i');

-- 步骤2：执行生成的脚本（示例）
DROP TABLE "RM"."CMP3$96874" PURGE;
DROP TABLE "RM"."CMP3$93830" PURGE;
DROP TABLE "RM"."CMP3$93816" PURGE;
-- 其他表删除语句...

3. 预防表残留的最佳实践

（1）执行压缩顾问时显式指定清理

调用 DBMS_COMPRESSION 时，强制设置 keep_temp_tables => FALSE（双重保障，默认值已为FALSE）：

DECLARE
    v_comp_ratio   NUMBER;    -- 压缩比
    v_blk_comp     NUMBER;    -- 压缩后块数
    v_blk_uncomp   NUMBER;    -- 压缩前块数
BEGIN
    DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
        scratchtbsname    => 'USERS',                  -- 临时表空间
        ownname           => 'RM',                     -- 源表所属用户
        tabname           => 'SOURCE_TABLE',           -- 待测试的源表名
        partname          => NULL,                     -- 分区名（无分区则填NULL）
        comptype          => DBMS_COMPRESSION.COMP_FOR_OLTP,  -- 压缩类型（OLTP压缩）
        keep_temp_tables  => FALSE,                    -- 关键：执行后自动删除临时表
        compression_ratio => v_comp_ratio,
        compressed_blocks => v_blk_comp,
        uncompressed_blocks => v_blk_uncomp
    );
    DBMS_OUTPUT.PUT_LINE('压缩比：'||ROUND(v_comp_ratio, 2));
END;
/

（2）定期巡检清理

创建定时任务，每日清理残留的 CMP$ 表，避免堆积：

BEGIN
    -- 创建定时任务
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'RM_CLEAN_CMP_TEMP_TABLES',  -- 任务名
        job_type        => 'PLSQL_BLOCK',               -- 任务类型
        job_action      => '
            DECLARE
                CURSOR c_cmp_tables IS
                    SELECT ''DROP TABLE "''||owner||''"."''||table_name||''" PURGE;'' AS sql_stmt
                    FROM dba_tables
                    WHERE owner = ''RM''
                      AND table_name REGEXP_LIKE(''^CMP[1-4]?\$\d+$'', ''i'');
            BEGIN
                FOR rec IN c_cmp_tables LOOP
                    EXECUTE IMMEDIATE rec.sql_stmt;
                END LOOP;
            END;',
        start_date      => SYSTIMESTAMP,                -- 立即生效
        repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=0',  -- 每日23点执行
        enabled         => TRUE,                        -- 启用任务
        comments        => '每日清理RM用户的压缩顾问临时表'
    );
END;
/