ORACLE Online Table redefinition

ORACLE in fact some of the tables due to the increase in traffic, some of the original planning is not very large data volume of the table, in order to optimize the table from the ordinary table into a partitioned table. Now let's introduce a technique that Oracle comes with to handle this situation-a technique called online table redefinition.

The first step is to create a temporary table:

CREATE TABLE Test_table

(ID Numeber (Ten) is not NULL,

STATUS VARCHAR2 (+) NULL,

Create_date DATE

) PARTITION by RANGE (create_date)
(PARTITION part23 VALUES less THAN (to_date (' 2010-09-24 ', ' yyyy-mm-dd '),
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 Dbms_redefinition package of the Can_redef_table method to judge.

EXEC dbms_redefinition.can_redef_table (' User ', ' table ', DBMS_REDEFINITION.CONS_USE_PK);

Materialized views created with CONS_USE_PK redefined are based on the normal refresh mode.

You can also use the rowID method

EXEC dbms_redefinition.can_redef_table (' User ', ' table ', dbms_redefinition.cons_use_rowid);

Materialized views created with Cons_use_rowid redefined are refreshed based on ROWID.

The third step starts redefining

EXEC dbms_redefinition.start_redef_table (' User ', ' table ', ' test_table ');

Synchronizing temporary tables with data from the original table

EXEC dbms_redefinition.sync_interim_table (' User ', ' table ', ' test_table ');

Start copying properties of a table

DECLARECLAR

Num_errors Pls_integer;

BEGIN

Dbms_redefinition. Copy_table_dependents (' user ', ' TABLE ', ' test_table ',

Dbms_redefinition. Cons_orig_params, True, True, true, true, num_errors);

END;

/

Include indexes, rules, hair enhancers

Finish redefining

EXEC dbms_redefinition.finish_redef_table (' User ', ' table ', ' test_table ');

_PK);

This article is from the "12024114" blog, please be sure to keep this source http://12034114.blog.51cto.com/12024114/1879218

ORACLE Online Table redefinition