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
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);
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);
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);
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';
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);
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;
/