Online redefinition of the Oracle System Package dbms_redefinition Implementation table

Oracle's own Dbms_redefinition package is very powerful and can be used to reduce high watermark, reclaim debris space, make online redefinition of tables (Add or Remove fields, change field types, normal table redefine table as partition table, Partition table redefinition table is normal table, etc.)

In addition, it has a very powerful function, the word "online" is vividly embodied, but it is not completely online, because the last time before the completion of the redefinition, will hold the table-level exclusive lock, but this lock-up times are controllable.

If you have ever been bothered by the fact that delete cannot lower the high watermark, or because the history table is too large to make maintenance difficult, I believe this article will have a lot of feelings for you, so I believe that great friends have used such as Exp/imp,expdp/impdp,shink space,move and so on to release fragments, But the scope of business impact is very large, compared to dbms_redefinition way there are too many advantages!

The following is an example of an on-line redefinition of an ingredient area table for an ordinary table, demonstrating that the history table is too large to cause maintenance difficulties:

Sql> List
1* CREATE TABLE Ori_tab tablespace users as SELECT * from AB
Sql> Select COUNT (*) from Ori_tab;

COUNT (*)
----------
100

Sql> sql> Ed
Wrote file Afiedt.buf

1 begin
2 dbms_stats.gather_table_stats (' T1 ', ' ori_tab ');
The end;
Sql>/

PL/SQL procedure successfully completed.
Sql> Select Blocks,empty_blocks from user_tables where table_name= ' ori_tab ';

BLOCKS Empty_blocks
---------- ------------
4 0 <<<< occupies 4 data blocks

Sql> Delete from Ori_tab where a > 50;

Rows deleted.

Sql> COMMIT;

Commit complete.

Sql> Select COUNT (Distinct dbms_rowid.rowid_block_number (ROWID)) from Ori_tab;

COUNT (Distinctdbms_rowid. Rowid_block_number (ROWID))
---------------------------------------------------
1 <<<<<<<<< actual data only occupies 1 blocks, indicating that the delete operation did not reclaim the high watermark

Sql> ALTER TABLE ORI_TAB add constraint ORITAB_A_PK primary key (a); ---Create a primary key for the table for online re-order preparation (redefine can have two ways rowid or primary key _not NULL)

Table altered.

+++1 Check if Ori_tab supports online redefinition
sql> exec dbms_redefinition.can_redef_table (uname = ' T1 ', tname = ' ori_tab ');

PL/SQL procedure successfully completed.

No error, indicating that the current table supports online redefinition

+++2 Creating a new table Int_tab
Sql> Ed
Wrote file Afiedt.buf

1 CREATE TABLE Int_tab
2 (A number
3, B varchar2 (2)
4)
5 PARTITION by RANGE (a)
6 (PARTITION p0 VALUES less THAN (10),
7 PARTITION p1 VALUES less THAN (20),
8 PARTITION p2 VALUES less THAN (30),
9 PARTITION P3 VALUES less THAN (40),
10* PARTITION P4 values less than (MaxValue))
Sql>/

Table created.

+++3 began to do
Sql> alter session force parallel DML parallel 4;

Session altered.

Sql> ALTER session Force parallel query parallel 4;

Session altered.

sql> exec dbms_redefinition.start_redef_table (uname = ' T1 ', orig_table = ' Ori_tab ', int_table = ' Int_tab ' , col_mapping = Null,options_flag = Dbms_redefinition. CONS_USE_PK);

PL/SQL procedure successfully completed.

Sql> SELECT COUNT (*) from Int_tab;

COUNT (*)
----------
<<<<<<< has seen data being copied

Sql> Select Index_name from user_indexes where table_name= ' int_tab ';

No rows selected <<<<<<<<<<<< index not yet created automatically

Sql> Change/int_tab/ori_tab
1* Select Index_name from user_indexes where table_name= ' Ori_tab '
Sql>/

Index_name
--------------------------------------------------------------------------------
Oritab_a_pk

+++4 Copy related objects (indexes, triggers, views, etc.)

Sql> INSERT into Ori_tab VALUES (Wuyi, ' NW ');

1 row created.

Sql> COMMIT;

Commit complete.

Sql> SELECT COUNT (*) from Ori_tab;

COUNT (*)
----------
Wuyi <<<< Source is 51 records

Sql> SELECT COUNT (*) from Int_tab;

COUNT (*)
----------
<<<< new table is still 50 records

Sql> Ed
Wrote file Afiedt.buf

1 DECLARE
2 num_errors Pls_integer;
3 BEGIN
4 dbms_redefinition. Copy_table_dependents (' T1 ',
5 ' Ori_tab ',
6 ' Int_tab ',
7 dbms_redefinition. Cons_orig_params,
8 TRUE,
9 TRUE,
Ten TRUE,
One TRUE,
(num_errors);
13* END;
Sql>/

PL/SQL procedure successfully completed.

Sql> SELECT COUNT (*) from Ori_tab;

COUNT (*)
----------
51

Sql> SELECT COUNT (*) from Int_tab;

COUNT (*)
----------
50

Sql> Select Index_name from user_indexes where table_name= ' int_tab ';

Index_name
--------------------------------------------------------------------------------
Tmp$$_oritab_a_pk0 <<<< Index has been automatically created

+++5 source table synchronizes with new table
sql> exec dbms_redefinition.sync_interim_table (' T1 ', ' ori_tab ', ' int_tab ');

PL/SQL procedure successfully completed.

Sql> SELECT COUNT (*) from Ori_tab;

COUNT (*)
----------
51

Sql> SELECT COUNT (*) from Int_tab;

COUNT (*)
----------
<<<<< Description If the source table is updated frequently, we can also use this method to make the source table and the new table as much data synchronization as possible


Note: The sync program determines the length of the Finsh in the following step because the online redefine method holds table-level exclusive locks on the table at the last step.

+++6 Complete Online redefinition
sql> exec dbms_redefinition.finish_redef_table (' T1 ', ' ori_tab ', ' int_tab ');

PL/SQL procedure successfully completed.

Another window queries the source table Ori_tab:
Sql>/

COUNT (*)
----------
51

Sql>/
/
/<<<<<<< holds table-level exclusive lock at this time

COUNT (*)
----------
51

Sql>
COUNT (*)
----------
51

Sql>
COUNT (*)
----------
51

Now that the online redefinition of the table is complete, the principle is to synchronize the old data through a temporary segment, eventually switching the old data segment to production. Therefore, this method is required for free space and requires additional space for data synchronization and storage.

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

This article is from my technical blog http://blog.csdn.net/robo23

Reprint please mark source text link, otherwise investigate legal liability!

Online redefinition of the Oracle System Package dbms_redefinition Implementation table