"DBMS_TABCOMP_TEMP_UNCMP" and "DBMS_TABCOMP_TEMP_CMP" tables get created on Oracle-based TIBCO Spotfire Server application database at time of database size increase – Revvity Signals

Date Posted: 2018-10-25
Product: TIBCO Spotfire®

Problem:

"DBMS_TABCOMP_TEMP_UNCMP" and "DBMS_TABCOMP_TEMP_CMP" tables get created on Oracle-based TIBCO Spotfire Server application database at time of database size increase

Solution:

The TIBCO Spotfire Server database, hosted on an Oracle server, temporarily increases in size and observes a peak period by causing the increase in table space usage. During this period, two temporary tables get created: "*.DBMS_TABCOMP_TEMP_UNCMP" and "*.DBMS_TABCOMP_TEMP_CMP". The creation of these two temporary tables ("*.DBMS_TABCOMP_TEMP_UNCMP" and "*.DBMS_TABCOMP_TEMP_CMP") is part of the Oracle Compression Advisor which can be a part of the DEFAULT_MAINTENANCE_PLAN. Compression advisor is a new advisor added in 11gR2. It is shipped with Segment Advisor, and will be executed whenever a Segment Advisor task is run. It can also be invoked manually.

Dbms_compression package will be invoked to estimate how much space can be saved. It will create two internal tables (DBMS_TABCOMP_TEMP_UNCMP, DBMS_TABCOMP_TEMP_CMP) in the tablespace which hosts the segment. "DBMS_TABCOMP_TEMP_UNCMP" is created with 99% sample blocks by default. "DBMS_TABCOMP_TEMP_CMP" is created with compress option based on "DBMS_TABCOMP_TEMP_UNCMP". COMPRESSION_RATIO is generated by comparing "DBMS_TABCOMP_TEMP_UNCMP" and "DBMS_TABCOMP_TEMP_CMP". These two tables will be dropped immediately after the estimation.

No action is required from Spotfire when this behavior is seen, and the temporary increase in table space of the TIBCO Spotfire Server application database is not due to Spotfire activity. External: Oracle Compression Advisor