Follow Us on Twitter

Oracle Securefiles compress validation at row level

by Jos van den Oord on January 29, 2010 · 1 comment

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;
/
or use the numbers of the attributes
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
Now the same PL/SQL block but now for CONTRACT_ID=100
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);
Ratings:
VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

{ 0 comments… add one now }

Leave a Comment

 

{ 1 trackback }

Previous post:

Next post:

About Whitehorses
Company profile
Services
Technology

Whitehorses website

Home page
Whitebooks
Jobs

Follow us
Blog post RSS
Comment RSS
Twitter