| General Information |
| Note: This package supports advanced compression features new to 11gR2 and the Oracle-Sun Exadata Server |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmscomp.sql |
| First Available |
11.2.0.1 |
| Constants |
| Name |
Data Type |
Value |
| COMP_NOCOMPRESS |
NUMBER |
1 |
| COMP_FOR_OLTP |
NUMBER |
2 |
| COMP_FOR_QUERY_HIGH |
NUMBER |
4 - Exadata Only |
| COMP_FOR_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_RATIO_ALLROWS |
NUMBER |
-1 |
| COMP_RATIO_MINROWS |
NUMBER |
1000000 |
|
| Dependencies |
| ALL_ALL_TABLES |
DBA_OBJECTS |
DBMS_OUTPUT |
| ALL_OBJECT_TABLES |
DBA_OBJECTS_AE |
DBMS_ROWID |
| ALL_PART_TABLES |
DBA_PART_TABLES |
PRVT_COMPRESSION |
| ALL_TABLES |
DBA_TABLES |
SEG$ |
| ALL_TAB_PARTITIONS |
DBA_TAB_PARTITIONS |
USER_ALL_TABLES |
| ALL_TAB_SUBPARTITIONS |
DBA_TAB_SUBPARTITIONS |
USER_OBJECT_TABLES |
| DBA_ALL_TABLES |
DBMS_ADVISOR |
WRI$_ADV_COMPRESSION_T |
| DBA_OBJECT_TABLES |
DBMS_ASSERT |
WRI$_ADV_OBJSPACE_TREND_T |
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
|
| |
GET_COMPRESSION_RATIO (new 11.2.0.2 parameter)  |
|
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,
tabname IN VARCHAR2,
partname IN VARCHAR2,
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); |
CREATE TABLE comp_test1 AS
SELECT * FROM dba_objects_ae;
set serveroutput on
DECLARE
blkcnt_comp PLS_INTEGER;
blkcnt_uncm PLS_INTEGER;
row_comp PLS_INTEGER;
row_uncm PLS_INTEGER;
comp_ratio PLS_INTEGER;
comp_type VARCHAR2(30);
BEGIN
dbms_compression.get_compression_ratio('UWDATA', 'UWCLASS', 'COMP_TEST1', NULL,
dbms_compression.comp_for_oltp, blkcnt_cmp, blkcnt_uncmp, row_comp, row_uncm, comp_ratio, comp_type);
dbms_output.put_line('Block Count Compressed: ' || TO_CHAR(blkcnt_comp);
dbms_output.put_line('Block Count UnCompressed: ' || TO_CHAR(blkcnt_uncm);
dbms_output.put_line('Row Count Compressed: ' || TO_CHAR(row_comp);
dbms_output.put_line('Row Count UnCompressed: ' || TO_CHAR(row_uncm);
dbms_output.put_line('Block Count Compressed: ' || TO_CHAR(comp_ratio);
dbms_output.put_line('Compression Type: ' || comp_type;
END;
/ |
| |
| 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; |
CREATE TABLE 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 comp_test2;
n := dbms_compression.get_compression_type(USER, 'COMP_TEST2', rid);
dbms_output.put_line(n);
END;
/
CREATE TABLE uwclass.comp_test2
COMPRESS FOR OLTP AS
SELECT * FROM dba_objects_ae; |
| |
| 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. |
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 ''); |
UPDATE comp_test1
SET namespace = 100;
exec dbms_compression.incremental_compress(USER, 'COMP_TEST1', NULL, 'OBJECT_NAME', 1, 0, '''WHERE namespace=200''');
INSERT INTO comp_test1
SELECT * FROM dba_objects_ae;
UPDATE comp_test1
SET namespace = 200
WHERE namespace <> 100;
SELECT MAX(rowid) FROM comp_test1 WHERE namespace = 100;
SELECT MAX(rowid) FROM comp_test1 WHERE namespace = 200;
DECLARE
rid100 ROWID;
rid200 ROWID;
n NUMBER;
BEGIN
SELECT MAX(rowid)
INTO rid100
FROM comp_test1
WHERE namespace = 100;
n := dbms_compression.get_compression_type(USER, 'COMP_TEST1', rid100);
dbms_output.put_line(n);
SELECT MAX(rowid)
INTO rid200
FROM comp_test1
WHERE namespace = 200;
n := dbms_compression.get_compression_type(USER, 'COMP_TEST1', rid200);
dbms_output.put_line(n);
END;
/ |
