Oracle PRVT_COMPRESSION
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose This internal package supports advanced compression features introduced beginning with 11gR1
AUTHID CURRENT_USER
Dependencies
ALL_EXTERNAL_TABLES ALL_TAB_PARTITIONS DBMS_STANDARD
ALL_INDEXES ALL_TAB_SUBPARTITIONS DUAL
ALL_IND_COLUMNS ALL_VARRAYS PLITBLM
ALL_IND_PARTITIONS DBA_OBJECTS USER_TABLESPACES
ALL_IND_SUBPARTITIONS DBMS_ADVISOR V$PARAMETER
ALL_LOBS DBMS_ASSERT WRI$_ADV_COMPRESSION_T
ALL_OBJECTS DBMS_COMPRESSION WRI$_ADV_FINDINGS
ALL_PART_INDEXES DBMS_LOB WRI$_ADV_MESSAGE_GROUPS
ALL_TABLES DBMS_OUTPUT WRI$_ADV_OBJECTS
ALL_TAB_COLS DBMS_SPACE WRI$_ADV_SEQ_MSGGROUP
ALL_TAB_COLUMNS    
Constants
Name Data Type Value
COMP_RATIO_ALLROWS BINARY_INTEGER -1
Documented No
First Available 11.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/prvtcmpr.plb
Subprograms
 
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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


-- but 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 (new 12.1)
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

Related Topics
Advanced Compression
Hybrid Columnar Compression
Packages
PRVT_COMPRESS
Secure Files

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved