oracle数据库中出现,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 CMP3xxxxxxorCMP4xxxxxx or CMP4xxxxxxorCMP4xxxxxx (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