Oracle Securefiles has extra options on performance and security on lob types. When those options are activated/decativated on table level, the process will execute the statement on the table for every Oracle Securefile which does not fulfill the option requirement. Oracle Securefile has an option for enabeling/disabeling of the options on ROW level of the table. In this example I will demonstrate how you can disable an option on row level. For this demo I’ll use the option for compression.
Summary
In the meta data you see that the option is activated. This will not guarantee that this is the state for every row in the table for the Oracle SecureFiles. To be sure you have to execute the alter table statement or a validate statement on every row in the table.
Here’s the example. First create a table with data:
create table securefile_tab
(contract_id number(12)
,contract_name varchar2(80)
,file_size number
,clob_data clob
) tablespace users
lob (clob_data) store as securefile sf_clob_data
(compress high
tablespace users
);
select l.owner,l.segment_name, s.bytes/1024/1024 Mb,l.compression,l.securefile
from dba_lobs l,
dba_segments s
where (l.owner||l.segment_name)=(s.owner||s.segment_name)
and l.owner ='JVDOORD';
OWNER SEGMENT_NAME MB COMP SEC
------------------------------ -------------------- ---------- ------ ---
JVDOORD SF_CLOB_DATA 0.125 HIGH YES
DECLARE
l_clob CLOB := RPAD('Y', 2000, 'Y');
BEGIN
FOR i IN 1 .. 1000 LOOP
if i=123
then
INSERT INTO securefile_tab(contract_id,contract_name,file_size,clob_data )
VALUES (i, 'Contract'||i,null,RPAD('X', 100000, 'X'));
ELSE
INSERT INTO securefile_tab(contract_id,contract_name,file_size,clob_data )
VALUES (i, 'Contract'||i,null,l_clob);
end if;
END LOOP;
COMMIT;
END;
/
Begin
FOR a in 1..12
LOOP
update securefile_tab
SET clob_data = clob_data||clob_data
WHERE CONTRACT_ID = 123;
commit;
END LOOP;
update securefile_tab
set file_size =dbms_lob.getlength(clob_data);
commit;
END;
/
See here the results of adding rows into the compressed lob:
OWNER SEGMENT_NAME MB COMP SEC ------------------------------ -------------------- ---------- ------ --- JVDOORD SF_CLOB_DATA 0.375 HIGH YES
Now set at ROW level the lob compression off for the row with CONTRACT_ID=123
DECLARE
l_clob CLOB;
BEGIN
SELECT clob_data
INTO l_clob
FROM securefile_tab
WHERE CONTRACT_ID = 123
FOR UPDATE;
DBMS_LOB.setoptions(l_clob,DBMS_LOB.opt_compress,0);
COMMIT;
END;
/
OWNER SEGMENT_NAME MB COMP SEC ------------------------------ -------------------- ---------- ------ --- JVDOORD SF_CLOB_DATA 35.375 HIGH YES
How to detect the lob compression or other setting at row level. Oracle has introduce new functions in the DBMS_LOB package for setting the attributes on ROW level. The following attributes for the option are possible for Oracle Securefile:
Option
- opt_compress => 1
- opt_encrypt => 2
- opt_deduplicate => 4
To Enable or Disable the functionality of the lob options at row level:
- compress_off – use value => 0
- compress_on – use value => 1
- encrypt_off – use value => 0
- encrypt on – use value => 2
- deduplicate_off – use value => 0
- deduplicate_on – use value => 4
Here is the PL/SQL block to validate the Oracle Securefile status at ROW level of the object:
set outputserver on
DECLARE
l_clob CLOB;
BEGIN
SELECT clob_data
INTO l_clob
FROM securefile_tab
WHERE CONTRACT_ID = 123
FOR UPDATE;
DBMS_OUTPUT.put_line('Compression : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_compress));
DBMS_OUTPUT.put_line('Encryption : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_encrypt));
DBMS_OUTPUT.put_line('Deduplication: ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_deduplicate));
ROLLBACK;
END;
/
DECLARE
l_clob CLOB;
BEGIN
SELECT clob_data
INTO l_clob
FROM securefile_tab
WHERE CONTRACT_ID = 123
FOR UPDATE;
DBMS_OUTPUT.put_line('Compression : ' || DBMS_LOB.getoptions(l_clob, 1));
DBMS_OUTPUT.put_line('Encryption : ' || DBMS_LOB.getoptions(l_clob, 2));
DBMS_OUTPUT.put_line('Deduplication: ' || DBMS_LOB.getoptions(l_clob, 4));
ROLLBACK;
END;
/
The above PL/SQL gives the following output:
Compression: 0 Encryption : 0 Deduplication : 0
Compression: 1 Encryption : 0 Deduplication : 0
Execute the following statement will solve the uncompressed data of possible lobs of the object:
alter table securefile_tab modify lob(clob_data) (compress high);
{ 1 trackback }
{ 0 comments… add one now }