Online redefinition convert the Basicfiles to SecureFiles and vice-versa in LOB
Following are steps to show Convert Basicfiles to SecureFiles:
The following example show the convertion from basicfiles to securefiles by package DBMS REDEFINATION:
1. Create the user with dba privileges
Create user scott identified by tiger;
grant dba to scott;
2. Connect with scott user and create table with LOB segments.
CREATE TABLE cust (
c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);
3. Insert data into the created table.
INSERT INTO cust VALUES (1, 94065, 'hhh', 'ttt');
COMMIT;
4. Create table used for dbms redefination
CREATE TABLE cust_int (
c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
) LOB(c_LOB) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);
5. Start the redefination process
DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping := 'c_id c_id , '|| 'c_zip c_zip , '|| 'c_name c_name, '|| 'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/
6. COPY THE CONSTRAINTS FROM OUR ORIGINAL TABLE to new table.
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
7. Sync the old table with new table.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('pm', 'cust', 'cust_int');
END;
/
8. Finish the redefination process.
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');
9. Drop the new table
DROP TABLE cust_int;
10. Verify the table its convert to Secure.
SQL> SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'CUST';
SEC
---
YES
Note: Same steps follows for vice versa but table first created is SECUREFILE table and new table is BASICFILE table.