Oracle 11g online reconstruction table

Oracle 11g online reconstruction table

Before Oracle9i appeared, you can only re-define the table by moving or exporting and importing the table. Therefore, the table redefinition process may be quite long or offline, during this period, the application will fail to operate on the table. Besides this, if exp is used, we cannot guarantee that the data in the table is not changed (unless it is a single user) during exp, and imp is a long process. To solve this problem, Oracle9i introduced the online redefinition function in its DBMS_REDEFINITION software package.

This feature is very important for 24*7 database systems. Using this technology, DBA can modify the structure while keeping the table allow DML statements, for example, you can add columns, move tables to other tablespaces, and Process Table fragments. Of course, you can use the shrink operation to Process Table fragments after 10 Gb, we will not discuss shrink here.

Online redefinition has the following features:

(1) Modify Table Storage parameters;
(2) tables can be transferred to other tablespaces;
(3) add, modify, or delete one or more columns in the table;
(4) added the parallel query option;
(5) add partition support;
(6) modify the partition structure;
(7) recreate the table to reduce fragments;
(8) change the heap table to an index organization table or the opposite operation;

Online redefinition Method

1. Based on the primary key
2. Based on ROWID. The ROWID method cannot be used to index the Organizational table, and the hidden column M_ROW $ will exist after being redefined.
The primary key is used by default.

Restrictions on online redefinition

1. The original and intermediate tables must be in the same solution;
2. Two or more times the tablespace space is required;
3. If the primary key is redefined, the original table must have a primary key;

Simulate duplicate partition tables of common tables and add fields.

-- Delete the previous test data table
-- Executed by DB users
SQL> DROP TABLE MXQ03;

Table dropped
SQL> DROP TABLE MXQ04;

Drop table MXQ04

Table dropped

-- Create simulated data
-- Create a common table
SQL> CREATE TABLE "DB". "MXQ03"
2 ("ID" NUMBER (10, 0 ),
3 "NAME" VARCHAR2 (20 ),
4 "NEW_DATE" DATE
5) TABLESPACE "SMSDB_DATA ";

Table created
SQL> create index name_dex on MXQ03 (name );

Index created
SQL> alter table mxq03 add constraint pk_id_03 primary key (id );

Table altered
SQL> INSERT INTO MXQ03 VALUES (1, 'A', SYSDATE );

1 row inserted
SQL> insert into MXQ03 VALUES (2, 'B', SYSDATE );

1 row inserted
SQL> INSERT INTO MXQ03 VALUES (3, 'C', SYSDATE );

1 row inserted
SQL> INSERT INTO MXQ03 VALUES (4, 'D', SYSDATE );

1 row inserted
SQL> INSERT INTO MXQ03 VALUES (5, 'E', SYSDATE );

1 row inserted
SQL> INSERT INTO MXQ03 VALUES (6, 'F', SYSDATE );

1 row inserted
SQL> COMMIT;

Commit complete

Create a partition temporary table
SQL> CREATE TABLE "DB". "MXQ04"
2 ("ID" NUMBER (10, 0 ),
3 "NAME" VARCHAR2 (20 ),
4 "old" varchar2 (20 ),
5 "NEW_DATE" DATE)
6 partition by range (ID)
7 (partition mxq_3 values less than (3 ),
8 partition mxq_6 values less thAn (10) TABLESPACE "SMSDB_DATA ";

Table created

-- Run
-- Judge whether the table supports Reconstruction
SQL> exec dbms_redefinition.can_redef_table ('db', 'mxq03 ');

PL/SQL procedure successfully completed

-- Start refactoring table
SQL> exec dbms_redefinition.start_redef_table ('db', 'mxq03', 'mxq04 ', 'Id ID, NAME, new_date ');

PL/SQL procedure successfully completed

-- Copy index, primary key, trigger .....
SQL> var v_log number;
SQL> exec dbms_redefinition.copy_table_dependents ('sdb', 'mxq03', 'mxq04 ', NUM_ERRORS =>: V_log );

PL/SQL procedure successfully completed
V_log
---------
0

-- Start Synchronization
SQL> exec dbms_redefinition.sync_interim_table ('db', 'mxq03', 'mxq04 ');

PL/SQL procedure successfully completed

-- Complete synchronization
SQL> exec dbms_redefinition.finish_redef_table ('db', 'mxq03', 'mxq04 ');

PL/SQL procedure successfully completed

-- The old field has been added to the source table structure.
SQL> desc db. mxq03;
Name Type Nullable Default Comments
-------------------------------------------
Id number (10) Y
NAME VARCHAR2 (20) Y
Old VARCHAR2 (20) Y
NEW_DATE DATE Y


-- Data query has been synchronized
SQL> select * from db. mxq03;

Id name old NEW_DATE
--------------------------------------------------------------
1 A 2015/5/28 1
2 B 2015/5/28 1
3 C 2015/5/28 1
4 D 2015/5/28 1
5 E 2015/5/28 1
6 F 2015/5/28 1

6 rows selected

SQL> select * from db. mxq03 partition (mxq_3 );

Id name old NEW_DATE
--------------------------------------------------------------
1 A 2015/5/28 1
2 B 2015/5/28 1

SQL> select * from db. mxq03 partition (mxq_6 );

Id name old NEW_DATE
--------------------------------------------------------------
3 C 2015/5/28 1
4 D 2015/5/28 1
5 E 2015/5/28 1
6 F 2015/5/28 1