OCM tips – Migration of LOBs to SecureFiles

Reference:

HOW TO ONLINE CONVERT TO/FROM SECUREFILES FROM/TO BASICFILES (Doc ID 728758.1)

During an OCM exam you might be asked to convert a BasicFiles LOB to SecureFiles LOB. To perform this operation, you should use online redefinition feature. More detailed information about this feature was provided in the previous chapter. Check “Using Online Redefinition for Migrating Tables with BasicFiles LOBs” part of the documentation “Database SecureFiles and Large Objects Developer’s Guide – Chapter 4” to see an example related on how to migrate from BasicFiles to Securefiles LOB. Below, we will provide a simple example.

– Create a table with BasicFiles LOB column:

SQL> CREATE TABLE DBSNMP.tbl_basic_lob

(

  id NUMBER,

  clob_data CLOB

)

TABLESPACE users;

Table created.

SQL>

 

– Insert a few rows in it as we did in the previous example:

 

SQL> INSERT INTO DBSNMP.tbl_basic_lob

SELECT object_id, RPAD (‘ ‘, 5000, ‘This is test ‘)  FROM dba_objects WHERE ROWNUM < 3000;

2999 rows created.

SQL>

 

The following error will appear if you try to use SecureFiles LOB features on this table:

 

SQL>ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);

ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE)

*

ERROR at line 1:

ORA-43856: Unsupported LOB type for SECUREFILE LOB operation

 

Create a table with SecureFiles LOB option which will be used as in interim table:

 

SQL> CREATE TABLE DBSNMP.tbl_securefiles_lob

(

id NUMBER,

clob_data CLOB

)

LOB (clob_data) STORE AS SECUREFILE (TABLESPACE users);

Table created.

SQL>

 

Start the redefinition using DBMS_REDEFINITION.START_REDEF_TABLE procedure:

 

SQL> EXEC dbms_redefinition.start_redef_table(‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’,null,2);

PL/SQL procedure successfully completed.

 

Clone the dependent objects using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS procedure:

 

SQL> DECLARE

error_count PLS_INTEGER := 0;

BEGIN

DBMS_REDEFINITION.copy_table_dependents (‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’,

1, TRUE, TRUE, TRUE, FALSE, error_count);

DBMS_OUTPUT.put_line (‘errors := ‘ || TO_CHAR (error_count));

END;

/

PL/SQL procedure successfully completed.

 

Complete the redefinition process:

 

SQL> EXEC dbms_redefinition.finish_redef_table(‘DBSNMP’, ‘TBL_BASIC_LOB’, ‘TBL_SECUREFILES_LOB’);

PL/SQL procedure successfully completed.

 

Query DBA_LOBS view to make sure that the table is migrated to SecureFiles LOB:

 

SQL> SELECT securefile FROM dba_lobs WHERE table_name = ‘TBL_BASIC_LOB’;

SEC

---

YES

 

Now try the command you failed to run before to use SecureFiles option. The command will be running successfully:

 

SQL> ALTER TABLE DBSNMP.tbl_basic_lob MODIFY LOB (clob_data) (CACHE DEDUPLICATE);

Table altered.

SQL>

 

To get more detailed information on secure lobs and managing lob segments, get the OCM preparation book from the following link:

http://www.amazon.com/Oracle-Certified-Master-Exam-Guide/dp/1536800791/

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>