DBMS_COMPRESSION.GET_COMPRESSION_RATIO Erroring Out With ORA-01403: No Data Found (Doc ID 3004805.1)

Last updated on DECEMBER 23, 2024

Applies to:

Symptoms

set serveroutput on

DECLARE
blkcnt_cnt pls_integer;
blkcnt_uncmp pls_integer;
blkcnt_cmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('MYTABLESPACE', 'MYSCHEMA', 'MYINDEX', 'MYPARTITION',DBMS_COMPRESSION.COMP_INDEX_ADVANCED_HIGH, blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_COMPRESSION.COMP_INDEX_ADVANCED_HIGH, 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 = '|| cmp_ratio);
END;
/
-- Error
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.PRVT_COMPRESSION", line 40
ORA-06512: at "SYS.DBMS_COMPRESSION", line 246
ORA-06512: at line 10

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.