Introduction to oracle11g online redefinition (onlineredefinition)

[Experiment] Here we will not describe the online redefinition steps. We will use an experiment to demonstrate it, the following is an example of converting a common table to a partitioned table for online redefinition. 1. First, create user tj and grant the online redefinition permission and role SQLcreateusertjidentifiedbytj2defaulttablespaceu.

[Experiment] Here we will not describe the online redefinition steps. We will use an experiment to demonstrate it, the following is an example of converting a common table into a partitioned table for online redefinition. 1. First, create user tj, and grant the online redefinition permission and role SQL create user tj identified by tj 2 default tablespace u

[Experiment]

Here, we will not describe the steps for online redefinition. We will use an experiment to demonstrate how to convert a common table into a partitioned table online redefinition.

1. create user tj and grant permissions and roles that can be redefined online.

SQL> create user tj identified by tj

2 default tablespace users

3 temporary tablespace temp

4 quota unlimited on users;

User created.

SQL> GRANT CREATE SESSION, CREATE ANY TABLE, ALTER ANY TABLE,

2 drop any table, lock any table, select any table,

3 create any index, CREATE ANY TRIGGER

4 to tj;

Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO TJ;

Grant succeeded.

2. Log On with the TJ user and create a table DEMO. As the original table for online redefinition, add a primary key and an index to the table.

SQL> conn tj/tj

Connected.

SQL> create table demo as select empno, ename, sal, deptno from scott. emp;

Table created.

SQL> set linesize 120

SQL> set pagesize 60

SQL> select * from demo;

EMPNO ENAME SAL DEPTNO

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

7369 SMITH 6000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7566 JONES 2975 20

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7782 CLARK 2450 10

7788 SCOTT 1000 20

7839 KING 5000 10

7844 TURNER 1500 30

7876 ADAMS 1100 20

7900 JAMES 950 30

7902 FORD 3000 20

7934 MILLER 1300 10

14 rows selected.

SQL> alter table demo add constraint demo_pk primary key (empno );

Table altered.

SQL> create index demo_idx on demo (ename );

Index created.

SQL> select object_id, object_name, object_type, status from user_objects;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS

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

77125 DEMO TABLE VALID

77126 DEMO_PK INDEX VALID

77127 DEMO_IDX INDEX VALID

3. Use CAN_REDEF_TABLE to confirm whether the table can be redefined online

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('tj ', 'Demo ');

PL/SQL procedure successfully completed.

4. Create an intermediate table. Of course, this is an empty table. Use START_REDEF_TABLE to start online redefinition.

SQL> create table demo_tmp

2 partition by range (deptno)

3 (

4 partition p1 values less than (11 ),

5 partition p2 values less than (21 ),

6 partition p3 values less than (31)

7)

8

9 select * from demo where 1 = 2;

Table created.

SQL> select object_id, object_name, object_type, status from user_objects;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS

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

77129 DEMO_TMP TABLE VALID

77130 DEMO_TMP TABLE PARTITION VALID

77132 DEMO_TMP TABLE PARTITION VALID

77131 DEMO_TMP TABLE PARTITION VALID

77127 DEMO_IDX INDEX VALID

77126 DEMO_PK INDEX VALID

77125 DEMO TABLE VALID

7 rows selected.

SQL> BEGIN

2 DBMS_REDEFINITION.START_REDEF_TABLE ('tj ', 'Demo', 'demo _ TMP ');

3 END;

4/

PL/SQL procedure successfully completed.

SQL> select object_id, object_name, object_type, status from user_objects;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS

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

77134 RUPD $ _ DEMO TABLE VALID

77133 MLOG $ _ DEMO TABLE VALID

77129 DEMO_TMP TABLE VALID

77130 DEMO_TMP TABLE PARTITION VALID

77132 DEMO_TMP TABLE PARTITION VALID

77131 DEMO_TMP TABLE PARTITION VALID

77127 DEMO_IDX INDEX VALID

77126 DEMO_PK INDEX VALID

77125 DEMO TABLE VALID

9 rows selected.

We have noticed that Oracle has created two new tables, RUPD $ _ DEMO and MLOG $ _ DEMO. In fact, Oracle online redefinition is implemented through the materialized view LOG. After completing this step, the same data exists in the intermediate table.

SQL> select * from demo;

EMPNO ENAME SAL DEPTNO

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

7369 SMITH 6000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7566 JONES 2975 20

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7782 CLARK 2450 10

7788 SCOTT 1000 20

7839 KING 5000 10

7844 TURNER 1500 30

7876 ADAMS 1100 20

7900 JAMES 950 30

7902 FORD 3000 20

7934 MILLER 1300 10

14 rows selected.

SQL> select * from demo_tmp;

EMPNO ENAME SAL DEPTNO

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

7782 CLARK 2450 10

7839 KING 5000 10

7934 MILLER 1300 10

7369 SMITH 6000 20

7566 JONES 2975 20

7788 SCOTT 1000 20

7876 ADAMS 1100 20

7902 FORD 3000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7844 TURNER 1500 30

7900 JAMES 950 30

14 rows selected.

5. Use COPY_TABLE_DEPENDENTS to create a copy of the permissions, constraints, indexes, and materialized view logs of the original table in the intermediate table.

SQL> set serveroutput on

SQL> var v_err number

SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('tj ', 'Demo', 'demo _ TMP', NUM_ERRORS =>: V_ERR );

PL/SQL procedure successfully completed.

SQL> print v_err

V_ERR

----------

0

SQL> select object_id, object_name, object_type, status from user_objects;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS

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

77137 TMP $ _ DEMO_PK0 INDEX VALID

77138 TMP $ _ DEMO_IDX0 INDEX VALID

77134 RUPD $ _ DEMO TABLE VALID

77133 MLOG $ _ DEMO TABLE VALID

77129 DEMO_TMP TABLE VALID

77130 DEMO_TMP TABLE PARTITION VALID

77132 DEMO_TMP TABLE PARTITION VALID

77131 DEMO_TMP TABLE PARTITION VALID

77127 DEMO_IDX INDEX VALID

77126 DEMO_PK INDEX VALID

77125 DEMO TABLE VALID

11 rows selected.

SQL> select table_name, index_name, status from user_indexes where table_name = 'demo _ TMP ';

TABLE_NAME INDEX_NAME STATUS

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

DEMO_TMP TMP $ _ DEMO_IDX0 VALID

DEMO_TMP TMP $ _ DEMO_PK0 VALID

Here we can see that Oracle has created two indexes in the intermediate table DEMO_TMP based on the original table DEMO.

6. If the online redefinition takes a long time, and other DML statements are operated on the original table in this process, Oracle uses SYNC_INTERIM_TABLE for synchronization.

SQL> insert into demo values (1000, 'Tommy ', 10 );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo;

EMPNO ENAME SAL DEPTNO

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

7369 SMITH 6000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7566 JONES 2975 20

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7782 CLARK 2450 10

7788 SCOTT 1000 20

7839 KING 5000 10

7844 TURNER 1500 30

7876 ADAMS 1100 20

7900 JAMES 950 30

7902 FORD 3000 20

7934 MILLER 1300 10

1000 TOMMY 1350 10

15 rows selected.

SQL> select * from demo_tmp;

EMPNO ENAME SAL DEPTNO

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

7782 CLARK 2450 10

7839 KING 5000 10

7934 MILLER 1300 10

7369 SMITH 6000 20

7566 JONES 2975 20

7788 SCOTT 1000 20

7876 ADAMS 1100 20

7902 FORD 3000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7844 TURNER 1500 30

7900 JAMES 950 30

14 rows selected.

Insert a record to the DEMO of the original table, which is invisible to the intermediate table. This operation is recorded in MLOG $ _ DEMO and needs to be actively synchronized to DEMO_TMP.

SQL> desc MLOG $ _ DEMO

Name Null? Type

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

Empno number (4)

DMLTYPE $ VARCHAR2 (1)

OLD_NEW $ VARCHAR2 (1)

CHANGE_VECTOR $ RAW (255)

XID $ NUMBER

SQL> select empno, DMLTYPE $, OLD_NEW $ from MLOG $ _ DEMO;

EMPNO D O

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

1000 I N

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('tj ', 'Demo', 'demo _ TMP ');

PL/SQL procedure successfully completed.

SQL> select * from demo;

EMPNO ENAME SAL DEPTNO

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

7369 SMITH 6000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7566 JONES 2975 20

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7782 CLARK 2450 10

7788 SCOTT 1000 20

7839 KING 5000 10

7844 TURNER 1500 30

7876 ADAMS 1100 20

7900 JAMES 950 30

7902 FORD 3000 20

7934 MILLER 1300 10

1000 TOMMY 1350 10

15 rows selected.

SQL> select * from demo_tmp;

EMPNO ENAME SAL DEPTNO

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

7782 CLARK 2450 10

7839 KING 5000 10

7934 MILLER 1300 10

1000 TOMMY 1350 10

7369 SMITH 6000 20

7566 JONES 2975 20

7788 SCOTT 1000 20

7876 ADAMS 1100 20

7902 FORD 3000 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7844 TURNER 1500 30

7900 JAMES 950 30

15 rows selected.

SQL> select empno, DMLTYPE $, OLD_NEW $ from MLOG $ _ DEMO;

No rows selected

Strictly speaking, step 6 is not necessary. When Step 7 is used, Oracle will automatically synchronize data, but this will prolong the unavailable time of the table. Therefore, we recommend that you do this separately.

7. Complete online redefinition. In this step, the original table DEMO should be locked exclusively.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('tj ', 'Demo', 'demo _ TMP ');

PL/SQL procedure successfully completed.

SQL> select object_id, object_name, object_type, status from user_objects;

OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS

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

77129 DEMO TABLE VALID

77130 DEMO TABLE PARTITION VALID

77131 DEMO TABLE PARTITION VALID

77132 DEMO TABLE PARTITION VALID

77125 DEMO_TMP TABLE VALID

77138 DEMO_IDX INDEX VALID

77127 TMP $ _ DEMO_IDX0 INDEX VALID

77137 DEMO_PK INDEX VALID

77126 TMP $ _ DEMO_PK0 INDEX VALID

9 rows selected.

After the operation, we found that RUPD $ _ DEMO and MLOG $ _ DEMO were automatically deleted, and we can also see the effect of redefinition.

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'Demo ';

TABLE_NAME PARTITION_NAME

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

DEMO P1

DEMO P2

DEMO P3

SQL> select table_name, index_name, status from user_indexes where table_name = 'Demo ';

TABLE_NAME INDEX_NAME STATUS

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

DEMO DEMO_IDX VALID

DEMO DEMO_PK VALID

SQL> select table_name, index_name, status from user_indexes where table_name = 'demo _ TMP ';

TABLE_NAME INDEX_NAME STATUS

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

DEMO_TMP TMP $ _ DEMO_IDX0 VALID

DEMO_TMP TMP $ _ DEMO_PK0 VALID

SQL> select * from demo partition (p1 );

EMPNO ENAME SAL DEPTNO

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

7782 CLARK 2450 10

7839 KING 5000 10

7934 MILLER 1300 10

1000 TOMMY 1350 10