Shulou(Shulou.com)06/01 Report--

The first method

Redefine using the primary key id

-- create user test identified by 1 account unlock

-- grant resource

-- grant create any table, alter any table, drop any table, lock any table, select any table to test

To create a test table, use online redefinition to convert the table to a partitioned table, with created as the partition key and object_id as the primary key

Drop table test01 purge

Create table test01 as select * from dba_objects where object_id is not null

Alter table test01 add primary key (object_id)

Select * from test01

-- create partition tables

Create table test01_new

Partition by range (created)

-- INTERVAL (NUMTOYMINTERVAL (1)

Interval (numtodsinterval (1)

Store in (users)

(

Partition p0 values less than (to_date ('2008-01-01-01-01))

)

As select * from test01 where created is not null and 1

Alter table test01_new add primary key (object_id)

Determine whether the target table can be redefined online using the primary key or using 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. (this operation is required only if you want to update the table TEST01. )

Exec dbms_redefinition.sync_interim_table ('TEST',' TEST01', 'TEST01_NEW')

-- end the redefinition table

Exec dbms_redefinition.finish_redef_table ('TEST',' TEST01', 'TEST01_NEW')

-- unlock if redefinition fails

Exec dbms_redefinition.abort_redef_table ('TEST',' TEST01', 'TEST01_NEW')

-- advantages:

To ensure the consistency of the data, Table T can operate DML normally most of the time.

Lock the table only at the moment of switching, with high availability. This method has strong flexibility and can meet a variety of different needs.

-- moreover, the corresponding authorization and various constraints can be established before the switch, so that no additional management operations are needed after the switch is completed.

--

Deficiency: the implementation is slightly more complex than the above two, and it is suitable for all kinds 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 can't change the main key bar.

The table must have a primary key.

The table must be redefined in the same outline.

You cannot NOT NULL a new column until the redefinition operation is complete.

-- A table cannot contain LONG, BFILE, and user type (UDT).

-you cannot redefine linked lists (clustered tables).

-- tables cannot be redefined in SYS and SYSTEM outlines.

You cannot redefine a table with a materialized view log (materialized VIEW logs); you cannot redefine a table that contains a materialized view.

-- horizontal diversity (horizontal subsetting) cannot be performed during redefinition

The second method

Use rowid for redefinition, typically without a primary key

-- others as above

Determine whether the target table can be redefined online using the primary key or using rowid

Exec dbms_redefinition.can_redef_table ('TEST',' TEST01', dbms_redefinition.cons_use_rowid)

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',null,2)

Synchronize the intermediate table with the original table. (this operation is required only if you want to update the table TEST01. )

Exec dbms_redefinition.sync_interim_table ('TEST',' TEST01', 'TEST01_NEW')

-- end the redefinition table

Exec dbms_redefinition.finish_redef_table ('TEST',' TEST01', 'TEST01_NEW')

-- unlock if redefinition fails

Exec dbms_redefinition.abort_redef_table ('TEST',' TEST01', 'TEST01_NEW')

The third method

-- renaming table

Alter table test01 rename to test01_bak

Alter table test01_bak rename to test01

-- check whether the missing data is missing and make up the data

Select * from test01 T1

Where up_time is not null-the index key cannot have empty data, otherwise the insert fails

And not exists (select 1 from test01 T2 where t1.id = t2.id)

Insert into test01

Select * from test01_bak T1

Where up_time is not null-the index key cannot have empty data, otherwise the insert fails

And not exists (select 1 from test01 T2 where t1.id = t2.id)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.