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)