Oracle PRVT_COMPRESSION
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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

Related Topics
Advanced Compression
Built-in Functions
Built-in Packages
Hybrid Columnar Compression
PRVT_COMPRESS
Secure Files
What's New In 19c
What's New In 20c-21c

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-2021 Daniel A. Morgan All Rights Reserved
  DBSecWorx