Compression Advisor fails with ORA-20000

(Doc ID 1380611.1)

Last updated on JULY 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

Compression Advisor fails with ORA-20000, if there is no data at all in the table (ZERO rows)
As an example:

DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
COMPTYPE_STR varchar2(100);

BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('BONUS', 'BONUS', 'GBELETAB_9E4C', '',
DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);

DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;

ORA-20000: Compression Advisor TEMP UNCMP table failure

 

The error occurs only if the schema has any table with ZERO rows

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms