在
Oracle9i
出现之前,你只能通过MOVE
或导出和导入的方式来进行表的重定义,因此表重定义的过程可能相当漫长或者说是一个离线过程,在此期间应用程序对该表的操作将失败。除了这个,如果用exp
,我们也不能保证exp
的时候该表的数据没有改变(除非单用户),而imp
更是一个漫长的过程。
为了解决这个问题,Oracle9i
在其DBMS_REDEFINITION
软件包中引入了在线重定义功能。这个特性对24*7
的数据库系统来说非常重要,使用这个技术DBA
可以在保持表允许DML
语句的情况下修改结构,比如添加列、移动表到其他表空间、处理表的碎片等,当然了对于表的碎片处理,在10g
以后,可以考虑使用shrink
操作来实现,关于shrink
在这里不做讨论。
在线重定义具有以下功能:
(1)修改表的存储参数;
(2)可以将表转移到其他表空间;
(3)在表上增加、修改或删除一列或是多列;
(4)增加并行查询选项;
(5)增加分区支持;
(6)修改分区结构;
(7)重建表以减少碎片;
(8)将堆表改为索引组织表或相反的操作;
在线重定义的方法
1.基于主键 默认采用主键的方式。
2.基于ROWID
,ROWID
的方式不能用于索引组织表,而且重定义后会存在隐藏列M_ROW$$
。
在线重定义的一些限制
1.要求原始表和中间表在同一个方案下;
2.要求有2
倍甚至是多于2
倍的表空间空间;
3.如果使用主键重定义的方式,原始表上要有主键;
对于在线重定义的步骤,这里不再具体说明,我们通过一个实验来演示一下,下面是一个把普通表转换成分区表在线重定义的例子
一、首先创建用户xiha
,并授予能够完成在线重定义的权限和角色
SQL> create tablespace xiha datafile '/opt/oracle/oradata/orcl/xiha01.dbf' size 500m autoextend on next 500m;
Tablespace created.
SQL> alter tablespace xiha add datafile '/opt/oracle/oradata/orcl/xiha03.dbf' size 500m autoextend on next 500m;
Tablespace altered.
SQL> alter tablespace xiha add datafile '/opt/oracle/oradata/orcl/xiha02.dbf' size 500m autoextend on next 500m;
Tablespace altered.
SQL> create user xiha identified by xiha
2 default tablespace xiha
3 temporary tablespace temp
4 quota unlimited on xiha;
User created.
GRANT
CREATE SESSION,
CREATE ANY TABLE,
ALTER ANY TABLE,
DROP ANY TABLE,
LOCK ANY TABLE,
SELECT ANY TABLE,
CREATE ANY INDEX,
CREATE ANY TRIGGER
TO xiha;
SQL> GRANT EXECUTE_CATALOG_ROLE TO xiha;
Grant succeeded.
二、使用xiha
用户登录,使用datapump
从别的库中导入一个大表T_TEST_REC
,作为在线重定义的原始表,该表上有主键和索引
SQL> conn xiha/xiha
Connected.
下面的报错是因为expdp
的机器是中文环境的,impdp
的机器是英文环境这里可以忽略,不影响这次实验,下次使用datapump
的时候要注意语言环境,尽量都export LC_ALL=en_US.UTF-8
[oracle@oracle pump]$ impdp xiha/xiha directory=s01 dumpfile=T_TEST_REC_%U.oraexpdp logfile=T_TEST_REC_impdp.log parallel=6 remap_schema=cdbp:xiha remap_tablespace=cdbp:xiha,newcdbp:xiha,cdbp_index:xiha
Import: Release 11.2.0.4.0 - Production on Wed Apr 28 17:10:38 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XIHA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XIHA"."SYS_IMPORT_FULL_01": xiha/******** directory=s01 dumpfile=T_TEST_REC_%U.oraexpdp logfile=T_TEST_REC_impdp.log parallel=6 remap_schema=cdbp:xiha remap_tablespace=cdbp:xiha,newcdbp:xiha,cdbp_index:xiha
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "XIHA"."T_TEST_REC" 20.65 GB 40766912 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BAQXXZX' does not exist
Failing sql is:
GRANT SELECT ON "XIHA"."T_TEST_REC" TO "BAQXXZX"
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"XIHA"."TRI_T_TEST_REC" created with compilation warnings
ORA-39082: Object type TRIGGER:"XIHA"."TRI_T_TEST_REC" created with compilation warnings
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "XIHA"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Wed Apr 28 17:43:01 2021 elapsed 0 00:32:22
现在不是分区表
SQL> conn xiha/xiha
Connected.
SQL> select table_name,partitioned from user_tables where table_name = 'T_TEST_REC';
TABLE_NAME PARTITION
------------------------------------------------------------------------------------------ ---------
T_TEST_REC NO
--有四千多万条数据
SQL> select count(1) from T_TEST_REC;
COUNT(1)
----------
40766912
xiha
用户下有哪些对象
conn xiha/xiha
SQL> select object_id,object_name,object_type,status from user_objects order by object_type;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- --------------------------------------------------------- ---------------------
107219 T_TEST_REC_N4 INDEX VALID
107233 PK_TEST_REC INDEX VALID
107220 T_TEST_REC_N3 INDEX VALID
107221 T_TEST_REC_N1 INDEX VALID
107222 T_TEST_REC_N2 INDEX VALID
107223 TESTREC_MONGO INDEX VALID
107224 TR_DN_IDX9 INDEX VALID
107225 TR_DS_IDX102 INDEX VALID
107226 TR_DS_IDX101 INDEX VALID
107227 ORG_CODE_INDEX10 INDEX VALID
107228 CREATE_TIME_INDEX10 INDEX VALID
107229 TR_BN_IDX101 INDEX VALID
107230 TR_DS_IDX100 INDEX VALID
107231 TR_DN_IDX99 INDEX VALID
107232 INDEX_T_TEST_REC INDEX VALID
107218 T_TEST_REC TABLE VALID
107234 TRI_T_TEST_REC TRIGGER INVALID
17 rows selected.
三、使用CAN_REDEF_TABLE
确认表是否可以做在线重定义
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('XIHA','T_TEST_REC',dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed. --这样的数据就是可以做,要是不可以他会报错
注意该方法的第三个参数,使用主键还是
rowid
方法。本质上,Online Redefinition
是使用物化视图Materialized View
技术。过程定义记录就是主键和rowid
两种策略。通常而言,我们还是推荐数据表有一个明确主键,也就是使用cons_use_pk
。如果希望使用rowid
,就使用dbms_redefinition.cons_use_rowid
。
四、创建中间表,当然这是一个空表,然后使用START_REDEF_TABLE
开始在线重定义
create table T_TEST_REC_TMP
partition by list(gateway_name)
(
partition p1 values ('zyy'),
partition p2 values ('rmyy'),
partition p3 values ('syyy'),
partition p4 values ('sgyy') ,
partition p5 values ('fybj'),
partition p6 values ('fyyy'),
partition p7 values ('sjyy'),
partition p8 values ('zxyy'),
partition p9 values ('mbyy')
)
as
select * from T_TEST_REC where 1=2;
Table created.
SQL> select object_id,object_name,object_type,status from user_objects order by object_type;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- --------------------------------------------------------- ---------------------
107233 PK_TEST_REC INDEX VALID
107219 T_TEST_REC_N4 INDEX VALID
107220 T_TEST_REC_N3 INDEX VALID
107221 T_TEST_REC_N1 INDEX VALID
107222 T_TEST_REC_N2 INDEX VALID
107223 TESTREC_MONGO INDEX VALID
107224 TR_DN_IDX9 INDEX VALID
107225 TR_DS_IDX102 INDEX VALID
107226 TR_DS_IDX101 INDEX VALID
107227 ORG_CODE_INDEX10 INDEX VALID
107228 CREATE_TIME_INDEX10 INDEX VALID
107229 TR_BN_IDX101 INDEX VALID
107232 INDEX_T_TEST_REC INDEX VALID
107231 TR_DN_IDX99 INDEX VALID
107230 TR_DS_IDX100 INDEX VALID
107218 T_TEST_REC TABLE VALID
107235 T_TEST_REC_TMP TABLE VALID
107237 T_TEST_REC_TMP TABLE PARTITION VALID
107236 T_TEST_REC_TMP TABLE PARTITION VALID
107244 T_TEST_REC_TMP TABLE PARTITION VALID
107243 T_TEST_REC_TMP TABLE PARTITION VALID
107239 T_TEST_REC_TMP TABLE PARTITION VALID
107242 T_TEST_REC_TMP TABLE PARTITION VALID
107241 T_TEST_REC_TMP TABLE PARTITION VALID
107240 T_TEST_REC_TMP TABLE PARTITION VALID
107238 T_TEST_REC_TMP TABLE PARTITION VALID
107234 TRI_T_TEST_REC TRIGGER INVALID
27 rows selected.
停掉原始表和中间表上的触发器(如果有的话,没有更好,省心!)
SQL> conn xiha/xiha;
Connected.
SQL> alter table t_test_rec disable all triggers;
Table altered.
SQL> alter table t_test_rec_tmp disable all triggers;
Table altered.
表如果很大,下面的过程时间会很长
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP');
END;
/
PL/SQL procedure successfully completed.
SQL> select object_id,object_name,object_type,status from user_objects order by object_type,object_name;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- -------------------- --------------------------------------------------------- ---------------------
107228 CREATE_TIME_INDEX10 INDEX VALID
107232 INDEX_T_TEST_REC INDEX VALID
107247 I_MLOG$_T_TEST_REC INDEX VALID
107227 ORG_CODE_INDEX10 INDEX VALID
107233 PK_TEST_REC INDEX VALID
107223 TESTREC_MONGO INDEX VALID
107229 TR_BN_IDX101 INDEX VALID
107224 TR_DN_IDX9 INDEX VALID
107231 TR_DN_IDX99 INDEX VALID
107230 TR_DS_IDX100 INDEX VALID
107226 TR_DS_IDX101 INDEX VALID
107225 TR_DS_IDX102 INDEX VALID
107221 T_TEST_REC_N1 INDEX VALID
107222 T_TEST_REC_N2 INDEX VALID
107220 T_TEST_REC_N3 INDEX VALID
107219 T_TEST_REC_N4 INDEX VALID
107245 MLOG$_T_TEST_REC TABLE --多出来的 VALID
107246 RUPD$_T_TEST_REC TABLE --多出来的 VALID
107218 T_TEST_REC TABLE VALID
107235 T_TEST_REC_TMP TABLE VALID
107237 T_TEST_REC_TMP TABLE PARTITION VALID
107236 T_TEST_REC_TMP TABLE PARTITION VALID
107238 T_TEST_REC_TMP TABLE PARTITION VALID
107239 T_TEST_REC_TMP TABLE PARTITION VALID
107240 T_TEST_REC_TMP TABLE PARTITION VALID
107241 T_TEST_REC_TMP TABLE PARTITION VALID
107242 T_TEST_REC_TMP TABLE PARTITION VALID
107243 T_TEST_REC_TMP TABLE PARTITION VALID
107244 T_TEST_REC_TMP TABLE PARTITION VALID
107234 TRI_T_TEST_REC TRIGGER INVALID
30 rows selected.
我们注意到Oracle
新建了两张表MLOG$_T_TEST_REC
和RUPD$_T_TEST_REC
,其实Oracle
在线重定义是通过物化视图的LOG
来实现的。做完这一步后,在中间表中也有了相同的数据
五、使用COPY_TABLE_DEPENDENTS
把原始表的权限、约束、索引、物化视图LOG
在中间表上创建一份
现在中间表是没有index
信息的
SQL> select *
2 from dba_indexes where table_name = 'T_TEST_REC_TMP';
no rows selected
SQL> set serveroutput on
SQL> var v_err number
SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('XIHA','T_TEST_REC','T_TEST_REC_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 order by object_type,object_name;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ --------------------------------------------------------- ---------------------
107228 CREATE_TIME_INDEX10 INDEX VALID
107232 INDEX_T_TEST_REC INDEX VALID
107247 I_MLOG$_T_TEST_REC INDEX VALID
107227 ORG_CODE_INDEX10 INDEX VALID
107233 PK_TEST_REC INDEX VALID
107223 TESTREC_MONGO INDEX VALID
107355 TMP$$_CREATE_TIME_INDEX100 INDEX VALID
107359 TMP$$_INDEX_T_TEST_REC0 INDEX VALID
107354 TMP$$_ORG_CODE_INDEX100 INDEX VALID
107360 TMP$$_PK_TEST_REC0 INDEX VALID
107350 TMP$$_TESTREC_MONGO0 INDEX VALID
107356 TMP$$_TR_BN_IDX1010 INDEX VALID
107351 TMP$$_TR_DN_IDX90 INDEX VALID
107358 TMP$$_TR_DN_IDX990 INDEX VALID
107357 TMP$$_TR_DS_IDX1000 INDEX VALID
107353 TMP$$_TR_DS_IDX1010 INDEX VALID
107352 TMP$$_TR_DS_IDX1020 INDEX VALID
107348 TMP$$_T_TEST_REC_N10 INDEX VALID
107349 TMP$$_T_TEST_REC_N20 INDEX VALID
107347 TMP$$_T_TEST_REC_N30 INDEX VALID
107346 TMP$$_T_TEST_REC_N40 INDEX VALID
107229 TR_BN_IDX101 INDEX VALID
107224 TR_DN_IDX9 INDEX VALID
107231 TR_DN_IDX99 INDEX VALID
107230 TR_DS_IDX100 INDEX VALID
107226 TR_DS_IDX101 INDEX VALID
107225 TR_DS_IDX102 INDEX VALID
107221 T_TEST_REC_N1 INDEX VALID
107222 T_TEST_REC_N2 INDEX VALID
107220 T_TEST_REC_N3 INDEX VALID
107219 T_TEST_REC_N4 INDEX VALID
107245 MLOG$_T_TEST_REC TABLE VALID
107246 RUPD$_T_TEST_REC TABLE VALID
107218 T_TEST_REC TABLE VALID
107235 T_TEST_REC_TMP TABLE VALID
107243 T_TEST_REC_TMP TABLE PARTITION VALID
107242 T_TEST_REC_TMP TABLE PARTITION VALID
107241 T_TEST_REC_TMP TABLE PARTITION VALID
107240 T_TEST_REC_TMP TABLE PARTITION VALID
107238 T_TEST_REC_TMP TABLE PARTITION VALID
107237 T_TEST_REC_TMP TABLE PARTITION VALID
107236 T_TEST_REC_TMP TABLE PARTITION VALID
107244 T_TEST_REC_TMP TABLE PARTITION VALID
107239 T_TEST_REC_TMP TABLE PARTITION VALID
107361 TMP$$_TRI_T_TEST_REC0 TRIGGER INVALID
107234 TRI_T_TEST_REC TRIGGER INVALID
46 rows selected.
SQL> select table_name,index_name,status from user_indexes where table_name='T_TEST_REC_TMP' order by index_name;
TABLE_NAME INDEX_NAME STATUS
-------------------- ------------------------------ ------------------------
T_TEST_REC_TMP TMP$$_CREATE_TIME_INDEX100 VALID
T_TEST_REC_TMP TMP$$_INDEX_T_TEST_REC0 VALID
T_TEST_REC_TMP TMP$$_ORG_CODE_INDEX100 VALID
T_TEST_REC_TMP TMP$$_PK_TEST_REC0 VALID
T_TEST_REC_TMP TMP$$_TESTREC_MONGO0 VALID
T_TEST_REC_TMP TMP$$_TR_BN_IDX1010 VALID
T_TEST_REC_TMP TMP$$_TR_DN_IDX90 VALID
T_TEST_REC_TMP TMP$$_TR_DN_IDX990 VALID
T_TEST_REC_TMP TMP$$_TR_DS_IDX1000 VALID
T_TEST_REC_TMP TMP$$_TR_DS_IDX1010 VALID
T_TEST_REC_TMP TMP$$_TR_DS_IDX1020 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N10 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N20 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N30 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N40 VALID
15 rows selected.
这里我们看到,Oracle
在中间表T_TEST_REC_TMP
上又根据原始表T_TEST_REC
建了索引
六、如果在线重定义的时间比较长,而在这个过程中有其他的DML
语句操作在原始表上,Oracle
通过SYNC_INTERIM_TABLE
来做同步
SQL> insert into T_TEST_REC(id) values (11111);
1 row created.
SQL> insert into T_TEST_REC(id) values (22222);
1 row created.
SQL> insert into T_TEST_REC(id) values (33333);
1 row created.
SQL> insert into T_TEST_REC(id) values (44444);
1 row created.
SQL> insert into T_TEST_REC(id) values (55555);
1 row created.
SQL> commit;
Commit complete.
SQL> select id,rowid from T_TEST_REC where id in ('11111','22222','33333','44444','55555');
ID ROWID
---------- ------------------
11111 AAAaLSAAHAAEmUvAAA
22222 AAAaLSAAHAAEmUvAAB
33333 AAAaLSAAHAAEmUvAAC
44444 AAAaLSAAHAAEmUvAAD
55555 AAAaLSAAHAAEmUvAAE
SQL> select id,rowid from T_TEST_REC_TMP where id in ('11111','22222','33333','44444','55555');
no rows selected
上面插入五条记录到原始表T_TEST_REC
中,中间表上是看不到的,这个操作会被记录在MLOG$_T_TEST_REC
中,需要我们主动同步到T_TEST_REC_TMP
中
SQL> desc MLOG$_T_TEST_REC
Name Null? Type
----------------------- -------- ----------------
ID VARCHAR2(32)
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER
SQL> select id,DMLTYPE$$,OLD_NEW$$ from MLOG$_T_TEST_REC;
ID DML OLD
---------- --- ---
11111 I N
22222 I N
33333 I N
44444 I N
55555 I N
下面同步的时候报错了是因为我们是通过gateway_name
分的区,但是刚刚insert
插入的数据gateway_name
是空的,所以就无法同步了
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP');
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP');
BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP'); END;
*
ERROR at line 1:
ORA-42009: error occurred while synchronizing the
redefinition
ORA-12008: error in materialized view refresh
path
ORA-14400: inserted partition key does not map to
any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 123
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1605
ORA-06512: at line 1
删除刚刚的数据
SQL> delete from T_TEST_REC where id in ('11111','22222','33333','44444','55555');
5 rows deleted.
SQL> commit;
Commit complete.
同步一下(其实不需要)
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP');
PL/SQL procedure successfully completed.
重新插入
SQL> insert into T_TEST_REC(id,gateway_name) values ('11111','rmyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('22222','zxyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('33333','sgyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('44444','sjyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('55555','zyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('66666','fybj');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('77777','fyyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('88888','mbyy');
1 row created.
SQL> insert into T_TEST_REC(id,gateway_name) values ('99999','fyyy');
1 row created.
SQL> commit;
Commit complete.
这样就能同步成功了
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP');
PL/SQL procedure successfully completed.
SQL> select rowid,id,gateway_name from T_TEST_REC where id in ('11111','22222','33333','44444','55555');
ROWID ID GATEWAY_NAME
------------------ ---------- ------------------------------------------------------------
AAAaLSAAHAAEmUvAAF 11111 rmyy
AAAaLSAAHAAEmUvAAG 22222 zxyy
AAAaLSAAHAAEmUvAAH 33333 sgyy
AAAaLSAAHAAEmUvAAI 44444 sjyy
AAAaLSAAHAAEmUvAAJ 55555 zyy
SQL> select rowid,id,gateway_name from T_TEST_REC_TMP where id in ('11111','22222','33333','44444','55555');
ROWID ID GATEWAY_NAME
------------------ ---------- ------------------------------------------------------------
AAAaLlAAHAAMO3OAAA 11111 rmyy
AAAaLrAAHAAMOtQAAA 22222 zxyy
AAAaLnAAGAAMHTkAAA 33333 sgyy
AAAaLqAAGAAMGgpAAA 44444 sjyy
AAAaLkAAGAAMJX+AAA 55555 zyy
SQL> select id,DMLTYPE$$,OLD_NEW$$ from MLOG$_T_TEST_REC;
no rows selected
严格意义上来说,第六步不是必须的,当做第七步的时候,Oracle
会自动同步数据,不过这样会加长表不可用的时间,所以还是建议我们单独做
七、完成在线重定义,在这一步中,要对原始表T_TEST_REC
以独占的方式锁定。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('XIHA', 'T_TEST_REC', 'T_TEST_REC_TMP');
PL/SQL procedure successfully completed.
SQL> conn xiha/xiha;
Connected.
SQL> select object_id,object_name,object_type,status from user_objects order by object_type,object_name;
OBJECT_ID OBJECT_NAME OBJECT_TYPE STATUS
---------- ------------------------------ --------------------------------------------------------- ---------------------
107355 CREATE_TIME_INDEX10 INDEX VALID
107359 INDEX_T_TEST_REC INDEX VALID
107354 ORG_CODE_INDEX10 INDEX VALID
107360 PK_TEST_REC INDEX VALID
107350 TESTREC_MONGO INDEX VALID
107228 TMP$$_CREATE_TIME_INDEX100 INDEX VALID
107232 TMP$$_INDEX_T_TEST_REC0 INDEX VALID
107227 TMP$$_ORG_CODE_INDEX100 INDEX VALID
107233 TMP$$_PK_TEST_REC0 INDEX VALID
107223 TMP$$_TESTREC_MONGO0 INDEX VALID
107229 TMP$$_TR_BN_IDX1010 INDEX VALID
107224 TMP$$_TR_DN_IDX90 INDEX VALID
107231 TMP$$_TR_DN_IDX990 INDEX VALID
107230 TMP$$_TR_DS_IDX1000 INDEX VALID
107226 TMP$$_TR_DS_IDX1010 INDEX VALID
107225 TMP$$_TR_DS_IDX1020 INDEX VALID
107221 TMP$$_T_TEST_REC_N10 INDEX VALID
107222 TMP$$_T_TEST_REC_N20 INDEX VALID
107220 TMP$$_T_TEST_REC_N30 INDEX VALID
107219 TMP$$_T_TEST_REC_N40 INDEX VALID
107356 TR_BN_IDX101 INDEX VALID
107351 TR_DN_IDX9 INDEX VALID
107358 TR_DN_IDX99 INDEX VALID
107357 TR_DS_IDX100 INDEX VALID
107353 TR_DS_IDX101 INDEX VALID
107352 TR_DS_IDX102 INDEX VALID
107348 T_TEST_REC_N1 INDEX VALID
107349 T_TEST_REC_N2 INDEX VALID
107347 T_TEST_REC_N3 INDEX VALID
107346 T_TEST_REC_N4 INDEX VALID
107235 T_TEST_REC TABLE VALID
107218 T_TEST_REC_TMP TABLE VALID
107244 T_TEST_REC TABLE PARTITION VALID
107243 T_TEST_REC TABLE PARTITION VALID
107242 T_TEST_REC TABLE PARTITION VALID
107241 T_TEST_REC TABLE PARTITION VALID
107240 T_TEST_REC TABLE PARTITION VALID
107239 T_TEST_REC TABLE PARTITION VALID
107238 T_TEST_REC TABLE PARTITION VALID
107237 T_TEST_REC TABLE PARTITION VALID
107236 T_TEST_REC TABLE PARTITION VALID
107234 TMP$$_TRI_T_TEST_REC0 TRIGGER INVALID
107361 TRI_T_TEST_REC TRIGGER INVALID
43 rows selected.
操作完成后,我们发现RUPD$_T_TEST_REC
和MLOG$_T_TEST_REC
被自动删除,另外我们也可以看到重定义的效果了
SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T_TEST_REC';
TABLE_NAME PARTITION_NAME
-------------------- ------------------------------------------------------------------------------------------
T_TEST_REC P1
T_TEST_REC P2
T_TEST_REC P3
T_TEST_REC P4
T_TEST_REC P5
T_TEST_REC P6
T_TEST_REC P7
T_TEST_REC P8
T_TEST_REC P9
9 rows selected.
SQL> select table_name,index_name,status from user_indexes where table_name='T_TEST_REC' order by index_name;
TABLE_NAME INDEX_NAME STATUS
-------------------- ------------------------------ ------------------------
T_TEST_REC CREATE_TIME_INDEX10 VALID
T_TEST_REC INDEX_T_TEST_REC VALID
T_TEST_REC ORG_CODE_INDEX10 VALID
T_TEST_REC PK_TEST_REC VALID
T_TEST_REC TESTREC_MONGO VALID
T_TEST_REC TR_BN_IDX101 VALID
T_TEST_REC TR_DN_IDX9 VALID
T_TEST_REC TR_DN_IDX99 VALID
T_TEST_REC TR_DS_IDX100 VALID
T_TEST_REC TR_DS_IDX101 VALID
T_TEST_REC TR_DS_IDX102 VALID
T_TEST_REC T_TEST_REC_N1 VALID
T_TEST_REC T_TEST_REC_N2 VALID
T_TEST_REC T_TEST_REC_N3 VALID
T_TEST_REC T_TEST_REC_N4 VALID
15 rows selected.
SQL> select table_name,index_name,status from user_indexes where table_name='T_TEST_REC_TMP' order by index_name;
TABLE_NAME INDEX_NAME STATUS
-------------------- ------------------------------ ------------------------
T_TEST_REC_TMP TMP$$_CREATE_TIME_INDEX100 VALID
T_TEST_REC_TMP TMP$$_INDEX_T_TEST_REC0 VALID
T_TEST_REC_TMP TMP$$_ORG_CODE_INDEX100 VALID
T_TEST_REC_TMP TMP$$_PK_TEST_REC0 VALID
T_TEST_REC_TMP TMP$$_TESTREC_MONGO0 VALID
T_TEST_REC_TMP TMP$$_TR_BN_IDX1010 VALID
T_TEST_REC_TMP TMP$$_TR_DN_IDX90 VALID
T_TEST_REC_TMP TMP$$_TR_DN_IDX990 VALID
T_TEST_REC_TMP TMP$$_TR_DS_IDX1000 VALID
T_TEST_REC_TMP TMP$$_TR_DS_IDX1010 VALID
T_TEST_REC_TMP TMP$$_TR_DS_IDX1020 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N10 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N20 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N30 VALID
T_TEST_REC_TMP TMP$$_T_TEST_REC_N40 VALID
15 rows selected.
SQL> select count(1) from T_TEST_REC partition(p1);
COUNT(1)
----------
3987798
开启触发器
SQL> alter table T_TEST_REC enable all triggers;
Table altered.
删除中间表
SQL> drop table T_TEST_REC_TMP purge;
大致分为五个步骤:
1 判断数据表是否可以支持重定义,定义中间表
Interim
结构(停掉原始给表和中间表的触发器);
2 使用dbms_redefinition
的start_redef_table
方法开始重定义过程;
3 拷贝约束、重定义register
约束信息内容;
4 同步online
过程中的DML操作(Optional
,在之后例子演示);
5 结束过程finish_redef_table
操作;
6 开启原始表的触发器,删除中间表