在线重定义的操作步骤__普通表转换为分区表的实验案例
普通表转换为分区表的在线重定义的步骤
（1）确定表A是否可以在线重定义
表A有主键的情况下
DBMS_REDEFINITION.CAN_REDEF_TABLE('用户名','A表名')
或
表A没有主键的情况下
DBMS_REDEFINITION.CAN_REDEF_TABLE('用户名','A表名',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID)
（2）创建临时的分区表B
（3）开始重定义，此过程会锁表，此时select可以查询到B表有了数据
表A有主键的情况下
DBMS_REDEFINITION.start_redef_table('用户名','A表名','B表名')
或
表A没有主键的情况下
DBMS_REDEFINITION.start_redef_table('用户名','A表名','B表名',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID)
（4）将表A的依赖约束、触发器、索引等依赖对象同步到临时分区表B，此过程不会锁表，此时select可以查到B表有了这些约束、触发器、索引den依赖对象
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
（5）将表A的数据同步到临时分区表B，此过程不会锁表，此动作可以做多次，只是同步重定义过程中DML产生的数据到表B
DBMS_REDEFINITION.sync_interim_table('用户名','A表名','B表名')
在通过调用START_REDEF_TABLE启动重新定义过程之后，在调用FINISH_REDEF_TABLE之前，可能在原始表上执行了大量DML语句。如果您知道确实如此，建议您定期将临时表与原始表同步。这是通过调用SYNC_INTERIM_TABLE过程完成的。调用此过程可以减少FINISH_REDEF_TABLE完成重定义过程所需的时间。调用SYNC_INTERIM_TABLE的次数没有限制。
（6）结束重定义，此过程会锁表，结束后才将2个表进行了交换，在此之前的3、4、5步过程中两表维持原来定义没变（表A还是普通表，表B还是分区表）。重定义结束后基表A成了分区表，我们创建的临时分区表B成了普通表。此时我们可以删除我们创建的临时表B。
DBMS_REDEFINITION.finish_redef_table('用户名','A表名','B表名')
如果表A没有主键使用rowid，最后再执行ALTER TABLE 表A DROP UNUSED COLUMNS
在线重定义的限制
1、重新定义具有物化视图日志的表之后，任何依赖物化视图的后续刷新必须是完全刷新
2、索引组织表不能在线重新定义
3、具有细粒度访问控制(行级安全性)的表不能在线重新定义
4、已启用Flashback数据存档的表不能在线重新定义
5、SYS和SYSTEM中的表不能在线重新定义
6、临时表不能在线重新定义
7、分区的表如果有一个或多个嵌套表，则不能在线重新定义
表A有主键的案例
create table test as select * from dba_objects
update test set CREATED=sysdate-(365*8) where object_id between 0 and 10000;
update test set CREATED=sysdate-(365*7) where object_id between 10000 and 20000;
update test set CREATED=sysdate-(365*6) where object_id between 20000 and 30000;
update test set CREATED=sysdate-(365*5) where object_id between 30000 and 40000;
update test set CREATED=sysdate-(365*4) where object_id between 40000 and 50000;
update test set CREATED=sysdate-(365*3) where object_id between 50000 and 60000;
update test set CREATED=sysdate-(365*2) where object_id between 60000 and 70000;
update test set CREATED=sysdate-(365*1) where object_id between 70000 and 80000;
alter table test add constraint IND_OBJ primary key ( OBJECT_ID );
create index IND_CREATED on test(created)
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
NO
SQL> select count(*) from test where CREATED between sysdate-(365*9) and sysdate-(365*8)
2 union all
3 select count(*) from test where CREATED between sysdate-(365*8) and sysdate-(365*7)
4 union all
5 select count(*) from test where CREATED between sysdate-(365*7) and sysdate-(365*6)
6 union all
7 select count(*) from test where CREATED between sysdate-(365*6) and sysdate-(365*5)
8 union all
9 select count(*) from test where CREATED between sysdate-(365*5) and sysdate-(365*4)
10 union all
11 select count(*) from test where CREATED between sysdate-(365*4) and sysdate-(365*3)
12 union all
13 select count(*) from test where CREATED between sysdate-(365*3) and sysdate-(365*2)
14 union all
15 select count(*) from test where CREATED between sysdate-(365*2) and sysdate-(365*1)
16 union all
17 select count(*) from test where CREATED between sysdate-(365*1) and sysdate-(365*0);
COUNT(*)
----------
9829
9848
10000
10000
10000
9959
9536
6077
0
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_OBJ OBJECT_ID
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
CONSTRAINT_NAME C
------------------------------ -
IND_OBJ P
SQL> select to_char(max(created),'yyyy-mm-dd hh24:mi:ss') from test where created<sysdate-(365*8);
TO_CHAR(MAX(CREATED
-------------------
2010-06-14 23:50:24
创建临时的分区表T1
SQL> CREATE TABLE T1
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19 BYTE),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE),
GENERATED VARCHAR2(1 BYTE),
SECONDARY VARCHAR2(1 BYTE),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30 BYTE)
)
PARTITION BY RANGE(CREATED) INTERVAL (numtoyminterval(1,'YEAR'))
( PARTITION p01 VALUES LESS THAN (to_date('2010-06-14 23:50:25','yyyy-mm-dd hh24:mi:ss')));
确定表TEST是否可以在线重定义
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77001
会话2
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','test');
PL/SQL procedure successfully completed.
会话1不提交，CAN_REDEF_TABLE正常执行
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77000
开始重定义
会话2
SQL> begin
2 DBMS_REDEFINITION.start_redef_table('hr','test','t1');
3 end;
4 /
会话1不提交，start_redef_table会一直等待,可以通过v$session查询到堵塞事件是enq: TX - row lock contention。
会话1 commit;
会话2 PL/SQL procedure successfully completed.
将TEST的依赖对象同步到T1
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77002
会话2
DECLARE
2 num_errors PLS_INTEGER;
3 begin
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr','test','t1',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
5 end;
6 /
PL/SQL procedure successfully completed.
会话1不提交，COPY_TABLE_DEPENDENTS可以正常执行
将TEST的数据同步到T1
SQL> begin
2 dbms_redefinition.sync_interim_table('hr','test','t1');
3 end;
4 /
PL/SQL procedure successfully completed.
会话1不提交，sync_interim_table可以正常执行
结束重定义
SQL> begin
2 dbms_redefinition.finish_redef_table('hr','test','t1');
3 end;
4 /
会话1不提交，则上面的finish_redef_table会一直等待，但是v$session查不到堵塞，v$lock可以查到会话1和会话2都持有锁，但是都不请求对方
验证
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_OBJ OBJECT_ID
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
CONSTRAINT_NAME C
------------------------------ -
IND_OBJ P
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
YES
select * from user_tab_partitions
insert into test (created) values(sysdate+699)--因为建表B时使用了interver，所以表A也发现自动扩展了一个分区
表A没有主键的案例
drop table test purge
drop table t1 purge
create table test as select * from dba_objects;
update test set CREATED=sysdate-(365*8) where object_id between 0 and 10000;
update test set CREATED=sysdate-(365*7) where object_id between 10000 and 20000;
update test set CREATED=sysdate-(365*6) where object_id between 20000 and 30000;
update test set CREATED=sysdate-(365*5) where object_id between 30000 and 40000;
update test set CREATED=sysdate-(365*4) where object_id between 40000 and 50000;
update test set CREATED=sysdate-(365*3) where object_id between 50000 and 60000;
update test set CREATED=sysdate-(365*2) where object_id between 60000 and 70000;
update test set CREATED=sysdate-(365*1) where object_id between 70000 and 80000;
create index IND_CREATED on test(created)
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
NO
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
no rows selected
SQL> select to_char(max(created),'yyyy-mm-dd hh24:mi:ss') from test where created<sysdate-(365*8);
TO_CHAR(MAX(CREATED
-------------------
2010-06-14 23:50:24
创建临时的分区表T1
SQL> CREATE TABLE T1
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19 BYTE),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE),
GENERATED VARCHAR2(1 BYTE),
SECONDARY VARCHAR2(1 BYTE),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30 BYTE)
)
PARTITION BY RANGE(CREATED) INTERVAL (numtoyminterval(1,'YEAR'))
( PARTITION p01 VALUES LESS THAN (to_date('2010-06-14 23:50:25','yyyy-mm-dd hh24:mi:ss')));
确定表TEST是否可以在线重定义
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77001
会话2
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','test',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
会话1不提交，CAN_REDEF_TABLE正常执行
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77000
开始重定义
SQL> begin
2 DBMS_REDEFINITION.start_redef_table('hr','test','t1',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
3 end;
4 /
会话1不提交，start_redef_table会一直等待,可以通过v$session查询到堵塞事件是enq: TX - row lock contention。
会话1 commit;
会话2 PL/SQL procedure successfully completed.
将TEST的依赖对象同步到T1
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77002
会话2
DECLARE
2 num_errors PLS_INTEGER;
3 begin
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr','test','t1',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
5 end;
6 /
PL/SQL procedure successfully completed.
会话1不提交，COPY_TABLE_DEPENDENTS可以正常执行
将TEST的数据同步到T1
SQL> begin
2 dbms_redefinition.sync_interim_table('hr','test','t1');
3 end;
4 /
PL/SQL procedure successfully completed.
会话1不提交，sync_interim_table可以正常执行
结束重定义
SQL> begin
2 dbms_redefinition.finish_redef_table('hr','test','t1');
3 end;
4 /
会话1不提交，则上面的finish_redef_table会一直等待，但是v$session查不到堵塞，v$lock可以查到会话1和会话2都持有锁，但是都不请求对方
验证
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
no rows selected
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
YES
select * from user_tab_partitions
insert into test (created) values(sysdate+699)--因为建表B时使用了interver，所以表A也发现自动扩展了一个分区
select * from user_tab_cols where table_name='TEST'--发现有个隐藏字段
ALTER TABLE TEST DROP UNUSED COLUMNS--删除该隐藏字段
（1）确定表A是否可以在线重定义
表A有主键的情况下
DBMS_REDEFINITION.CAN_REDEF_TABLE('用户名','A表名')
或
表A没有主键的情况下
DBMS_REDEFINITION.CAN_REDEF_TABLE('用户名','A表名',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID)
（2）创建临时的分区表B
（3）开始重定义，此过程会锁表，此时select可以查询到B表有了数据
表A有主键的情况下
DBMS_REDEFINITION.start_redef_table('用户名','A表名','B表名')
或
表A没有主键的情况下
DBMS_REDEFINITION.start_redef_table('用户名','A表名','B表名',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID)
（4）将表A的依赖约束、触发器、索引等依赖对象同步到临时分区表B，此过程不会锁表，此时select可以查到B表有了这些约束、触发器、索引den依赖对象
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
（5）将表A的数据同步到临时分区表B，此过程不会锁表，此动作可以做多次，只是同步重定义过程中DML产生的数据到表B
DBMS_REDEFINITION.sync_interim_table('用户名','A表名','B表名')
在通过调用START_REDEF_TABLE启动重新定义过程之后，在调用FINISH_REDEF_TABLE之前，可能在原始表上执行了大量DML语句。如果您知道确实如此，建议您定期将临时表与原始表同步。这是通过调用SYNC_INTERIM_TABLE过程完成的。调用此过程可以减少FINISH_REDEF_TABLE完成重定义过程所需的时间。调用SYNC_INTERIM_TABLE的次数没有限制。
（6）结束重定义，此过程会锁表，结束后才将2个表进行了交换，在此之前的3、4、5步过程中两表维持原来定义没变（表A还是普通表，表B还是分区表）。重定义结束后基表A成了分区表，我们创建的临时分区表B成了普通表。此时我们可以删除我们创建的临时表B。
DBMS_REDEFINITION.finish_redef_table('用户名','A表名','B表名')
如果表A没有主键使用rowid，最后再执行ALTER TABLE 表A DROP UNUSED COLUMNS
在线重定义的限制
1、重新定义具有物化视图日志的表之后，任何依赖物化视图的后续刷新必须是完全刷新
2、索引组织表不能在线重新定义
3、具有细粒度访问控制(行级安全性)的表不能在线重新定义
4、已启用Flashback数据存档的表不能在线重新定义
5、SYS和SYSTEM中的表不能在线重新定义
6、临时表不能在线重新定义
7、分区的表如果有一个或多个嵌套表，则不能在线重新定义
表A有主键的案例
create table test as select * from dba_objects
update test set CREATED=sysdate-(365*8) where object_id between 0 and 10000;
update test set CREATED=sysdate-(365*7) where object_id between 10000 and 20000;
update test set CREATED=sysdate-(365*6) where object_id between 20000 and 30000;
update test set CREATED=sysdate-(365*5) where object_id between 30000 and 40000;
update test set CREATED=sysdate-(365*4) where object_id between 40000 and 50000;
update test set CREATED=sysdate-(365*3) where object_id between 50000 and 60000;
update test set CREATED=sysdate-(365*2) where object_id between 60000 and 70000;
update test set CREATED=sysdate-(365*1) where object_id between 70000 and 80000;
alter table test add constraint IND_OBJ primary key ( OBJECT_ID );
create index IND_CREATED on test(created)
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
NO
SQL> select count(*) from test where CREATED between sysdate-(365*9) and sysdate-(365*8)
2 union all
3 select count(*) from test where CREATED between sysdate-(365*8) and sysdate-(365*7)
4 union all
5 select count(*) from test where CREATED between sysdate-(365*7) and sysdate-(365*6)
6 union all
7 select count(*) from test where CREATED between sysdate-(365*6) and sysdate-(365*5)
8 union all
9 select count(*) from test where CREATED between sysdate-(365*5) and sysdate-(365*4)
10 union all
11 select count(*) from test where CREATED between sysdate-(365*4) and sysdate-(365*3)
12 union all
13 select count(*) from test where CREATED between sysdate-(365*3) and sysdate-(365*2)
14 union all
15 select count(*) from test where CREATED between sysdate-(365*2) and sysdate-(365*1)
16 union all
17 select count(*) from test where CREATED between sysdate-(365*1) and sysdate-(365*0);
COUNT(*)
----------
9829
9848
10000
10000
10000
9959
9536
6077
0
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_OBJ OBJECT_ID
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
CONSTRAINT_NAME C
------------------------------ -
IND_OBJ P
SQL> select to_char(max(created),'yyyy-mm-dd hh24:mi:ss') from test where created<sysdate-(365*8);
TO_CHAR(MAX(CREATED
-------------------
2010-06-14 23:50:24
创建临时的分区表T1
SQL> CREATE TABLE T1
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19 BYTE),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE),
GENERATED VARCHAR2(1 BYTE),
SECONDARY VARCHAR2(1 BYTE),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30 BYTE)
)
PARTITION BY RANGE(CREATED) INTERVAL (numtoyminterval(1,'YEAR'))
( PARTITION p01 VALUES LESS THAN (to_date('2010-06-14 23:50:25','yyyy-mm-dd hh24:mi:ss')));
确定表TEST是否可以在线重定义
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77001
会话2
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','test');
PL/SQL procedure successfully completed.
会话1不提交，CAN_REDEF_TABLE正常执行
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77000
开始重定义
会话2
SQL> begin
2 DBMS_REDEFINITION.start_redef_table('hr','test','t1');
3 end;
4 /
会话1不提交，start_redef_table会一直等待,可以通过v$session查询到堵塞事件是enq: TX - row lock contention。
会话1 commit;
会话2 PL/SQL procedure successfully completed.
将TEST的依赖对象同步到T1
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77002
会话2
DECLARE
2 num_errors PLS_INTEGER;
3 begin
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr','test','t1',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
5 end;
6 /
PL/SQL procedure successfully completed.
会话1不提交，COPY_TABLE_DEPENDENTS可以正常执行
将TEST的数据同步到T1
SQL> begin
2 dbms_redefinition.sync_interim_table('hr','test','t1');
3 end;
4 /
PL/SQL procedure successfully completed.
会话1不提交，sync_interim_table可以正常执行
结束重定义
SQL> begin
2 dbms_redefinition.finish_redef_table('hr','test','t1');
3 end;
4 /
会话1不提交，则上面的finish_redef_table会一直等待，但是v$session查不到堵塞，v$lock可以查到会话1和会话2都持有锁，但是都不请求对方
验证
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_OBJ OBJECT_ID
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
CONSTRAINT_NAME C
------------------------------ -
IND_OBJ P
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
YES
select * from user_tab_partitions
insert into test (created) values(sysdate+699)--因为建表B时使用了interver，所以表A也发现自动扩展了一个分区
表A没有主键的案例
drop table test purge
drop table t1 purge
create table test as select * from dba_objects;
update test set CREATED=sysdate-(365*8) where object_id between 0 and 10000;
update test set CREATED=sysdate-(365*7) where object_id between 10000 and 20000;
update test set CREATED=sysdate-(365*6) where object_id between 20000 and 30000;
update test set CREATED=sysdate-(365*5) where object_id between 30000 and 40000;
update test set CREATED=sysdate-(365*4) where object_id between 40000 and 50000;
update test set CREATED=sysdate-(365*3) where object_id between 50000 and 60000;
update test set CREATED=sysdate-(365*2) where object_id between 60000 and 70000;
update test set CREATED=sysdate-(365*1) where object_id between 70000 and 80000;
create index IND_CREATED on test(created)
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
NO
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
no rows selected
SQL> select to_char(max(created),'yyyy-mm-dd hh24:mi:ss') from test where created<sysdate-(365*8);
TO_CHAR(MAX(CREATED
-------------------
2010-06-14 23:50:24
创建临时的分区表T1
SQL> CREATE TABLE T1
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19 BYTE),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE),
GENERATED VARCHAR2(1 BYTE),
SECONDARY VARCHAR2(1 BYTE),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30 BYTE)
)
PARTITION BY RANGE(CREATED) INTERVAL (numtoyminterval(1,'YEAR'))
( PARTITION p01 VALUES LESS THAN (to_date('2010-06-14 23:50:25','yyyy-mm-dd hh24:mi:ss')));
确定表TEST是否可以在线重定义
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77001
会话2
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','test',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL procedure successfully completed.
会话1不提交，CAN_REDEF_TABLE正常执行
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77000
开始重定义
SQL> begin
2 DBMS_REDEFINITION.start_redef_table('hr','test','t1',options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
3 end;
4 /
会话1不提交，start_redef_table会一直等待,可以通过v$session查询到堵塞事件是enq: TX - row lock contention。
会话1 commit;
会话2 PL/SQL procedure successfully completed.
将TEST的依赖对象同步到T1
会话1，不提交
update test set object_id=object_id+1 where object_id in (select max(object_id) from test);
SQL> select max(object_id) from test;
MAX(OBJECT_ID)
--------------
77002
会话2
DECLARE
2 num_errors PLS_INTEGER;
3 begin
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr','test','t1',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
5 end;
6 /
PL/SQL procedure successfully completed.
会话1不提交，COPY_TABLE_DEPENDENTS可以正常执行
将TEST的数据同步到T1
SQL> begin
2 dbms_redefinition.sync_interim_table('hr','test','t1');
3 end;
4 /
PL/SQL procedure successfully completed.
会话1不提交，sync_interim_table可以正常执行
结束重定义
SQL> begin
2 dbms_redefinition.finish_redef_table('hr','test','t1');
3 end;
4 /
会话1不提交，则上面的finish_redef_table会一直等待，但是v$session查不到堵塞，v$lock可以查到会话1和会话2都持有锁，但是都不请求对方
验证
SQL> select index_name,column_name from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
IND_CREATED CREATED
SQL> select constraint_name,constraint_type from user_constraints where table_name='TEST';
no rows selected
SQL> select partitioned from user_tables where table_name='TEST';
PAR
---
YES
select * from user_tab_partitions
insert into test (created) values(sysdate+699)--因为建表B时使用了interver，所以表A也发现自动扩展了一个分区
select * from user_tab_cols where table_name='TEST'--发现有个隐藏字段
ALTER TABLE TEST DROP UNUSED COLUMNS--删除该隐藏字段
Choose the redefinition method: by key or by rowid
By key—Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULLconstraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.
By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. If COMPATIBLE is set to 10.2.0 or higher, the final phase of redefinition automatically sets this column unused. You can then use the ALTER TABLE ... DROP UNUSED COLUMNS statement to drop it.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2156145/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
公有云的这五大定律 看看谁能跑得更远？
请登录后发表评论
登录
全部评论
