Oracle online redefinition (converting normal heap tables into partitioned tables)

 

1. Create a test table

Use the sys user to create a test table

SQL> CREATE TABLE HR. ST (ID NUMBER, TIME DATE );

Table created.

SQL> INSERT INTO HR. ST SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

50416 rows created.

SQL> commit;

Commit complete.

2. Authorization

SQL> grant execute on DBMS_REDEFINITION to hr;

Grant succeeded.

SQL> grant CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE to hr;

Grant succeeded.

3. Create an intermediate table (partition table structure)

SQL> create table mid_st (id number primary key, time date) partition by range (time)

2 (

3 partition p1 values less than (to_date ('1970-7-1 ', 'yyyy-mm-dd ')),

4 partition p2 values less than (to_date ('2017-1-1 ', 'yyyy-mm-dd ')),

5 partition p3 values less than (to_date ('1970-7-1 ', 'yyyy-mm-dd ')),

6 partition p4 values less than (maxvalue)

7 );

Table created

4. Online redefinition

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE (USER, 'st', 'mid _ st ');

PL/SQL procedure successfully completed

5. Data Synchronization (optional)

If a large number of DML operations are performed directly on the redefinition table during the execution of the DBMS_REDEFINITION.START_REDEF_TABLE () process and the execution of explain () process, you can choose to execute the SYNC_INTERIM_TABLE () process one or more times, to reduce the lock time for the FINISH_REDEF_TABLE () process in the last step.

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE (USER, 'st', 'mid _ st ');

PL/SQL procedure successfully completed

6. End redefinition

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (USER, 'st', 'mid _ st ');

PL/SQL procedure successfully completed

7. View partition results

SQL> select a. table_name, a. partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME

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

ST P1

ST P2

ST P3

ST P4

8. Discard online redefinition

After dbms_redefinition.start_redef_table is executed, run the following command before dbms_redefinition.finish_redef_table is executed:

DBMS_REDEFINITION.abort_redef_table (user, 'st', 'mid _ st') to discard online redefinition.

9. FAQs

9.1 No primary key

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (user, 'st', DBMS_REDEFINITION.CONS_USE_PK );

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE (user, 'st', DBMS_REDEFINITION.CONS_USE_PK); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "HR". "ST" with no primary key

ORA-06512: at "SYS. DBMS_REDEFINITION", line 137

ORA-06512: at "SYS. DBMS_REDEFINITION", line 1478

ORA-06512: at line 1

 

An error occurred. The table does not have a primary key. Create a primary key for the table and then perform verification.

SQL> alter table st add constraint pk_t primary key (id );

Table altered.

 

Delete the materialized view with this sentence to continue.

Drop materialized view log on <tablename>;

Drop materialized view log on t; OR drop materialized t;

9.2 unauthorized

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE (USER, 'st', 'mid _ st ');

Begin DBMS_REDEFINITION.START_REDEF_TABLE (USER, 'st', 'mid _ st'); end;

ORA-01031: insufficient privileges

ORA-06512: at "SYS. DBMS_REDEFINITION", line 50

ORA-06512: at "SYS. DBMS_REDEFINITION", line 1343

ORA-06512: at line 2

 

From Xiaowei's column