線上重定義的操作步驟__普通錶轉換為分割槽表的實驗案例
普通錶轉換為分割槽表的線上重定義的步驟
（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部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2156145/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 普通錶轉換分割槽表-線上重定義
- 線上重定義 ?普通錶轉換成分割槽表
- ORACLE 普通錶轉換成分割槽表（線上重定義）Oracle
- ORACLE 普通錶轉換成分割槽表（線上重定義）(zt)Oracle
- 利用線上重定義功能將普通錶轉換為分割槽表問題
- 線上表格重定義研究,比如將普通錶轉換為分割槽表等
- 普通錶轉換為分割槽表
- ORACLE普通錶轉換成分割槽表的操作Oracle
- 將普通錶轉換為分割槽表
- 使用線上重定義方法改造普通表為分割槽表實戰
- ORACLE 線上重新定義表分割槽表重定義為普通表。Oracle
- 使用ORACLE線上重定義將普通表改為分割槽表Oracle
- 【Oracle】利用線上重定義的方式改變普通表為分割槽表Oracle
- Oracle 將普通錶轉換為分割槽表Oracle
- 使用DBMS_REDEFINITION線上重定義表普通表為分割槽表
- 【分割槽】如何將一個普通錶轉換為分割槽表
- dbms_redefinition利用線上重定義把普通錶轉化為分割槽表的一些測試
- Oracle線上將普通錶轉分割槽表Oracle
- 普通錶轉換成分割槽表的四種方法
- Oracle Database將普通錶轉換為分割槽表遇到的問題OracleDatabase
- 將非分割槽錶轉換為分割槽表
- 批次轉換分割槽表為普通表
- 批量轉換分割槽表為普通表
- oracle線上重定義表步驟Oracle
- 將mysql非分割槽錶轉換為分割槽表MySql
- ORACLE將普通錶轉變為分割槽表方法Oracle
- 指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh指令碼
- 將一個非分割槽錶轉換為分割槽表
- 非分割槽錶轉換為分割槽表和partition indexIndex
- ogg環境線上重定義普通表到分割槽表實戰及生產操作注意事項-orastarAST
- 非分割槽錶轉換成分割槽表
- MySQL普通錶轉換成分割槽表的兩種方法舉例MySql
- 使用dbms_redefinition包線上把大資料量非分割槽錶轉換為分割槽表大資料
- 分割槽表和dbms_redefinition包線上重定義表
- 高手幫忙，超大普通錶轉分割槽表？
- 堆錶轉換成分割槽表
- 海量資料處理_表分割槽(線上重定義)
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle