DBMS_COMPRESSION.GET_COMPRESSION_RATIO Fails With ORA-00933 - ORA-06512 (Doc ID 1493289.1)

Last updated on MARCH 27, 2014

Applies to:

Symptoms

On production database 11.2.0.3 version, when checking a table for possbile compression ratio the following statement fails as below.

ERROR
-----------------------
ORA-00933: SQL-Befehl wurde nicht korrekt beendet
ORA-06512: in "SYS.PRVT_COMPRESSION", Zeile 776
ORA-06512: in "SYS.DBMS_COMPRESSION", Zeile 214
ORA-06512: in Zeile 10

declare
 v_blkcnt_cmp pls_integer;
 v_blkcnt_uncmp pls_integer;
 v_row_cmp pls_integer;
 v_row_uncmp pls_integer;
 v_cmp_ratio number;
 V_COMPTYPE_STR varchar2(60);
 
begin
 DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
 scratchtbsname => 'TST_DATA',
 ownname => 'TEST',
 tabname => 'TEST_TA',
 partname => NULL,
 comptype => 2,
 blkcnt_cmp => v_blkcnt_cmp,
 blkcnt_uncmp => v_blkcnt_uncmp,
 row_cmp => v_row_cmp,
 row_uncmp => v_row_uncmp,
 cmp_ratio => v_cmp_ratio,
 COMPTYPE_STR => V_COMPTYPE_STR
 );
 
 dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
 dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
 dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
end;
/

Cause