ORA-01536: space quota exceeded for tablespace along with DBMS_COMPRESSION.GET_COMPRESSION_RATIO

(Doc ID 970324.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.1 to 11.2.0.1.0 [Release 11.2]
Information in this document applies to any platform.

*** Checked for Relevance 02-Jul-2012 ***

Symptoms

When using DBMS_COMPRESSION.GET_COMPRESSION_RATIO


SQL> set serveroutput on
SQL> DECLARE
2 blkcnt_cmp pls_integer;
3 blkcnt_uncmp pls_integer;
4 row_cmp pls_integer;
5 row_uncmp pls_integer;
6 cmp_ratio pls_integer;
7 comptype_str varchar2(100);
8 BEGIN
9 DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('USERS', 'SCOTT', 'EMP', '',
10 DBMS_COMPRESSION.COMP_FOR_OLTP,
11 blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
12
13 DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
14 DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
15 DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
16 DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
17 DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
18 DBMS_OUTPUT.PUT_LINE('Compression ratio = '||blkcnt_uncmp/blkcnt_cmp||' to 1');
19 DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
20 END;
21 /


the following errors are coming:

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.PRVT_COMPRESSION", line 459
ORA-01536: space quota exceeded for tablespace 'USERS'
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 9

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