線上重定義的操作步驟__普通錶轉換為分割槽表的實驗案例
普通錶轉換為分割槽表的線上重定義的步驟
（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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 線上重定義與普通表改為分割槽表
- 線上重定義方式將普通表修改為分割槽表
- 非分割槽錶轉換成分割槽表
- 指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh指令碼
- LightDB 22.4 新特性之相容Oracle線上轉換普通表為分割槽表Oracle
- Oracle 12C新特性-線上把非分割槽錶轉為分割槽表Oracle
- PG的非分割槽表線上轉分割槽表
- 非分割槽錶轉換成分割槽表以及注意事項
- 【ASK_ORACLE】使用insert語句將普通錶轉換成分割槽表Oracle
- 壓縮錶轉非壓縮表（線上重定義）
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- MySQL線上轉分割槽表（以及TiDB）MySqlTiDB
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- oracle 普通表-分割槽表改造流程Oracle
- Spark操作Hive分割槽表SparkHive
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- Oracle切換undo表空間操作步驟Oracle
- win10無損分割槽的步驟_win10如何對硬碟無損分割槽Win10硬碟
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 在oracle 9i下線上重定義表Oracle
- 分割槽表-實戰
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- oracle將表配置為分割槽表Oracle
- oracle分割槽表和分割槽表exchangeOracle
- 第40期：MySQL 分割槽表案例分享MySql
- MySql資料分割槽操作之新增分割槽操作MySql
- Mysql表分割槽實操MySql
- Mysql表分割槽實現MySql
- MySQL高可用方案MHA線上切換的步驟及原理MySql
- 如何使用Fdisk來快速重建隨身碟分割槽？Fdisk快速重建隨身碟分割槽的方法步驟
- oracle分割槽表和非分割槽表exchangeOracle
- 物化檢視分割槽實驗
- MapReduce實現之Reduce端重分割槽Join操作最佳化！
- 檢視錶的定義
- 將網頁轉換為Markdown的免費線上轉換工具網頁
- PostgreSQL分割槽表、繼承表記錄去重方法SQL繼承