An initiative of Mobiliar IT

Where does strange Table CMP4$17672 come from ?

Some time ago we defined a User Defined Metric in Cloud Control to alert us if a developer or even a dba creates a table using the advanced compression option. It is just easy make a mistake, and per Tech Note 1459216.1 there is no way to switch off advanced compression. See Can The Advanced Compression Option (ACO) Be Disabled Or Uninstalled? (Doc ID 1459216.1) and underlying bug Bug 14079401 : CUSTOMERS NEED A WAY OF DISABLING THE ADVANCED COMPRESSION FEATURE that end with the following statement: 

*** 07/25/16 01:36 pm RESPONSE ***

There are currently no plans to allow the Advanced Compression option 
to be disabled.

We were very suprised as a couple of days ago the metric fired.

oemwarning

We immediatly looked at the object causing the problem. But it wasn’t present any more.

We asked our developer if they knew about the table, but they were clueless.

We had a short look at v$sql and we found the statement in the shared pool.

 

SQL_FULLTEXT                                       PARSING_SCHEMA_NAME
create table "SchemName".CMP4$21200 organization     SYS
heap tablespace "SCHEMA_DATA"
compress for all operations nologging as select
/*+ DYNAMIC_SAMPLING(0) */ * from
"SCHEMA_NAME".CMP3$21200 mytab

The Table was created by SYS in the Schema of the Application.

We raised a SR, and the answer was the following:

“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.” Reference DOC ID ( Doc ID 1606356.1 ).

For me this seems to be quite dangerous, as if the table remains in the Users Schema for an abrupt termination of the segment advisor, than You will have a table with advanced compression in Your user schema.

We asked if the dba_feature_usage_statistics recognizes this table and will not fire on it. Or if we should track informations about those objects, in case auditing their creation, or something like that. Just in case we have to discuss during a license auditing.

Luckyly the dba_feature_statistics View is collected once a week, and up to now she did not notice any License faults, but probably because at the collection moment the table did not exist, or better, during lifetime of these objects there were no collection of feature statistics.

Here the output of the script You can find in tech note “Database Options/Management Packs Usage Reporting for Oracle Databases 11gR2 and 12c” (Doc ID 1317265.1)

PRODUCT USAGE

PRODUCT                |USAGE     |LAST_SAMPLE_DATE   |FIRST_USAGE_DATE|
-----------------------|----------|-------------------|----------------|
Active Data Guard      |NO_USAGE  |2018.04.27_07.54.53|                |
Advanced Analytics     |NO_USAGE  |2018.04.27_07.54.53|                |
Advanced Compression   |NO_USAGE  |2018.04.27_07.54.53|                |

The SR is ongoing, we will inform You about the results of it. So stay tuned.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: