oracle 表線上重定義

hurp_oracle發表於2014-05-31
在生產環境下,有時侯我們需要對錶進行表空間的遷移,或碎片整理等,我們可以透過線上重定義來完成。

---在定重定義包
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章