Oracle DBMS_COMPRESSION
Version 12.1.0.2

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 package supports advanced compression features new as of 11gR2 and the Oracle-Sun Exadata Server
AUTHID CURRENT_USER
Constants
Name Data Type Value
COMP_NOCOMPRESS NUMBER 1
COMP_ADVANCED NUMBER 2
COMP_QUERY_HIGH NUMBER 4 - Exadata Only
COMP_QUERY_LOW NUMBER 8 - Exadata Only
COMP_FOR_ARCHIVE_HIGH NUMBER 16 - Exadata Only
COMP_FOR_ARCHIVE_LOW NUMBER 32 - Exadata Only
COMP_BLOCK NUMBER 64 - Pillar Axiom & Sun ZFSSA Storage Only
COMP_LOB_HIGH NUMBER 128
COMP_LOB_MEDIUM NUMBER 256
COMP_LOB_LOW NUMBER 512
COMP_INDEX_ADVANCED_HIGH NUMBER 1024
COMP_INDEX_ADVANCED_LOW NUMBER 2048
COMP_BASIC NUMBER 4096
COMP_INMEMORY_NOCOMPRESS NUMBER 8192
COMP_INMEMORY_DML NUMBER 16384
COMP_INMEMORY_QUERY_LOW NUMBER 32768
COMP_INMEMORY_QUERY_HIGH NUMBER 65536
COMP_INMEMORY_CAPACITY_LOW NUMBER 131072
COMP_INMEMORY_CAPACITY_HIGH NUMBER 262144
COMP_RATIO_MINROWS NUMBER 1000000
COMP_RATIO_ALLROWS NUMBER -1
COMP_RATIO_LOB_MINROWS NUMBER 1000
COMP_RATIO_LOB_MAXROWS NUMBER 5000
COMP_RATIO_INDEX_MINROWS NUMBER 100000
OBJTYPE_TABLE NUMBER 1
OBJTYPE_INDEX NUMBER 2
OBJTYPE_PART NUMBER 2
OBJTYPE_SUBPART NUMBER 3
Data Types --record for calculating an individual index cr on a table
TYPE compRec IS RECORD(
ownname      VARCHAR2(255),
objname      VARCHAR2(255),
blkcnt_cmp   PLS_INTEGER,
blkcnt_uncmp PLS_INTEGER,
row_cmp      PLS_INTEGER,
row_uncmp    PLS_INTEGER,
cmp_ratio    NUMBER,
objtype      PLS_INTEGER);

TYPE compRecList IS TABLE OF compRec;
Dependencies
DBA_OBJECTS DBMS_OUTPUT SEG$
DBA_TABLES DBMS_ROWID WRI$_ADV_COMPRESSION_T
DBMS_ADVISOR PRVT_COMPRESSION WRI$_ADV_OBJSPACE_TREND_T
DBMS_ASSERT    
Documented Yes
First Available 11.2.0.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmscomp.sql
Subprograms
 
DUMP_COMPRESSION_MAP (new 12.1.0.2)
Undocumented but appears to be Exadata specific based on the acceptable comptype constants dbms_compression.dump_compression_map(
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
comptype IN NUMBER);
CREATE TABLE comptab AS
SELECT /* APPEND */ * FROM source$;

exec dbms_compression.dump_compression_map('SYS', 'COMPTAB', dbms_compression.comp_query_high);
 
GET_COMPRESSION_RATIO
Gives the possible compression ratio for an uncompressed table or materialized view and estimates the compression that can be achieved dbms_compression.get_compression_ratio(
scratchtbsname IN  VARCHAR2,
ownname        IN  VARCHAR2,
objname        IN  VARCHAR2,
subobjname     IN  VARCHAR2, -- partition name
comptype       IN  NUMBER,
blkcnt_cmp     OUT PLS_INTEGER,
blkcnt_uncmp   OUT PLS_INTEGER,
row_cmp        OUT PLS_INTEGER,
row_uncmp      OUT PLS_INTEGER,
cmp_ratio      OUT NUMBER,
comptype_str   OUT VARCHAR2,
subset_numrows IN  NUMBER      DEFAULT COMP_RATIO_MINROWS,
objtype        IN  PLS_INTEGER DEFAULT OBJTYPE_TABLE);
conn sys@pdbdev as sysdba

CREATE TABLE uwclass.comp_test1 AS
SELECT /* APPEND */ * FROM dba_objects_ae;

set serveroutput on

DECLARE
 blkcnt_cmp   PLS_INTEGER;
 blkcnt_uncmp PLS_INTEGER;
 row_comp     PLS_INTEGER;
 row_uncmp    PLS_INTEGER;
 cmp_ratio    NUMBER;
 comptype     VARCHAR2(30);
BEGIN
  dbms_compression.get_compression_ratio('SYSTEM', 'SYS', 'SOURCE$', NULL, dbms_compression.comp_advanced, blkcnt_cmp, blkcnt_uncmp, row_comp, row_uncmp, cmp_ratio, comptype);

  dbms_output.put_line('Block Count Compressed:   ' || TO_CHAR(blkcnt_cmp));
  dbms_output.put_line('Block Count UnCompressed: ' || TO_CHAR(blkcnt_uncmp));
  dbms_output.put_line('Row Count Compressed:     ' || TO_CHAR(row_comp));
  dbms_output.put_line('Row Count UnCompressed:   ' || TO_CHAR(row_uncmp));
  dbms_output.put_line('Block Count Compressed:   ' || TO_CHAR(cmp_ratio));
  dbms_output.put_line('Compression Type:         ' || comptype);
END;
/
Overload 2 dbms_compression.get_compression_ratio(
scratchtbsname IN  VARCHAR2,
tabowner       IN  VARCHAR2,
tabname        IN  VARCHAR2,
lobname        IN  VARCHAR2,
partname       IN  VARCHAR2,
comptype       IN  NUMBER,
blkcnt_cmp     OUT PLS_INTEGER,
blkcnt_uncmp   OUT PLS_INTEGER,
lobcnt         OUT PLS_INTEGER,
cmp_ratio      OUT NUMBER,
comptype_str   OUT VARCHAR2,
subset_numrows IN  number DEFAULT COMP_RATIO_LOB_MAXROWS);
TBD
Overload 3 dbms_compression.get_compression_ratio(
scratchtbsname IN  VARCHAR2,
ownname        IN  VARCHAR2,
tabname        IN  VARCHAR2,
comptype       IN  NUMBER,
index_cr       OUT compRecList,
comptype_str   OUT VARCHAR2,
subset_numrows IN  NUMBER DEFAULT COMP_RATIO_MINROWS);
TBD
 
GET_COMPRESSION_TYPE
Inspects data and reports what compression type is in use for a specific row dbms_compression.get_compression_type(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
row_id  IN ROWID)
RETURN NUMBER;
conn sys@pdbdev as sysdba

CREATE TABLE uwclass.comp_test2
COMPRESS FOR OLTP AS
SELECT * FROM dba_objects_ae;

set serveroutput on

DECLARE
 rid ROWID;
 n   NUMBER;
BEGIN
  SELECT MAX(rowid)
  INTO rid
  FROM uwclass.comp_test2;

  n := dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST2', rid);
  dbms_output.put_line(TO_CHAR(n));
END;
/
 
INCREMENTAL_COMPRESS
Allows turning compression of on and off and compressing uncompressed data offline, in the background)

This undocumented proc must be run as SYS and appears to have a bug.

Dump is an optional parameter that dumps out the space saved in each block into the trace files. It is turned  OFF by default (set to 0). It is advised not to turn this feature on for large tables or partitions because of excessive logging.
dbms_compression.incremental_compress(
ownname         IN dba_objects.owner%TYPE,
tabname         IN dba_objects.object_name%TYPE,
tabpart         IN dba_objects.subobject_name%TYPE,
colname         IN VARCHAR2,
dump_on         IN NUMBER   DEFAULT 0,
autocompress_on IN NUMBER   DEFAULT 0,
where_clause    IN VARCHAR2 DEFAULT '');
conn sys@pdbdev as sysdba

UPDATE uwclass.comp_test1
SET namespace = 100;

exec dbms_compression.incremental_compress('UWCLASS', 'COMP_TEST1', NULL, 'OBJECT_NAME', 1, 0, '''WHERE namespace=200''');

INSERT INTO uwclass.comp_test1
SELECT * FROM dba_objects_ae;

UPDATE uwclass.comp_test1
SET namespace = 200
WHERE namespace <> 100;

SELECT MAX(rowid) FROM uwclass.comp_test1 WHERE namespace = 100;
SELECT MAX(rowid) FROM uwclass.comp_test1 WHERE namespace = 200;

DECLARE
 rid100 ROWID;
 rid200 ROWID;
 n      NUMBER;
BEGIN
  SELECT MAX(rowid)
  INTO rid100
  FROM uwclass.comp_test1
  WHERE namespace = 100;

  n := dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST1', rid100);
  dbms_output.put_line(n);

  SELECT MAX(rowid)
  INTO rid200
  FROM uwclass.comp_test1
  WHERE namespace = 200;

  n := dbms_compression.get_compression_type('UWCLASS', 'COMP_TEST1', rid200);
  dbms_output.put_line(n);
END;
/

Related Topics
Advanced Compression
DBMS_COMPRESSION
Exadata
Hybrid Columnar Compression
PRVT_COMPRESS
PRVT_COMPRESSION
Packages
Secure Files
ZFS Storage Appliance