Online redefinition convert the Basicfiles to SecureFiles and vice-versa in LOB

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.