Oracle 11g online redefine normal table variable partition table

--create user test identified by 1 account unlock;

--grant Resource

--grant Create any table, alter any table, drop any table, lock any table, select all table to test;

--<1> Create a test table, the following uses online redefinition to convert a table to a partitioned table, created to a partition key, object_id to a primary key

drop table test01 Purge;

CREATE TABLE test01 as SELECT * from Dba_objects where object_id are NOT null;

ALTER TABLE TEST01 add primary key (OBJECT_ID);

SELECT * from test01;

--<2> Creating a partitioned table

CREATE TABLE Test01_new

Partition by range (created)

--interval (Numtoyminterval (1, ' month '))

Interval (numtodsinterval (1, ' Day '))

Store in (users)

(

Partition P0 values less than (to_date (' 2008-01-01 ', ' yyyy-mm-dd ')

)

As SELECT * from test01 where created are not null and 1!=1;

ALTER TABLE Test01_new add primary key (OBJECT_ID);

--Determine if the target table can be redefined online using the primary key, or you can use the ROWID

exec dbms_redefinition.can_redef_table (' TEST ', ' TEST01 ', DBMS_REDEFINITION.CONS_USE_PK);

--Change the definition of the test01 table of the user test to the definition of the Test01_new table

exec dbms_redefinition.start_redef_table (' TEST ', ' TEST01 ', ' test01_new ');

--Synchronize the intermediate table with the original table. (You need to do this only if you want to update the table TEST01.) ）

exec dbms_redefinition.sync_interim_table (' TEST ', ' TEST01 ', ' test01_new ');

--End Redefine table

exec dbms_redefinition.finish_redef_table (' TEST ', ' TEST01 ', ' test01_new ');

--If the redefinition fails, unlock

exec dbms_redefinition.abort_redef_table (' TEST ', ' TEST01 ', ' test01_new ');

--Advantages:

--Ensure the consistency of the data, and for most of the time, the DML operations can be performed normally by the table T.

--only in the moment of switching lock table, with high availability. This approach is highly flexible and can be met for a variety of different needs.

-Furthermore, you can authorize and establish various constraints before switching, so that no additional administrative action is required after the switchover is complete.

--

--Inadequate: implementation is slightly more complex than the above two, applicable to a variety of situations.

However, online table redefinition is not perfect. Some of the limitations of the oracle9i redefinition process are listed below:

-You must have enough space to maintain two copies of the form.

--You cannot change the primary key bar.

--The table must have a primary key.

--You must redefine the table in the same outline.

--You cannot have a NOT NULL constraint on new columns until the redefine operation is complete.

--The table cannot contain long, bfile, and user types (UDTs).

--The linked list (clustered tables) cannot be redefined.

--The table cannot be redefined in SYS and the system outline.

--You cannot redefine a table with materialized view logs (materialized view logs), and you cannot redefine a table that contains materialized views.

--cannot perform a lateral diversity in the redefinition process (horizontal subsetting)

This article is from the "oracle+linux=>majesty" blog, make sure to keep this source http://majesty.blog.51cto.com/3493901/1627222

Oracle 11g online redefine normal table variable partition table