Deduplication Using SecureFile for LOBS in 11g

In my previous blog, I had moved a lob from basicfile to securefile. I will use the same table for the dedupication example.
I had five rows in that table, 3 had similar lob data.

Storage Stats of the table:
SQL> select owner,table_name,COLUMN_NAME,SEGMENT_NAME from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ---------- --------------- --------------------------------------------------
HR BLOB_TEST DATA SYS_LOB0000073688C00002$$

The below plsql block is courtesy oracle
declare
l_segment_size_blocks number;
l_segment_size_bytes number;
l_used_blocks number;
l_used_bytes number;
l_expired_blocks number;
l_expired_bytes number;
l_unexpired_blocks number;
l_unexpired_bytes number;

begin
dbms_space.space_usage(
segment_owner => 'HR',
segment_name => 'SYS_LOB0000073688C00002$$',
segment_type => 'LOB',
partition_name => NULL,
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
dbms_output.put_line('Segment Size in Blocks => '|| l_segment_size_blocks);
dbms_output.put_line('Segment Size in Bytes => '|| l_segment_size_bytes);
dbms_output.put_line('Used Blocks => '|| l_used_blocks);
dbms_output.put_line('Used Bytes => '|| l_used_bytes);
dbms_output.put_line('Expired Blocks => '|| l_expired_blocks);
dbms_output.put_line('Expired Bytes => '|| l_expired_bytes);
dbms_output.put_line('Unexpired Blocks => '|| l_unexpired_blocks);
dbms_output.put_line('Unexpired Bytes => '|| l_unexpired_bytes);
end;
/
============================================
Segment Size in Blocks => 189848
Segment Size in Bytes => 1555234816
Used Blocks => 167392
Used Bytes => 1371275264 (1307.75 MB)
Expired Blocks => 22246
Expired Bytes => 182239232
Unexpired Blocks => 0
Unexpired Bytes => 0
============================================
SQL> alter table blob_test modify lob(data) (deduplicate);
Table altered.

This command takes some time to run.
Storage Stats after the deduplication.
============================================
Segment Size in Blocks => 416408
Segment Size in Bytes => 3411214336
Used Blocks => 55799
Used Bytes => 457105408 (435.93 MB)
Expired Blocks => 80882
Expired Bytes => 662585344
Unexpired Blocks => 279292
Unexpired Bytes => 2287960064
============================================

You can see that the Used Bytes column has come down visibly. As I had mentioned that there were 3 LOBs of the same size, which shows that the size has come down to 1/3rd.
The blob stored was:
a.dmp, b.dmp and c.dmp the same ones with size 428.88

Move from BasicFiles to Securefiles 11g

Let do the change from a BasicFile to SecureFile in 11g using a very basic example.

Lets create a table with BLOB using BasicFile.

SQL> create table BLOB_test (name varchar2(20),data BLOB);
Table created.

Lets load some data into the table.

SQL> create directory FOR_HR as '/home/oracle/';
Directory created.

SQL> grant read,write on directory FOR_HR to hr;
Grant succeeded.

I am using the below procedure to quickly load a few files:

CREATE OR REPLACE PROCEDURE blob_load (name IN BLOB_test.name%TYPE)
IS
b_data BFILE;
e_blob BLOB;
BEGIN
insert into BLOB_test values (name,EMPTY_BLOB() ) returning data into e_blob;
b_data := bfilename( 'FOR_HR', name);
Dbms_Lob.Fileopen(b_data, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(e_blob, b_data, Dbms_Lob.Getlength(b_data));
Dbms_Lob.Fileclose(b_data);
COMMIT;
END;
/

SQL> exec blob_load('a.dmp');
PL/SQL procedure successfully completed.

......
SQL> select count(*) from blob_test;

COUNT(*)
----------
5

Now lets try to migrate to SecureFiles in 11g:
First check the system parameter db_securefile:
SQL> show parameter db_securefile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_securefile string PERMITTED

Lets create a new table with SecureFile feature.

SQL> create table blob_test_1 (name varchar2(20),data blob)
lob (data) store as securefile; 2
Table created.

Now we need to use the dbms_redefinition package to define the column mappings:

SQL> exec dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data');
BEGIN dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HR"."BLOB_TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1631
ORA-06512: at line 1

SQL> alter table blob_test add primary key (name);
Table altered.

SQL> alter table blob_test_1 add primary key (name);
Table altered.

SQL> exec dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data');
PL/SQL procedure successfully completed.

Details of activity from v$sesstat, where 122 is our session id.

122 securefile allocation bytes 1,371,267,072
122 securefile allocation chunks 1,255
122 securefile direct write bytes 1,371,267,072
122 securefile direct write ops 1,809
122 securefile bytes non-transformed 1,349,152,270
122 securefile number of non-transformed flushes 326

SQL> select name from blob_test;
NAME
--------------------
a.dmp
b.dmp
c.dmp
d.log
e.log

SQL> select name from blob_test_1;
NAME
--------------------
a.dmp
b.dmp
c.dmp
d.log
e.log

SQL> exec dbms_redefinition.finish_redef_table ('HR','BLOB_TEST','BLOB_TEST_1');
PL/SQL procedure successfully completed.

SQL> select owner,table_name,COLUMN_NAME,SECUREFILE from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEC
---------- ---------- --------------- ---
HR BLOB_TEST DATA YES

SQL> drop table blob_test_1;
drop table blob_test_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
(The temporary table got removed automatically)