【实验】 对于在线重定义的步骤，这里不再具体说明，我们通过一个实验来演示一下，下面是一个把普通表转换成分区表在线重定义的例子 一、首先创建用户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> 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
三、使用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
---------- ---------- ---------- ----------
7369 史密斯 6000 20
7499 艾伦 1600 30
7521 病房 1250 30
7566 琼斯 2975 20
7654 马丁 1250 30
7698 布莱克 2850 30
7782 克拉克 2450 10
7788 斯科特 1000 20
7839 国王 5000 10
7844 特纳 1500 30
7876 亚当斯 1100 20
7900 詹姆斯 950 30
7902 福特 3000 20
7934 米勒 1300 10
已选择 14 行。
SQL>从 demo_tmp 选择*；
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 克拉克 2450 10
7839 国王 5000 10
7934 米勒 1300 10
7369 史密斯 6000 20
7566 琼斯 2975 20
7788 斯科特 1000 20
7876 亚当斯 1100 20
7902 福特 3000 20
7499 艾伦 1600 30
7521 病房 1250 30
7654 马丁 1250 30
7698 布莱克 2850 30
7844 TURNER 1500 30
7900 JAMES 950 30
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
----------
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
这里我们看到，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
---------- ---------- ---------- ----------
7369 SMITH 6000 20
7499 艾伦 1600 30
7521 病房 1250 30
7566 琼斯 2975 20
7654 马丁 1250 30
7698 布莱克 2850 30
7782 克拉克 2450 10
7788 斯科特 1000 20
7839 国王 5000 10
7844 特纳 1500 30
7876 亚当斯 1100 20
7900 詹姆斯 950 30
7902 福特 3000 20
7934 米勒 1300 10
1000 汤米 1350 10
已选择 15 行。
SQL>从 demo_tmp 选择*；
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 克拉克 2450 10
7839 国王 5000 10
7934 米勒 1300 10
7369 史密斯 6000 20
7566 琼斯 2975 20
7788 斯科特 1000 20
7876 亚当斯 1100 20
7902 福特 3000 20
7499 艾伦 1600 30
7521 病房 1250 30
7654 马丁 1250 30
7698 布莱克 2850 30
7844 特纳 1500 30
7900 JAMES 950 30
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
---------- ---------- ---------- ----------
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 国王 5000 10
7844 特纳 1500 30
7876 亚当斯 1100 20
7900 詹姆斯 950 30
7902 福特 3000 20
7934 米勒 1300 10
1000 汤米 1350 10
已选择 15 行。
SQL>从 demo_tmp 选择*；
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7782 克拉克 2450 10
7839 国王 5000 10
7934 米勒 1300 10
1000 汤米 1350 10
7369 史密斯 6000 20
7566 琼斯 2975 20
7788 斯科特 1000 20
7876 亚当斯 1100 20
7902 福特 3000 20
7499 艾伦 1600 30
7521 病房 1250 30
7654 马丁 1250 30
7698 布莱克 2850 30
7844 特纳 1500 30
7900 詹姆斯 950 30
已选择 15 行。
SQL>从 MLOG$_DEMO 中选择 empno,DMLTYPE$$,OLD_NEW$$;
未选择任何行
严格来说，第六步不是必须的，当做第七步的时候，Oracle会自动同步数据，不过这样会加长表不可用的时间，所以还是建议我们单独做
七、完成在线重定义，在这一步中，要对原始表DEMO以独占的方式锁定。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL 过程成功完成。
SQL> select object_id,object_name,object_type,status from user_objects;
OBJECT_ID OBJECT_NAME OBJECT_TYPE 状态
---------- --------------- ------------------- --- ----
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
------------------------------ ------------------------------
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 克拉克 2450 10
7839 国王 5000 10
7934 米勒 1300 10
1000 汤米 1350 10
