SECUREFILE Migration - Oracle 11g Feature

Step#1) Create a partition table:-

SQL> CREATE TABLE DOC_TAB (ID NUMBER, COLX CLOB )
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS LOBP1,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS LOBP2,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS LOBP3);

Table created.

Step#2) Insert data

SQL> INSERT INTO DOC_TAB VALUES (5,'XXX');

1 row created.

SQL> INSERT INTO DOC_TAB VALUES (15,'YYY');

1 row created.

SQL> INSERT INTO DOC_TAB VALUES (25,'ZZZ');

1 row created.

SQL> commit;

Commit complete.

Step#3) Create a transient table

SQL> CREATE TABLE DOC_TAB_TEMP (ID NUMBER, COLX CLOB)
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS SECUREFILE,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS SECUREFILE,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS SECUREFILE);

Table created.

Step#4) Table Redefinition

SQL> DECLARE
2 ERROR_COUNT NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.START_REDEF_TABLE(
5 UNAME=>'BABU',
6 ORIG_TABLE=>'DOC_TAB',
7 INT_TABLE=>'DOC_TAB_TEMP',
8 COL_MAPPING=>'ID ID,COLX COLX');
9 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
10 UNAME=>'BABU',
11 ORIG_TABLE=>'DOC_TAB',
12 INT_TABLE=>'DOC_TAB_TEMP',
13 COPY_INDEXES=>1,
14 COPY_TRIGGERS=>TRUE,
15 COPY_CONSTRAINTS=>TRUE,
16 COPY_PRIVILEGES=>TRUE,
17 IGNORE_ERRORS=>FALSE,
18 NUM_ERRORS=>ERROR_COUNT);
19 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
20 UNAME=>'BABU',ORIG_TABLE=>'DOC_TAB',INT_TABLE=>'DOC_TAB_TEMP');
21 DBMS_OUTPUT.PUT_LINE(ERROR_COUNT);
22 END;
23 /
DECLARE
*
ERROR at line 1:
ORA-12089: cannot online redefine table "BABU"."DOC_TAB" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1631
ORA-06512: at line 4

SQL> CREATE TABLE DOC_TAB (ID NUMBER PRIMARY KEY, COLX CLOB )
2 PARTITION BY RANGE (ID) (
3 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS_A LOB(COLX) STORE AS LOBP1,
4 PARTITION P2 VALUES LESS THAN (20) TABLESPACE TBS_B LOB(COLX) STORE AS LOBP2,
5 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS_C LOB(COLX) STORE AS LOBP3);

Table created.

Step#4) Table Redefinition (again)

SQL> DECLARE
2 ERROR_COUNT NUMBER;
3 BEGIN
4 DBMS_REDEFINITION.START_REDEF_TABLE(
5 UNAME=>'BABU',
6 ORIG_TABLE=>'DOC_TAB',
7 INT_TABLE=>'DOC_TAB_TEMP',
8 COL_MAPPING=>'ID ID,COLX COLX');
9 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
10 UNAME=>'BABU',
11 ORIG_TABLE=>'DOC_TAB',
12 INT_TABLE=>'DOC_TAB_TEMP',
13 COPY_INDEXES=>1,
14 COPY_TRIGGERS=>TRUE,
15 COPY_CONSTRAINTS=>TRUE,
16 COPY_PRIVILEGES=>TRUE,
17 IGNORE_ERRORS=>FALSE,
18 NUM_ERRORS=>ERROR_COUNT);
19 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
20 UNAME=>'BABU',ORIG_TABLE=>'DOC_TAB',INT_TABLE=>'DOC_TAB_TEMP');
21 DBMS_OUTPUT.PUT_LINE(ERROR_COUNT);
22 END;
23 /
0

PL/SQL procedure successfully completed.