【实验】 对于在线重定义的步骤，这里不再具体说明，我们通过一个实验来演示一下，下面是一个把普通表转换成分区表在线重定义的例子 一、首先创建用户tj，并授予能够完成在线重定义的权限和角色 SQL create user tj identified by tj 2 default tablespace u
【实验】
对于在线重定义的步骤，这里不再具体说明，我们通过一个实验来演示一下，下面是一个把普通表转换成分区表在线重定义的例子
一、首先创建用户tj，并授予能够完成在线重定义的权限和角色
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.
二、使用TJ用户登录，创建表DEMO，作为在线重定义的原始表，在表上添加主键和一个索引
SQL> conn tj/tj
Connected.
SQL> create table demo as select empno,ename,sal,deptno from scott.emp;
Table created.
SQL> select * from demo;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
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
三、使用CAN_REDEF_TABLE确认表是否可以做在线重定义
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('TJ', 'DEMO');
PL/SQL procedure successfully completed.
四、创建中间表，当然这是一个空表，使用START_REDEF_TABLE开始在线重定义
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 as
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.
我们注意到Oracle新建了两张表RUPD$_DEMO和MLOG$_DEMO，其实Oracle在线重定义是通过物化视图的LOG来实现的。做完这一步后，在中间表中也有了相同的数据
SQL> select * from demo;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
14 rows selected.
SQL> select * from demo_tmp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
14 rows selected.
五、使用COPY_TABLE_DEPENDENTS把原始表的权限、约束、索引、物化视图LOG在中间表上创建一份
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
----------
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
这里我们看到，Oracle在中间表DEMO_TMP上又根据原始表DEMO建了两个索引
六、如果在线重定义的时间比较长，而在这个过程中有其他的DML语句操作在原始表上，Oracle通过SYNC_INTERIM_TABLE来做同步
SQL> insert into demo values(1000,'TOMMY',1350,10);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from demo;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
15 rows selected.
SQL> select * from demo_tmp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
14 rows selected.
上面插入一条记录到原始表DEMO中，中间表上是看不到的，这个操作会被记录在MLOG$_DEMO中，需要我们主动同步到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
---------- ---------- ---------- ----------
15 rows selected.
SQL> select * from demo_tmp;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
15 rows selected.
SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
no rows selected
严格意义上来说，第六步不是必须的，当做第七步的时候，Oracle会自动同步数据，不过这样会加长表不可用的时间，所以还是建议我们单独做
七、完成在线重定义，在这一步中，要对原始表DEMO以独占的方式锁定。
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.
操作完成后，我们发现RUPD$_DEMO和MLOG$_DEMO被自动删除，另外我们也可以看到重定义的效果了
SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'DEMO';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
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
---------- ---------- ---------- ----------
1000 TOMMY 1350 10
查看更多关于oracle11g在线重定义（onlineredefinition）介绍的详细内容...