ORACLE online table redefinition

ORACLE in practice, due to the increase of business, the amount of data of some tables that were not planned to be very large has greatly increased. In order to optimize these tables, we need to change these tables from ordinary tables to partitioned tables. Now let's introduce a technique that comes with ORACLE to deal with this situation-this technique is called online table redefinition.

The first step is to create a temporary table:

Create table test_table

(ID NUMEBER (10) NOT NULL

STATUS VARCHAR2 (30) NULL

CREATE_DATE DATE

) PARTITION BY RANGE (CREATE_DATE)

(PARTITION part23 VALUES LESS THAN (TO_DATE ('2010-09-24)

PARTITION part24 VALUES LESS THAN (TO_DATE ('2010-09-25,' YYYY-MM-DD'))

PARTITION Part25 VALUES LESS THAN (TO_DATE ('2010-09-26,' YYYY-MM-DD'))

PARTITION partmax VALUES LESS THAN (MAXVALUE)

);

Alter table test_table add constraint test_table_pk primary key (id)

The second step is to determine whether the target data table can be redefined. Use the CAN_REDEF_TABLE method of the DBMS_REDEFINITION package to judge.

Exec dbms_redefinition.can_redef_table ('user','table',dbms_redefinition.cons_use_pk)

The materialized view created using cons_use_pk redefinition is based on the normal refresh mode.

You can also use ROWID's method.

Exec dbms_redefinition.can_redef_table ('user','table',dbms_redefinition.cons_use_rowid)

Materialized views created when using cons_use_rowid redefinition are refreshed based on ROWID.

The third step begins to redefine

Exec dbms_redefinition.start_redef_table ('user',' table', 'test_table')

Synchronize data in the temporary table with the original table

Exec dbms_redefinition.sync_interim_table ('user',' table', 'test_table')

Start copying the properties of the table

DeclareCLAR

Num_errors PLS_INTEGER

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('user','table','test_table'

DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, num_errors)

END

/

Including indexes, rules, promoters

Complete the redefinition

Exec dbms_redefinition.finish_redef_table ('user',' table', 'test_table')

_ Competition)