oracle 表線上重定義
在生產環境下，有時侯我們需要對錶進行表空間的遷移，或碎片整理等，我們可以透過線上重定義來完成。
---在定重定義包
Summary of DBMS_REDEFINITION Subprograms:
=========================================
CAN_REDEF_TABLE Procedure:
--------------------------
This procedure determines if a given table can be reorganized online. This is
the first step of the online reorganization process. If the table is not a
candidate for online redefinition, an error message is raised.
SYNTAX
DBMS_REDEFINITION.can_redef_table (
uname IN VARCHAR2,
tname IN VARCHAR2);
CAN_REDEF_TABLE Procedure Parameters:
Parameter Description
--------- ------------
uname The schema name of the table.
tname The name of the table to be reorganized.
START_REDEF_TABLE Procedure:
----------------------------
This procedure initiates the reorganization process. After verifying that the
table can be reorganized online, you create an empty interim table (in the same
schema as the table to be reorganized) with the desired attributes of the
post-reorganization table.
SYNTAX
DBMS_REDEFINITION.start_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
col_mapping IN VARCHAR2 := NULL);
START_REDEF_TABLE Procedure Parameters:
Parameter Description
---------- ------------
uname The schema name of the tables.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
col_mapping The mapping information from the columns in the interim
table to the columns in the original table. (This is similar
to the column list on the SELECT clause of a query.) If NULL,
all the columns in the original table are selected and have
the same name after reorganization.
FINISH_REDEF_TABLE Procedure:
----------------------------
This procedure completes the reorganization process. Before this step you can
create new indexes, triggers, grants, and constraints on the interim table. The
referential constraints involving the interim table must be disabled. After
completing this step, the original table is locked briefly during this
procedure.
SYNTAX
DBMS_REDEFINITION.finish_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);
FINISH_REDEF_TABLE Procedure Parameters:
Parameter Description
--------- ------------
uname The schema name of the tables.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
SYNC_INTERIM_TABLE Procedure:
----------------------------
This procedure keeps the interim table synchronized with the original table.
This step is useful in minimizing the amount of synchronization needed to be
done by finish_reorg_table before completing the online reorganization. This
procedure can be called between long running operations (such as create index)
on the interim table to sync it up with the data in the original table and
speed up subsequent operations.
SYNTAX
DBMS_REDEFINITION.sync_interim_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);
SYNC_INTERIM_TABLE Procedure Parameters:
Parameters Description
---------- ------------
uname The schema name of the tables.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
ABORT_REDEF_TABLE Procedure:
----------------------------
This procedure cleans up errors that occur during the reorganization process.
This procedure can also be used to abort the reorganization process any time
after start_reorg_table has been called and before finish_reorg_table is called.
SYNTAX
DBMS_REDEFINITION.abort_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);
ABORT_REDEF_TABLE Procedure Parameters:
Parameters Description
---------- ------------
uname The schema name of the table.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
If we have an encrypted column which is part of primary key then
follow the steps mentioned in the bug below.
Reference: Bug 6034260 DBMS_REDEFINITION.START_REDEF_TABLE FAILS ORA-32412 BECAUSE
OF ENCRYPTED COLUMN
COPY_TABLE_DEPENDENTS (Procedure)
Copies the dependent objects of the original table to the interim table
COPY_TABLE_DEPENDENTS.dbms_redefinition.copy_table_dependents(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
copy_indexes IN PLS_INTEGER := 1,
copy_triggers IN BOOLEAN := TRUE,
copy_constraints IN BOOLEAN := TRUE,
copy_privileges IN BOOLEAN := TRUE,
ignore_errors IN BOOLEAN := FALSE,
num_errors OUT PLS_INTEGER,
copy_statistics IN BOOLEAN := FALSE
copy_mvlog IN BOOLEAN := FALSE);
RELATED DOCUMENTS
-----------------
select dbms_metadata.get_ddl('TABLE','BILL_ITEM','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."BILL_ITEM_TEMP"
( "BILL_ITEM_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
"CLASSIFY" VARCHAR2(3) NOT NULL ENABLE,
"BILL_PARENT_ID" NUMBER(9,0),
"REMARK_ID" NUMBER(9,0),
"PRINT_ORDER" NUMBER(5,0),
"BILL_ITEM_NAME" VARCHAR2(50) NOT NULL ENABLE,
"ORG_ID" NUMBER(9,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP)
TABLESPACE "ACCT_DATA02"
---------測試
1 檢查是否支援線上重定義
表 ACCT.PAYMENT_HIS
set serveroutput on
set timing on time on
set feedback on
set lines 200 pages 999
alter system set recyclebin=ON;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('ACCT','BILL_ITEM',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2 建立中間表
select dbms_metadata.get_ddl('TABLE','PAYMENT_HIS','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."PAYMENT_HIS_TEMP"
( "PAYMENT_ID" NUMBER(12,0) NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"PAYMENT_METHOD" NUMBER(9,0) NOT NULL ENABLE,
"PAYED_METHOD" NUMBER(9,0),
"OPERATION_TYPE" VARCHAR2(3) NOT NULL ENABLE,
"OPERATED_PAYMENT_SERIAL_NBR" NUMBER(12,0),
"AMOUNT" NUMBER(16,5) NOT NULL ENABLE,
"PAYMENT_DATE" DATE NOT NULL ENABLE,
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ACC_NBR" VARCHAR2(32),
"PAY_CYCLE_ID" NUMBER(12,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "COMM_TBS";
select dbms_metadata.get_ddl('TABLE','BILL_HIS','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."BILL_HIS_TEMP"
( "BILL_ID" NUMBER(12,0) NOT NULL ENABLE,
"OPERATED_BILL_ID" NUMBER(12,0),
"PAYMENT_ID" NUMBER(12,0),
"PAYMENT_METHOD" NUMBER(9,0),
"BILLING_CYCLE_ID" NUMBER(9,0) NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ACC_NBR" VARCHAR2(32),
"BILL_AMOUNT" NUMBER(16,0) NOT NULL ENABLE,
"LATE_FEE" NUMBER(16,0) NOT NULL ENABLE,
"DERATE_LATE_FEE" NUMBER(16,0) NOT NULL ENABLE,
"BALANCE" NUMBER(16,0) NOT NULL ENABLE,
"DEPOSIT_AMOUNT" NUMBER(16,0) NOT NULL ENABLE,
"LAST_CHANGE" NUMBER(12,0) NOT NULL ENABLE,
"CUR_CHANGE" NUMBER(12,0) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"PAYMENT_DATE" DATE NOT NULL ENABLE,
"USE_DERATE_BLANCE" NUMBER(16,0) NOT NULL ENABLE,
"INVOICE_ID" NUMBER(12,0),
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "COMM_TBS";
select dbms_metadata.get_ddl('TABLE','INVOICE_HIS','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."INVOICE_HIS_TEMP"
( "INVOICE_ID" NUMBER(12,0) NOT NULL ENABLE,
"PRINT_SEQ" NUMBER(8,0) NOT NULL ENABLE,
"PAYMENT_ID" NUMBER(12,0) NOT NULL ENABLE,
"BILLING_CYCLE_ID" NUMBER(9,0),
"REQUIRE_ID" NUMBER(9,0) NOT NULL ENABLE,
"NAME" VARCHAR2(50),
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"BILL_ID" NUMBER(12,0),
"ACC_NBR" VARCHAR2(32) NOT NULL ENABLE,
"AMOUNT" NUMBER(16,5) NOT NULL ENABLE,
"COUNT" NUMBER(8,0) NOT NULL ENABLE,
"PRINT_FLAG" VARCHAR2(3) NOT NULL ENABLE,
"INVOICE_TYPE" VARCHAR2(3) NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0),
"SERV_ID" NUMBER(12,0),
"INVOICE_FLAG" NUMBER(2,0),
"PRINT_DATE" DATE NOT NULL ENABLE,
"RECEIPT_CLASS" NUMBER(3,0),
"INVOICE_NUM" VARCHAR2(20),
"PAYED_METHOD" NUMBER(9,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "COMM_TBS";
3 開始重定義
--為了加快重定義的速度，考慮開啟並行
alter session force parallel dml parallel 6;
alter session force parallel query parallel 6;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ACCT', 'BILL_ITEM','BILL_ITEM_TEMP',NULL,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ACCT', 'BILL_HIS','BILL_HIS_TEMP',NULL,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ACCT', 'INVOICE_HIS','INVOICE_HIS_TEMP',NULL,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
select owner,segment_name,partition_name,segment_type,tablespace_name,blocks,bytes/1024/1024 from dba_segments where owner='ACCT' and segment_name='PAYMENT_HIS_TEMP';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS BYTES/1024/1024
---------- -------------------- --------------- ------------------ ------------------------------ ---------- ---------------
ACCT PAYMENT_HIS_TEMP TABLE COMM_TBS 60704 474.25
4 檢查上一步同步所依賴物件是否有錯
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
5.檢視原表的相關資訊
col owner for a10
col constraint_name for a30
col column_name for a30
col tablespace_name for a20
select constraint_name,column_name from dba_cons_columns where table_name='PAYMENT_HIS' and owner='ACCT';
CONSTRAINT_NAME COLUMN_NAME
------------------------------ ------------------------------
SYS_C00191845 PAYMENT_ID
SYS_C00191846 ACCT_ID
SYS_C00191847 PAYMENT_METHOD
SYS_C00191848 OPERATION_TYPE
SYS_C00191849 AMOUNT
SYS_C00191850 PAYMENT_DATE
SYS_C00191851 STATE
SYS_C00191852 STATE_DATE
SYS_C00191853 CREATED_DATE
SYS_C00191854 STAFF_ID
PK_P_PAYMENT_HIS PAYMENT_ID
11 rows selected.
select owner,constraint_name,constraint_type,status,invalid,validated from dba_constraints where table_name='PAYMENT_HIS' and owner='ACCT';
OWNER CONSTRAINT_NAME C STATUS INVALID VALIDATED
---------- ------------------------------ - -------- ------- -------------
ACCT SYS_C00191845 C ENABLED VALIDATED
ACCT SYS_C00191846 C ENABLED VALIDATED
ACCT SYS_C00191847 C ENABLED VALIDATED
ACCT SYS_C00191848 C ENABLED VALIDATED
ACCT SYS_C00191849 C ENABLED VALIDATED
ACCT SYS_C00191850 C ENABLED VALIDATED
ACCT SYS_C00191851 C ENABLED VALIDATED
ACCT SYS_C00191852 C ENABLED VALIDATED
ACCT SYS_C00191853 C ENABLED VALIDATED
ACCT SYS_C00191854 C ENABLED VALIDATED
ACCT PK_P_PAYMENT_HIS P ENABLED VALIDATED
11 rows selected.
col index_name for a30
col index_type for a15
col degree for a10
select index_name,index_type,uniqueness,blevel,status,degree,tablespace_name from dba_indexes where table_name='PAYMENT_HIS' and owner='ACCT';
INDEX_NAME INDEX_TYPE UNIQUENES BLEVEL STATUS DEGREE TABLESPACE_NAME
------------------------------ --------------- --------- ---------- -------- ---------- --------------------
INDEX_PAYMENT_HIS_STAFF_ID NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
IDX_P_PAYMENT_HIS_SERV_ID NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX03
IDX_P_PAYMENT_HIS_CREATED_DATE NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
IDX_P_PAYMENT_HIS_SERIAL_NBR NORMAL NONUNIQUE 1 VALID 1 ACCT_INDEX01
IDX_P_PAYMENT_HIS_ACCT_ID NORMAL NONUNIQUE 2 VALID 1 ACCT_PAY_INDEX01
IDX_P_PAYMENT_HIS_PAYMENT_DATE NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
IDX_PAYMENT_HIS_STATE_DATE NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX01
PK_P_PAYMENT_HIS NORMAL UNIQUE 2 VALID 1 ACCT_PAY_INDEX03
8 rows selected.
select index_name,partitioning_type,partition_count,def_tablespace_name from dba_part_indexes where table_name='PAYMENT_HIS' and owner='ACCT';
col index_name for a35
col partition_name for a30
col column_name for a20
select a.index_name,a.partition_name,b.column_name,a.blevel,a.status from dba_ind_partitions a,dba_ind_columns b
where a.index_owner=b.index_owner
and a.index_name=b.index_name
and b.table_name='PAYMENT_HIS' and a.index_owner='ACCT';
select * from dba_dependencies where owner='ACCT' and name='PAYMENT_HIS';
6.檢視中間表的相關資訊
col owner for a10
col constraint_name for a30
col column_name for a30
col tablespace_name for a20
select constraint_name,column_name from dba_cons_columns where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
CONSTRAINT_NAME COLUMN_NAME
------------------------------ --------------------
SYS_C00205559 PAYMENT_ID
SYS_C00205560 ACCT_ID
SYS_C00205561 PAYMENT_METHOD
SYS_C00205562 OPERATION_TYPE
SYS_C00205563 AMOUNT
SYS_C00205564 PAYMENT_DATE
SYS_C00205565 STATE
SYS_C00205566 STATE_DATE
SYS_C00205567 CREATED_DATE
SYS_C00205568 STAFF_ID
10 rows selected.
11 rows selected.
select owner,constraint_name,constraint_type,status,invalid,validated from dba_constraints where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
OWNER CONSTRAINT_NAME C STATUS INVALID VALIDATED
---------- ------------------------------ - -------- ------- -------------
ACCT SYS_C00205559 C ENABLED VALIDATED
ACCT SYS_C00205560 C ENABLED VALIDATED
ACCT SYS_C00205561 C ENABLED VALIDATED
ACCT SYS_C00205562 C ENABLED VALIDATED
ACCT SYS_C00205563 C ENABLED VALIDATED
ACCT SYS_C00205564 C ENABLED VALIDATED
ACCT SYS_C00205565 C ENABLED VALIDATED
ACCT SYS_C00205566 C ENABLED VALIDATED
ACCT SYS_C00205567 C ENABLED VALIDATED
ACCT SYS_C00205568 C ENABLED VALIDATED
10 rows selected.
col index_name for a30
col index_type for a15
col degree for a10
select index_name,index_type,uniqueness,blevel,status,degree,tablespace_name from dba_indexes where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
no rows selected
select index_name,partitioning_type,partition_count,def_tablespace_name from dba_part_indexes where table_name='PAYMENT_HIS' and owner='ACCT';
col index_name for a35
col partition_name for a30
col column_name for a20
select a.index_name,a.partition_name,b.column_name,a.blevel,a.status from dba_ind_partitions a,dba_ind_columns b
where a.index_owner=b.index_owner
and a.index_name=b.index_name
and b.table_name='PAYMENT_HIS' and a.index_owner='ACCT';
select * from dba_dependencies where owner='ACCT' and name='PAYMENT_HIS';
7 複製依賴的物件(採用自動)
第一個表
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ACCT', 'BILL_ITEM','BILL_ITEM_TEMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors,TRUE);
DBMS_OUTPUT.PUT_LINE(num_errors);
END;
/
第二個表
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ACCT', 'BILL_HIS','BILL_HIS_TEMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors,TRUE);
DBMS_OUTPUT.PUT_LINE(num_errors);
END;
/
第三個表
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ACCT', 'INVOICE_HIS','INVOICE_HIS_TEMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors,TRUE);
DBMS_OUTPUT.PUT_LINE(num_errors);
END;
/
同步完以後發現以前沒有的依賴物件現在有了
20:23:28 SQL> select index_name,index_type,uniqueness,blevel,status,degree,tablespace_name from dba_indexes where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
INDEX_NAME INDEX_TYPE UNIQUENES BLEVEL STATUS DEGREE TABLESPACE_NAME
----------------------------------- --------------- --------- ---------- -------- ---------- --------------------
TMP$$_IDX_P_PAYMENT_HIS_PAY0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
TMP$$_IDX_P_PAYMENT_HIS_SER1 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX03
TMP$$_IDX_P_PAYMENT_HIS_SER0 NORMAL NONUNIQUE 1 VALID 1 ACCT_INDEX01
TMP$$_PK_P_PAYMENT_HIS0 NORMAL UNIQUE 2 VALID 1 ACCT_PAY_INDEX03
TMP$$_IDX_P_PAYMENT_HIS_ACC0 NORMAL NONUNIQUE 2 VALID 1 ACCT_PAY_INDEX01
TMP$$_INDEX_PAYMENT_HIS_STA0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
TMP$$_IDX_P_PAYMENT_HIS_CRE0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
TMP$$_IDX_PAYMENT_HIS_STATE0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX01
8 rows selected.
Elapsed: 00:00:02.05
8 同步中間表
第一張表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ACCT', 'BILL_ITEM', 'BILL_ITEM_TEMP');
END;
/
第二張表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ACCT', 'BILL_HIS', 'BILL_HIS_TEMP');
END;
/
第三張表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ACCT', 'INVOICE_HIS', 'INVOICE_HIS_TEMP');
END;
/
如果表資料更新頻繁，可多執行幾次以減少FINISH_REDEF_TABLE的耗時
9 完成重定義
第一張表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ACCT', 'BILL_ITEM', 'BILL_ITEM_TEMP');
END;
/
第二張表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ACCT', 'BILL_HIS', 'BILL_HIS_TEMP');
END;
/
第三張表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ACCT', 'INVOICE_HIS', 'INVOICE_HIS_TEMP');
END;
/
關閉並行
alter session disable parallel dml;
alter session disable parallel query;
10 刪除臨時表 觀察一段時間--刪除後放入回收站
drop table ACCT.BILL_ITEM_TEMP cascade constraint;
drop table ACCT.BILL_HIS_TEMP cascade constraint;
drop table ACCT.INVOICE_HIS_TEMP cascade constraint;
對錶收集統計資訊
ACCT.PAYMENT_HIS
acct.BILL_HIS
acct.INVOICE_HIS
---在定重定義包
Summary of DBMS_REDEFINITION Subprograms:
=========================================
CAN_REDEF_TABLE Procedure:
--------------------------
This procedure determines if a given table can be reorganized online. This is
the first step of the online reorganization process. If the table is not a
candidate for online redefinition, an error message is raised.
SYNTAX
DBMS_REDEFINITION.can_redef_table (
uname IN VARCHAR2,
tname IN VARCHAR2);
CAN_REDEF_TABLE Procedure Parameters:
Parameter Description
--------- ------------
uname The schema name of the table.
tname The name of the table to be reorganized.
START_REDEF_TABLE Procedure:
----------------------------
This procedure initiates the reorganization process. After verifying that the
table can be reorganized online, you create an empty interim table (in the same
schema as the table to be reorganized) with the desired attributes of the
post-reorganization table.
SYNTAX
DBMS_REDEFINITION.start_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
col_mapping IN VARCHAR2 := NULL);
START_REDEF_TABLE Procedure Parameters:
Parameter Description
---------- ------------
uname The schema name of the tables.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
col_mapping The mapping information from the columns in the interim
table to the columns in the original table. (This is similar
to the column list on the SELECT clause of a query.) If NULL,
all the columns in the original table are selected and have
the same name after reorganization.
FINISH_REDEF_TABLE Procedure:
----------------------------
This procedure completes the reorganization process. Before this step you can
create new indexes, triggers, grants, and constraints on the interim table. The
referential constraints involving the interim table must be disabled. After
completing this step, the original table is locked briefly during this
procedure.
SYNTAX
DBMS_REDEFINITION.finish_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);
FINISH_REDEF_TABLE Procedure Parameters:
Parameter Description
--------- ------------
uname The schema name of the tables.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
SYNC_INTERIM_TABLE Procedure:
----------------------------
This procedure keeps the interim table synchronized with the original table.
This step is useful in minimizing the amount of synchronization needed to be
done by finish_reorg_table before completing the online reorganization. This
procedure can be called between long running operations (such as create index)
on the interim table to sync it up with the data in the original table and
speed up subsequent operations.
SYNTAX
DBMS_REDEFINITION.sync_interim_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);
SYNC_INTERIM_TABLE Procedure Parameters:
Parameters Description
---------- ------------
uname The schema name of the tables.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
ABORT_REDEF_TABLE Procedure:
----------------------------
This procedure cleans up errors that occur during the reorganization process.
This procedure can also be used to abort the reorganization process any time
after start_reorg_table has been called and before finish_reorg_table is called.
SYNTAX
DBMS_REDEFINITION.abort_redef_table (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2);
ABORT_REDEF_TABLE Procedure Parameters:
Parameters Description
---------- ------------
uname The schema name of the table.
orig_table The name of the table to be reorganized.
int_table The name of the interim table.
If we have an encrypted column which is part of primary key then
follow the steps mentioned in the bug below.
Reference: Bug 6034260 DBMS_REDEFINITION.START_REDEF_TABLE FAILS ORA-32412 BECAUSE
OF ENCRYPTED COLUMN
COPY_TABLE_DEPENDENTS (Procedure)
Copies the dependent objects of the original table to the interim table
COPY_TABLE_DEPENDENTS.dbms_redefinition.copy_table_dependents(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
copy_indexes IN PLS_INTEGER := 1,
copy_triggers IN BOOLEAN := TRUE,
copy_constraints IN BOOLEAN := TRUE,
copy_privileges IN BOOLEAN := TRUE,
ignore_errors IN BOOLEAN := FALSE,
num_errors OUT PLS_INTEGER,
copy_statistics IN BOOLEAN := FALSE
copy_mvlog IN BOOLEAN := FALSE);
RELATED DOCUMENTS
-----------------
select dbms_metadata.get_ddl('TABLE','BILL_ITEM','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."BILL_ITEM_TEMP"
( "BILL_ITEM_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,
"CLASSIFY" VARCHAR2(3) NOT NULL ENABLE,
"BILL_PARENT_ID" NUMBER(9,0),
"REMARK_ID" NUMBER(9,0),
"PRINT_ORDER" NUMBER(5,0),
"BILL_ITEM_NAME" VARCHAR2(50) NOT NULL ENABLE,
"ORG_ID" NUMBER(9,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP)
TABLESPACE "ACCT_DATA02"
---------測試
1 檢查是否支援線上重定義
表 ACCT.PAYMENT_HIS
set serveroutput on
set timing on time on
set feedback on
set lines 200 pages 999
alter system set recyclebin=ON;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('ACCT','BILL_ITEM',DBMS_REDEFINITION.CONS_USE_PK);
END;
/
2 建立中間表
select dbms_metadata.get_ddl('TABLE','PAYMENT_HIS','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."PAYMENT_HIS_TEMP"
( "PAYMENT_ID" NUMBER(12,0) NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"PAYMENT_METHOD" NUMBER(9,0) NOT NULL ENABLE,
"PAYED_METHOD" NUMBER(9,0),
"OPERATION_TYPE" VARCHAR2(3) NOT NULL ENABLE,
"OPERATED_PAYMENT_SERIAL_NBR" NUMBER(12,0),
"AMOUNT" NUMBER(16,5) NOT NULL ENABLE,
"PAYMENT_DATE" DATE NOT NULL ENABLE,
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ACC_NBR" VARCHAR2(32),
"PAY_CYCLE_ID" NUMBER(12,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "COMM_TBS";
select dbms_metadata.get_ddl('TABLE','BILL_HIS','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."BILL_HIS_TEMP"
( "BILL_ID" NUMBER(12,0) NOT NULL ENABLE,
"OPERATED_BILL_ID" NUMBER(12,0),
"PAYMENT_ID" NUMBER(12,0),
"PAYMENT_METHOD" NUMBER(9,0),
"BILLING_CYCLE_ID" NUMBER(9,0) NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,
"SERV_ID" NUMBER(12,0),
"ACC_NBR" VARCHAR2(32),
"BILL_AMOUNT" NUMBER(16,0) NOT NULL ENABLE,
"LATE_FEE" NUMBER(16,0) NOT NULL ENABLE,
"DERATE_LATE_FEE" NUMBER(16,0) NOT NULL ENABLE,
"BALANCE" NUMBER(16,0) NOT NULL ENABLE,
"DEPOSIT_AMOUNT" NUMBER(16,0) NOT NULL ENABLE,
"LAST_CHANGE" NUMBER(12,0) NOT NULL ENABLE,
"CUR_CHANGE" NUMBER(12,0) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"PAYMENT_DATE" DATE NOT NULL ENABLE,
"USE_DERATE_BLANCE" NUMBER(16,0) NOT NULL ENABLE,
"INVOICE_ID" NUMBER(12,0),
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"STATE" VARCHAR2(3) NOT NULL ENABLE,
"STATE_DATE" DATE NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "COMM_TBS";
select dbms_metadata.get_ddl('TABLE','INVOICE_HIS','ACCT') FROM DUAL;
CREATE TABLE "ACCT"."INVOICE_HIS_TEMP"
( "INVOICE_ID" NUMBER(12,0) NOT NULL ENABLE,
"PRINT_SEQ" NUMBER(8,0) NOT NULL ENABLE,
"PAYMENT_ID" NUMBER(12,0) NOT NULL ENABLE,
"BILLING_CYCLE_ID" NUMBER(9,0),
"REQUIRE_ID" NUMBER(9,0) NOT NULL ENABLE,
"NAME" VARCHAR2(50),
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"BILL_ID" NUMBER(12,0),
"ACC_NBR" VARCHAR2(32) NOT NULL ENABLE,
"AMOUNT" NUMBER(16,5) NOT NULL ENABLE,
"COUNT" NUMBER(8,0) NOT NULL ENABLE,
"PRINT_FLAG" VARCHAR2(3) NOT NULL ENABLE,
"INVOICE_TYPE" VARCHAR2(3) NOT NULL ENABLE,
"ACCT_ID" NUMBER(12,0),
"SERV_ID" NUMBER(12,0),
"INVOICE_FLAG" NUMBER(2,0),
"PRINT_DATE" DATE NOT NULL ENABLE,
"RECEIPT_CLASS" NUMBER(3,0),
"INVOICE_NUM" VARCHAR2(20),
"PAYED_METHOD" NUMBER(9,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "COMM_TBS";
3 開始重定義
--為了加快重定義的速度，考慮開啟並行
alter session force parallel dml parallel 6;
alter session force parallel query parallel 6;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ACCT', 'BILL_ITEM','BILL_ITEM_TEMP',NULL,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ACCT', 'BILL_HIS','BILL_HIS_TEMP',NULL,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ACCT', 'INVOICE_HIS','INVOICE_HIS_TEMP',NULL,DBMS_REDEFINITION.CONS_USE_PK);
END;
/
select owner,segment_name,partition_name,segment_type,tablespace_name,blocks,bytes/1024/1024 from dba_segments where owner='ACCT' and segment_name='PAYMENT_HIS_TEMP';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCKS BYTES/1024/1024
---------- -------------------- --------------- ------------------ ------------------------------ ---------- ---------------
ACCT PAYMENT_HIS_TEMP TABLE COMM_TBS 60704 474.25
4 檢查上一步同步所依賴物件是否有錯
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
5.檢視原表的相關資訊
col owner for a10
col constraint_name for a30
col column_name for a30
col tablespace_name for a20
select constraint_name,column_name from dba_cons_columns where table_name='PAYMENT_HIS' and owner='ACCT';
CONSTRAINT_NAME COLUMN_NAME
------------------------------ ------------------------------
SYS_C00191845 PAYMENT_ID
SYS_C00191846 ACCT_ID
SYS_C00191847 PAYMENT_METHOD
SYS_C00191848 OPERATION_TYPE
SYS_C00191849 AMOUNT
SYS_C00191850 PAYMENT_DATE
SYS_C00191851 STATE
SYS_C00191852 STATE_DATE
SYS_C00191853 CREATED_DATE
SYS_C00191854 STAFF_ID
PK_P_PAYMENT_HIS PAYMENT_ID
11 rows selected.
select owner,constraint_name,constraint_type,status,invalid,validated from dba_constraints where table_name='PAYMENT_HIS' and owner='ACCT';
OWNER CONSTRAINT_NAME C STATUS INVALID VALIDATED
---------- ------------------------------ - -------- ------- -------------
ACCT SYS_C00191845 C ENABLED VALIDATED
ACCT SYS_C00191846 C ENABLED VALIDATED
ACCT SYS_C00191847 C ENABLED VALIDATED
ACCT SYS_C00191848 C ENABLED VALIDATED
ACCT SYS_C00191849 C ENABLED VALIDATED
ACCT SYS_C00191850 C ENABLED VALIDATED
ACCT SYS_C00191851 C ENABLED VALIDATED
ACCT SYS_C00191852 C ENABLED VALIDATED
ACCT SYS_C00191853 C ENABLED VALIDATED
ACCT SYS_C00191854 C ENABLED VALIDATED
ACCT PK_P_PAYMENT_HIS P ENABLED VALIDATED
11 rows selected.
col index_name for a30
col index_type for a15
col degree for a10
select index_name,index_type,uniqueness,blevel,status,degree,tablespace_name from dba_indexes where table_name='PAYMENT_HIS' and owner='ACCT';
INDEX_NAME INDEX_TYPE UNIQUENES BLEVEL STATUS DEGREE TABLESPACE_NAME
------------------------------ --------------- --------- ---------- -------- ---------- --------------------
INDEX_PAYMENT_HIS_STAFF_ID NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
IDX_P_PAYMENT_HIS_SERV_ID NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX03
IDX_P_PAYMENT_HIS_CREATED_DATE NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
IDX_P_PAYMENT_HIS_SERIAL_NBR NORMAL NONUNIQUE 1 VALID 1 ACCT_INDEX01
IDX_P_PAYMENT_HIS_ACCT_ID NORMAL NONUNIQUE 2 VALID 1 ACCT_PAY_INDEX01
IDX_P_PAYMENT_HIS_PAYMENT_DATE NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
IDX_PAYMENT_HIS_STATE_DATE NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX01
PK_P_PAYMENT_HIS NORMAL UNIQUE 2 VALID 1 ACCT_PAY_INDEX03
8 rows selected.
select index_name,partitioning_type,partition_count,def_tablespace_name from dba_part_indexes where table_name='PAYMENT_HIS' and owner='ACCT';
col index_name for a35
col partition_name for a30
col column_name for a20
select a.index_name,a.partition_name,b.column_name,a.blevel,a.status from dba_ind_partitions a,dba_ind_columns b
where a.index_owner=b.index_owner
and a.index_name=b.index_name
and b.table_name='PAYMENT_HIS' and a.index_owner='ACCT';
select * from dba_dependencies where owner='ACCT' and name='PAYMENT_HIS';
6.檢視中間表的相關資訊
col owner for a10
col constraint_name for a30
col column_name for a30
col tablespace_name for a20
select constraint_name,column_name from dba_cons_columns where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
CONSTRAINT_NAME COLUMN_NAME
------------------------------ --------------------
SYS_C00205559 PAYMENT_ID
SYS_C00205560 ACCT_ID
SYS_C00205561 PAYMENT_METHOD
SYS_C00205562 OPERATION_TYPE
SYS_C00205563 AMOUNT
SYS_C00205564 PAYMENT_DATE
SYS_C00205565 STATE
SYS_C00205566 STATE_DATE
SYS_C00205567 CREATED_DATE
SYS_C00205568 STAFF_ID
10 rows selected.
11 rows selected.
select owner,constraint_name,constraint_type,status,invalid,validated from dba_constraints where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
OWNER CONSTRAINT_NAME C STATUS INVALID VALIDATED
---------- ------------------------------ - -------- ------- -------------
ACCT SYS_C00205559 C ENABLED VALIDATED
ACCT SYS_C00205560 C ENABLED VALIDATED
ACCT SYS_C00205561 C ENABLED VALIDATED
ACCT SYS_C00205562 C ENABLED VALIDATED
ACCT SYS_C00205563 C ENABLED VALIDATED
ACCT SYS_C00205564 C ENABLED VALIDATED
ACCT SYS_C00205565 C ENABLED VALIDATED
ACCT SYS_C00205566 C ENABLED VALIDATED
ACCT SYS_C00205567 C ENABLED VALIDATED
ACCT SYS_C00205568 C ENABLED VALIDATED
10 rows selected.
col index_name for a30
col index_type for a15
col degree for a10
select index_name,index_type,uniqueness,blevel,status,degree,tablespace_name from dba_indexes where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
no rows selected
select index_name,partitioning_type,partition_count,def_tablespace_name from dba_part_indexes where table_name='PAYMENT_HIS' and owner='ACCT';
col index_name for a35
col partition_name for a30
col column_name for a20
select a.index_name,a.partition_name,b.column_name,a.blevel,a.status from dba_ind_partitions a,dba_ind_columns b
where a.index_owner=b.index_owner
and a.index_name=b.index_name
and b.table_name='PAYMENT_HIS' and a.index_owner='ACCT';
select * from dba_dependencies where owner='ACCT' and name='PAYMENT_HIS';
7 複製依賴的物件(採用自動)
第一個表
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ACCT', 'BILL_ITEM','BILL_ITEM_TEMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors,TRUE);
DBMS_OUTPUT.PUT_LINE(num_errors);
END;
/
第二個表
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ACCT', 'BILL_HIS','BILL_HIS_TEMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors,TRUE);
DBMS_OUTPUT.PUT_LINE(num_errors);
END;
/
第三個表
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ACCT', 'INVOICE_HIS','INVOICE_HIS_TEMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors,TRUE);
DBMS_OUTPUT.PUT_LINE(num_errors);
END;
/
同步完以後發現以前沒有的依賴物件現在有了
20:23:28 SQL> select index_name,index_type,uniqueness,blevel,status,degree,tablespace_name from dba_indexes where table_name='PAYMENT_HIS_TEMP' and owner='ACCT';
INDEX_NAME INDEX_TYPE UNIQUENES BLEVEL STATUS DEGREE TABLESPACE_NAME
----------------------------------- --------------- --------- ---------- -------- ---------- --------------------
TMP$$_IDX_P_PAYMENT_HIS_PAY0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
TMP$$_IDX_P_PAYMENT_HIS_SER1 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX03
TMP$$_IDX_P_PAYMENT_HIS_SER0 NORMAL NONUNIQUE 1 VALID 1 ACCT_INDEX01
TMP$$_PK_P_PAYMENT_HIS0 NORMAL UNIQUE 2 VALID 1 ACCT_PAY_INDEX03
TMP$$_IDX_P_PAYMENT_HIS_ACC0 NORMAL NONUNIQUE 2 VALID 1 ACCT_PAY_INDEX01
TMP$$_INDEX_PAYMENT_HIS_STA0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
TMP$$_IDX_P_PAYMENT_HIS_CRE0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX02
TMP$$_IDX_PAYMENT_HIS_STATE0 NORMAL NONUNIQUE 2 VALID 1 ACCT_INDEX01
8 rows selected.
Elapsed: 00:00:02.05
8 同步中間表
第一張表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ACCT', 'BILL_ITEM', 'BILL_ITEM_TEMP');
END;
/
第二張表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ACCT', 'BILL_HIS', 'BILL_HIS_TEMP');
END;
/
第三張表
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ACCT', 'INVOICE_HIS', 'INVOICE_HIS_TEMP');
END;
/
如果表資料更新頻繁，可多執行幾次以減少FINISH_REDEF_TABLE的耗時
9 完成重定義
第一張表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ACCT', 'BILL_ITEM', 'BILL_ITEM_TEMP');
END;
/
第二張表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ACCT', 'BILL_HIS', 'BILL_HIS_TEMP');
END;
/
第三張表
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ACCT', 'INVOICE_HIS', 'INVOICE_HIS_TEMP');
END;
/
關閉並行
alter session disable parallel dml;
alter session disable parallel query;
10 刪除臨時表 觀察一段時間--刪除後放入回收站
drop table ACCT.BILL_ITEM_TEMP cascade constraint;
drop table ACCT.BILL_HIS_TEMP cascade constraint;
drop table ACCT.INVOICE_HIS_TEMP cascade constraint;
對錶收集統計資訊
ACCT.PAYMENT_HIS
acct.BILL_HIS
acct.INVOICE_HIS
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29446986/viewspace-1173575/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle表線上重定義Oracle
- 【Oracle】線上重定義表Oracle
- Oracle表的線上重定義（一）Oracle
- oracle線上重定義表步驟Oracle
- Oracle中重建表的線上重定義Oracle
- 線上重定義表ORACLE 11GOracle
- oracle 線上重定義Oracle
- Oracle線上重定義Oracle
- 【TABLE】oracle表線上重定義注意事項Oracle
- oracle實驗記錄 （線上重定義表）Oracle
- ORACLE 線上重新定義表分割槽表重定義為普通表。Oracle
- 在oracle 9i下線上重定義表Oracle
- Oracle的線上重定義(轉)Oracle
- dbms_redefinition線上重定義表
- 使用ORACLE線上重定義將普通表改為分割槽表Oracle
- oracle分割槽表線上重定義欄位not null問題OracleNull
- ORACLE 普通錶轉換成分割槽表（線上重定義）Oracle
- 資料庫表的線上重定義資料庫
- 利用DBMS_REDEFINITION線上重定義表
- 【Oracle】利用線上重定義的方式改變普通表為分割槽表Oracle
- ORACLE 普通錶轉換成分割槽表（線上重定義）(zt)Oracle
- Oracle線上重定義之DBMS_REDEFINITIONOracle
- Oracle Online Redefinition線上重定義（上）Oracle
- Oracle Online Redefinition線上重定義（中）Oracle
- Oracle Online Redefinition線上重定義（下）Oracle
- 普通錶轉換分割槽表-線上重定義
- 分割槽表和dbms_redefinition包線上重定義表
- 使用DBMS_REDEFINITION線上重定義表普通表為分割槽表
- Oracle 9i中表的線上重定義（轉）Oracle
- 線上重定義 ?普通錶轉換成分割槽表
- OCM實驗-使用線上重定義方式遷移表
- 海量資料處理_表分割槽(線上重定義)
- oracle 線上重組表Oracle
- 使用線上重定義方法改造普通表為分割槽表實戰
- 線上重定義表(Redefine Tables Online) - dbms_redefinition
- Online Redefinition線上重定義（三）--多表關聯重定義案例
- Online Redefinition線上重定義（二）--單表複雜案例
- 線上重定義表導致constraint變成novalidateAI