ONLINE Table Redefinition

Yesterday I have restructured one of my normal table to partitioned table. There are two ways to do that :-

1. Export - Import method.
2. Online table Redefinition

1. Export - Import Method :-

This method can be used when we can take downtime of that table :-

a. Export the normal table

expdp user/password directory=datapump dumpfile=expdp_table.dmp logfile=expdp_table.log tables=TEST compression=all &

b. create the partitioned table as the same structure of normal table

c. Drop the normal table

d . Import the dumpfile to the Partition table.

impdp user/password directory=datapump dumpfile=expdp_table.dmp logfile=impdp.log &

 2. Online table Redefinition :-

This method is used when downtime option is not available with us. But to redefine a table by online table redefinition , table must have a primary key.

Consider a table named test has to be redefined. PFB steps:-

1. create a partitioned table having same structure as TEST table has.

SQL> create table test_test (
   2  id number(23),
  3  name varchar2(12),
  4  systemdate date,
  5  id_old number(12)
  6  )
  7  partition by list(id)
  8  (
  9  partition par_01 values (1,2,3),
 10  partition par_02 values (4,5,6),
 11  partition par_03 values (7,8,9),
 12  partition par_04 values (10,11,12)
 13  );

Table created.

2. Check whether table TEST can be redefined or not.if table which has to be redefined don't have any issues then it will give you successful message else error message.

SQL> EXEC DBMS_REDEFINITION.can_redef_table('AMIT','TEST');

PL/SQL procedure successfully completed.
######### TEST table can be redefined #################


SQL> EXEC DBMS_REDEFINITION.can_redef_table('AMIT','TEST_TEST');
BEGIN DBMS_REDEFINITION.can_redef_table('AMIT','TEST_TEST'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "AMIT"."TEST_TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1
############## TEST_TEST table cannot be redefined due to above error##########

3. Start the redefinition :-

SQL> EXEC DBMS_REDEFINITION.start_redef_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.


SQL> SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
SQL>   2    3  BEGIN
 4   DBMS_REDEFINITION.copy_table_dependents(
 5   uname             => 'AMIT',
 6   orig_table        => 'TEST',
 7   int_table         => 'TEST_TEST',
 8   copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
 9   copy_triggers     => TRUE,  -- Default
10   copy_constraints  => TRUE,  -- Default
11   copy_privileges   => TRUE,  -- Default
12   ignore_errors     => FALSE, -- Default
13   num_errors        => l_num_errors);
14   DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
15  END;
16  /
l_num_errors=0

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('AMIT','TEST','TEST_TEST');

PL/SQL procedure successfully completed.


SQL> SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'AMIT' and object_name='TEST';

OBJECT_NAME                                  OBJECT_TYPE                         STATUS
---------------------------------         ---------------------------------          ---------------------


TEST                                                   TABLE                                        VALID

TEST                                                  TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID

TEST                                                 TABLE PARTITION                     VALID


SQL> select index_name ,table_name,column_name from user_ind_columns where table_name='TEST';

INDEX_NAME                            TABLE_NAME                     COLUMN_NAME

----------------------------------     ------------------------------------     ------------------------------------------


TEST_IDX                                   TEST                                   ID_OLD

TEST_PK                                    TEST                                   ID


SQL>  select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='TEST';

TABLE_NAME                       PARTITION_NAME                                             TABLESPACE_NAME                               NUM_ROWS
---------------------------------     ------------------------------------------           ------------------------------                                          ------------------------
TEST                                                     PAR_01                                                USERS
TEST                                                     PAR_02                                                USERS
TEST                                                     PAR_03                                                USERS
TEST                                                    PAR_04                                                 USERS



SQL> exec dbms_stats.gather_table_stats('AMIT','TEST',granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='TEST';


TABLE_NAME                           PARTITION_NAME                                TABLESPACE_NAME                                        NUM_ROWS
-----------------------------------     ------------------------------------------    --------------------------------------------                                          ------------------

TEST                                            PAR_01                                    USERS                                                                                        3
TEST                                            PAR_02                                    USERS                                                                                        1
TEST                                            PAR_03                                    USERS                                                                                        2
TEST                                            PAR_04                                     USERS                                                                                        0

Once data reflects in our partitioned table . Drop the newly created table i,e TEST_TEST

I hope this article helped you.

Regards,
Amit Rath