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
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
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
