FAQ：Oracle数据库中出现的 CMP$ 表是什么？

经常有 Oracle DBA 提问，数据库中偶然出现的 CMP$ 表是什么？

类似下图这些对象，有时候看起来有些不知所措：

CMP - 指 Compression Advisor ，是数据库的压缩建议特性，在生成建议时产生的中间过程表，一般会自行删除。

【问题原因】
这个特性自 11.2.0.4 引入（ Oracle 11.2.0.4 BP1 or higher），在某些情况，该功能执行失败后，会遗留下一个系列的数据库对象。

这些对象，可以在 SYS 下进行删除。

If you see tables with names like CMP3$xxxxxx or CMP4$xxxxxx (where xxxxxx is a number) left over after running Compression Advisor, it is likely because Compression Advisor failed at some point. These are interim tables created/used by Compression Advisor, which are normally dropped when it completes. You can safely drop those tables.

MOS以下文档具有说明:

“MOS Note Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor? (Doc ID 1606356.1)

引用一个测试用例：

SQL> 
alter session set tracefile_identifier = 'CompTest1110201815h51';
alter session set events '10046 trace name context forever, level 12';
set serveroutput on
 
DECLARE
  l_blkcnt_cmp    PLS_INTEGER;
  l_blkcnt_uncmp  PLS_INTEGER;
  l_row_cmp       PLS_INTEGER;
  l_row_uncmp     PLS_INTEGER;
  l_cmp_ratio     NUMBER;
  l_comptype_str  VARCHAR2(32767);
BEGIN
  DBMS_COMPRESSION.get_compression_ratio (
    scratchtbsname  => 'USERS',
    ownname         => 'TEST_LAF',
    objname         => 'FOO',
    subobjname      => NULL,
    comptype        => DBMS_COMPRESSION.comp_advanced,
    blkcnt_cmp      => l_blkcnt_cmp,
    blkcnt_uncmp    => l_blkcnt_uncmp,
    row_cmp         => l_row_cmp,
    row_uncmp       => l_row_uncmp,
    cmp_ratio       => l_cmp_ratio,
    comptype_str    => l_comptype_str,
    subset_numrows  => DBMS_COMPRESSION.comp_ratio_allrows,
    objtype         => DBMS_COMPRESSION.objtype_table
  );
 
  DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);
  DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);
  DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);
  DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);
  DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/
 
Number of blocks used (compressed)       : 1325
Number of blocks used (uncompressed)     : 1753
Number of rows in a block (compressed)   : 74
Number of rows in a block (uncompressed) : 55
Compression ratio                        : 1.3
Compression type                         : "Compress Advanced"
 
PL/SQL procedure successfully completed.

通过跟踪文件可以看到内部过程：

grep  "CMP*" DBI_ora_20529_CompTest1110201823h19.trc
 
drop table "TEST_LAF".CMP1$23590 purge
drop table "TEST_LAF".CMP2$23590 purge
drop table "TEST_LAF".CMP3$23590 purge
drop table "TEST_LAF".CMP4$23590 purge
create table "TEST_LAF".CMP3$23590 tablespace "USERS" nologging  as select /*+ DYNAMIC_SAMPLING(0) FULL("TEST_LAF"."FOO") */ *  from "TEST_LAF"."FOO"  sample block( 99) mytab
create table "TEST_LAF".CMP4$23590 organization heap  tablespace "USERS"  compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "TEST_LAF".CMP3$23590 mytab
drop table "TEST_LAF".CMP1$23590 purge
drop table "TEST_LAF".CMP2$23590 purge
drop table "TEST_LAF".CMP3$23590 purge
drop table "TEST_LAF".CMP4$23590 purge