Oracle | PRVT_COMPRESSION

Oracle PRVT_COMPRESSION Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This internal package supports advanced compression features introduced beginning with 11gR1

AUTHID CURRENT_USER

Dependencies

ALL_EXTERNAL_TABLES	ALL_TAB_SUBPARTITIONS	DBMS_SPACE
ALL_INDEXES	ALL_VARRAYS	DBMS_STANDARD
ALL_IND_COLUMNS	DBA_OBJECTS	DUAL
ALL_IND_PARTITIONS	DBMS_ADVISOR	PLITBLM
ALL_IND_SUBPARTITIONS	DBMS_ASSERT	PRVT_COMPRESS
ALL_LOBS	DBMS_COMPRESSION	USER_TABLESPACES
ALL_OBJECTS	DBMS_FEATURE_ADV_IDXCMP	WRI$_ADV_COMPRESSION_T
ALL_PART_INDEXES	DBMS_FEATURE_ADV_TABCMP	WRI$_ADV_FINDINGS
ALL_TABLES	DBMS_INTERNAL_LOGSTDBY	WRI$_ADV_MESSAGE_GROUPS
ALL_TAB_COLS	DBMS_LOB	WRI$_ADV_OBJECTS
ALL_TAB_COLUMNS	DBMS_OUTPUT	WRI$_ADV_SEQ_MSGGROUP
ALL_TAB_PARTITIONS

Constants

Name	Data Type	Value
COMP_RATIO_ALLROWS	BINARY_INTEGER	-1
COMP_TMP_OBJ_PREFIX	VARCHAR2	'CMP4$'

Documented No

First Available 11.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/prvtcmpr.plb

Subprograms

ADV_ANALYZE_TABLE	GET_LOB_COMPRESSION_RATIO	INIT_TASK_STATE
GET_ALLINDEX_COMPRESSION_RATIO	GET_NUM_PARTITIONS	OLTP_COMPRESSIBLE
GET_COMPRESSION_RATIO	GET_TABLE_COMPRESSION_RATIO	REPORT
GET_INDEX_COMPRESSION_RATIO

ADV_ANALYZE_TABLE

Undocumented prvt_compression.adv_analyze_table( tabowner IN VARCHAR2, tabname IN VARCHAR2, tabpart IN VARCHAR2, comptype IN NUMBER, scratchtbsname IN VARCHAR2);

set serveroutput on

      

      exec prvt_compression_adv_analyze_table('UWCLASS', 'SERVERS', NULL, 1, 'UWDATA');

      

      PLS-00114: identifier 'PRVT_COMPRESSION_ADV_ANALYZE_T' too long

GET_ALLINDEX_COMPRESSION_RATIO

Undocumented prvt_compression.get_allindex_compression_ratio( scratchtbsname IN VARCHAR2, ownname IN VARCHAR2, tabname IN VARCHAR2, comptype IN NUMBER, index_cr OUT dbms_compression.compreclist, comptype_str OUT VARCHAR2, subset_numrows IN NUMBER);

TBD

GET_COMPRESSION_RATIO

Undocumented prvt_compression.get_comression_ratio( scratchtbsname IN VARCHAR2, tabowner IN VARCHAR2, tabname IN VARCHAR2, tabpart IN VARCHAR2, comptype IN NUMBER, blkcnt_cmp OUT BINARY_INTEGER, blkcnt_uncmp OUT BINARY_INTEGER, row_cmp OUT BINARY_INTEGER, row_uncmp OUT BINARY_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2);

TBD

GET_INDEX_COMPRESSION_RATIO

Undocumented prvt_compression.get_index_compression_ratio( scratchtbsname IN VARCHAR2, indexowner IN VARCHAR2, indexname IN VARCHAR2, partname IN VARCHAR2, comptype IN NUMBER, blkcnt_cmp OUT BINARY_INTEGER, blkcnt_uncmp OUT BINARY_INTEGER, row_cmp OUT BINARY_INTEGER, row_uncmp OUT BINARY_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2, subset_numrows IN NUMBER);

TBD

GET_LOB_COMPRESSION_RATIO

Undocumented prvt_compression.get_lob_compression_ratio( scratchtbsname IN VARCHAR2, tabowner IN VARCHAR2, tabname IN VARCHAR2, lobname IN VARCHAR2, partname IN VARCHAR2, comptype IN NUMBER, blkcnt_cmp OUT BINARY_INTEGER, blkcnt_uncmp OUT BINARY_INTEGER, lobcnt OUT BINARY_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2, subset_numrows IN NUMBER);

SELECT dtc.table_name, dt.num_rows

      FROM dba_tab_cols dtc, dba_tables dt, dba_lobs dl

      WHERE dtc.data_type LIKE '%LOB%'

      AND dtc.owner = 'SYS'

      AND dtc.owner = dt.owner

      AND dtc.owner = dl.owner

      AND dtc.table_name = dt.table_name

      AND dtc.table_name = dl.table_name

      AND dtc.column_name = dl.column_name

      AND dl.securefile = 'NO'

      AND dt.num_rows > 0

      AND dtc.num_nulls < dt.num_rows

      ORDER BY 2;

      

      desc wri$_optstat_histhead_history

      

      DECLARE

       v1 BINARY_INTEGER;

       v2 BINARY_INTEGER;

       v3 BINARY_INTEGER;

       v4 NUMBER;

       v5 VARCHAR2(60);

      BEGIN

        FOR i IN 1.. 2 LOOP

          prvt_compression.get_lob_compression_ratio(

            'SYSTEM',

            'SYS',

            'WRH$_SQL_PLAN',

            'OTHER_XML',

            NULL,

            i,

            v1, v2, v3, v4, v5,

            1000);

      

          dbms_output.put_line('Comptype = ' || TO_CHAR(i));

          dbms_output.put_line(TO_CHAR(v1));

          dbms_output.put_line(TO_CHAR(v2));

          dbms_output.put_line(TO_CHAR(v3));

          dbms_output.put_line(TO_CHAR(v4));

          dbms_output.put_line(v5);

        END LOOP;

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-20000: Compression Advisor scratch tablespace must be space management auto

      ORA-06512: at "SYS.PRVT_COMPRESSION", line 1582

      ORA-06512: at line 9

      

      SELECT tablespace_name, segment_space_management

      FROM dba_tablespaces

      ORDER BY 2,1;

      

      DECLARE

       v1 BINARY_INTEGER;

       v2 BINARY_INTEGER;

       v3 BINARY_INTEGER;

       v4 NUMBER;

       v5 VARCHAR2(60);

      BEGIN

        FOR i IN 1.. 2 LOOP

          prvt_compression.get_lob_compression_ratio(

            'SYSAUX',

            'SYS',

            'WRH$_SQL_PLAN',

            'OTHER_XML',

            NULL,

            i,

            v1, v2, v3, v4, v5,

            1000);

      

          dbms_output.put_line('Comptype = ' || TO_CHAR(i));

          dbms_output.put_line(TO_CHAR(v1));

          dbms_output.put_line(TO_CHAR(v2));

          dbms_output.put_line(TO_CHAR(v3));

          dbms_output.put_line(TO_CHAR(v4));

          dbms_output.put_line(v5);

        END LOOP;

      END;

      /

      Sampling percent: 92.42

      DECLARE

      *

      ERROR at line 1:

      ORA-20000: Compression option is not supported for securefile lobs

      ORA-06512: at "SYS.PRVT_COMPRESSION", line 1582

      ORA-06512: at line 9

      

      DECLARE

       v1 BINARY_INTEGER;

       v2 BINARY_INTEGER;

       v3 BINARY_INTEGER;

       v4 NUMBER;

       v5 VARCHAR2(60);

      BEGIN

        FOR i IN 1.. 2 LOOP

          prvt_compression.get_lob_compression_ratio(

            'SYSAUX',

            'SYS',

            'TABPART$',

            'BHIBOUNDVAL',

            NULL,

            i,

            v1, v2, v3, v4, v5,

            100); -- note that this table contains only 363 rows so I've dropped the sample size

      

          dbms_output.put_line('Comptype = ' || TO_CHAR(i));

          dbms_output.put_line(TO_CHAR(v1));

          dbms_output.put_line(TO_CHAR(v2));

          dbms_output.put_line(TO_CHAR(v3));

          dbms_output.put_line(TO_CHAR(v4));

          dbms_output.put_line(v5);

        END LOOP;

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-20000: Compression Advisor sample size must be at least 1000. Use COMP_RATIO_ALLROWS if the table has fewer rows.

      ORA-06512: at "SYS.PRVT_COMPRESSION", line 1582

      ORA-06512: at line 9

      

      /* from this demo you can safely surmise that you must choose a tablespace with space management auto, a table that does not use securefile to store the LOB and a table with 1000 rows or greater. */

      

      I now have feedback from Oracle support and have tried using the COMP_RATIO_ALLROWS constant ... and there is yet another bug.

      

      

      SQL> DECLARE

      2     v1 BINARY_INTEGER;

      3     v2 BINARY_INTEGER;

      4     v3 BINARY_INTEGER;

      5     v4 NUMBER;

      6     v5 VARCHAR2(60);

      7    BEGIN

      8      FOR i IN 1.. 2 LOOP

      9        prvt_compression.get_lob_compression_ratio(

      10         'SYSAUX',

      11         'SYS',

      12         'TABPART$',

      13         'BHIBOUNDVAL',

      14         NULL,

      15         i,

      16         v1, v2, v3, v4, v5,

      17         prvt_compression.COMP_RATIO_ALLROWS);

      18

      19       dbms_output.put_line('Comptype = ' || TO_CHAR(i));

      20       dbms_output.put_line(TO_CHAR(v1));

      21       dbms_output.put_line(TO_CHAR(v2));

      22       dbms_output.put_line(TO_CHAR(v3));

      23       dbms_output.put_line(TO_CHAR(v4));

      24       dbms_output.put_line(v5);

      25     END LOOP;

      26   END;

      27   /

      Sampling percent: 100

      DECLARE

      *

      ERROR at line 1:

      ORA-20000: Compression option is not supported for securefile lobs

      ORA-06512: at "SYS.PRVT_COMPRESSION", line 1582

      ORA-06512: at line 9

      

      -- the error message is incorrect as can be seen here:

      

      SQL> SELECT securefile

         2 FROM dba_lobs

         3 WHERE table_name = 'TABPART$'

         4 AND column_name = 'BHIBOUNDVAL';

      

      SEC

      ---

      NO

GET_NUM_PARTITIONS

Returns the number of partitions in a table: 1 for a non-partitioned table prvt_compression.get_num_partitions( tabowner IN VARCHAR2, tabname IN VARCHAR2) RETURN NUMBER;

SELECT prvt_compression.get_num_partitions('UWCLASS', 'SERVERS')

      FROM dual;

GET_TABLE_COMPRESSION_RATIO

Undocumented prvt_compression.get_table_compression_ratio( scratchtbsname IN VARCHAR2, tabowner IN VARCHAR2, tabname IN VARCHAR2, partname IN VARCHAR2, comptype IN NUMBER, blkcnt_cmp OUT BINARY_INTEGER, blkcnt_uncmp OUT BINARY_INTEGER, row_cmp OUT BINARY_INTEGER, row_uncmp OUT BINARY_INTEGER, cmp_ratio OUT NUMBER, comptype_str OUT VARCHAR2, subset_numrows IN NUMBER);

CREATE TABLE c##abc.comptest

      COMPRESS FOR ALL OPERATIONS AS

      SELECT * FROM obj$;

      

      DECLARE

       v1 BINARY_INTEGER;

       v2 BINARY_INTEGER;

       v3 BINARY_INTEGER;

       v4 BINARY_INTEGER;

       v5 NUMBER;

       v6 VARCHAR2(60);

      BEGIN

        FOR i IN 1.. 2 LOOP

          prvt_compression.get_table_compression_ratio(

            'SYSTEM',

            'C##ABC',

            'COMPTEST',

            NULL,

            i,

            v1, v2, v3, v4, v5, v6,

            1000);

      

          dbms_output.put_line('Comptype = ' || TO_CHAR(i));

          dbms_output.put_line(TO_CHAR(v1));

          dbms_output.put_line(TO_CHAR(v2));

          dbms_output.put_line(TO_CHAR(v3));

          dbms_output.put_line(TO_CHAR(v4));

          dbms_output.put_line(TO_CHAR(v5));

          dbms_output.put_line(v6);

        END LOOP;

      END;

      /

INIT_TASK_STATE

Undocumented prvt_compression.init_task_state(task_id IN NUMBER, fin_id IN NUMBER);

TBD

OLTP_COMPRESSIBLE

Returns TRUE if a table is compressible, otherwise FALSE prvt_compression.oltp_compressible( p_owner IN VARCHAR2, p_table_name IN VARCHAR2) RETURN BOOLEAN;

set serveroutput on

      

      BEGIN

        IF prvt_compression.oltp_compressible('UWCLASS', 'SERVERS') THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

REPORT

Undocumented prvt_compression.report( taskid IN NUMBER, type IN VARCHAR2, level IN VARCHAR2) RETURN CLOB;

TBD