Online Redefinition

yyp2009發表於2012-12-20

 begin
   dbms_redefinition.can_redef_table(uname       => 'CT10000UAM',
                                     tname       => 'UISS_AUTHENTICATION_LOG',
                                     options_flag =>
DBMS_REDEFINITION.CONS_USE_ROWID);
 end;
 /
 
 
 begin
exec dbms_redefinition.can_redef_table('CT10000UAM','UISS_AUTHENTICATION_LOG');end;
                                     options_flag =>
DBMS_REDEFINITION.CONS_USE_ROWID);
 end;
 /
 
 
 
 
 CREATE TABLE UISS_AUTHENTICATION_LOG_TP
(
TRANSACTIONID                                      VARCHAR2(50 CHAR),
 CREATEBY                                           VARCHAR2(50 CHAR),
 CREATETIME                                         TIMESTAMP(6),
 LASTMODIFYBY                                       VARCHAR2(50 CHAR),
 LASTMODIFYTIME                                     TIMESTAMP(6),
 ACCOUNTID                                          VARCHAR2(100 CHAR),
 ACCOUNTTYPE                                        VARCHAR2(20 CHAR),
 ACCOUNTPASSWORD                                    VARCHAR2(200 CHAR),
 PWDTYPE                                            VARCHAR2(20 CHAR),
 SRCSYSID                                           VARCHAR2(20 CHAR),
 DSTSYSID                                           VARCHAR2(20 CHAR),
 RSPCODE                                            VARCHAR2(20 CHAR),
 RSPMESSAGE                                         VARCHAR2(4000 CHAR),
 ELAPSEDDATE                                        VARCHAR2(100 CHAR),
 RECORDID                                           VARCHAR2(50 CHAR)  NOT NULL,
 REQMESSAGE                                         VARCHAR2(4000 CHAR),
 MICHAELYANG                                        VARCHAR2(4000 CHAR)
)
PARTITION BY range(CREATETIME)
(
partition UISS_AUTHENTICATION_LOG201210 values less than(TO_DATE('2012/10','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201211 values less than(TO_DATE('2012/11','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201212 values less than(TO_DATE('2012/12','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201301 values less than(TO_DATE('2013/01','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201302 values less than(TO_DATE('2013/02','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201303 values less than(TO_DATE('2013/03','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201304 values less than(TO_DATE('2013/04','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201305 values less than(TO_DATE('2013/05','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201306 values less than(TO_DATE('2013/06','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201307 values less than(TO_DATE('2013/07','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201308 values less than(TO_DATE('2013/08','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201309 values less than(TO_DATE('2013/09','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201310 values less than(TO_DATE('2013/10','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201311 values less than(TO_DATE('2013/11','YYYY/MM')),
partition UISS_AUTHENTICATION_LOG201312 values less than(TO_DATE('2013/12','YYYY/MM'))
)                


alter session force parallel dml parallel 6;
alter session force parallel query parallel 6;

set timing on;
begin
DBMS_REDEFINITION.START_REDEF_TABLE(uname       => 'CT10000UAM',
orig_table  => 'UISS_AUTHENTICATION_LOG',
int_table   => 'UISS_AUTHENTICATION_LOG_TP',
col_mapping => 'TRANSACTIONID TRANSACTIONID,CREATEBY CREATEBY,CREATETIME
CREATETIME,LASTMODIFYBY LASTMODIFYBY,LASTMODIFYTIME LASTMODIFYTIME,ACCOUNTID ACCOUNTID,ACCOUNTTYPE ACCOUNTTYPE,
ACCOUNTPASSWORD ACCOUNTPASSWORD,PWDTYPE PWDTYPE,SRCSYSID SRCSYSID,DSTSYSID DSTSYSID,RSPCODE RSPCODE,RSPMESSAGE RSPMESSAGE,
ELAPSEDDATE ELAPSEDDATE,RECORDID RECORDID,REQMESSAGE REQMESSAGE',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
end;


select * from dba_mview_logs where master='UISS_AUTHENTICATION_LOG';

SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(uname       => 'CT10000UAM',
  3  orig_table  => 'UISS_AUTHENTICATION_LOG',
  4  int_table   => 'UISS_AUTHENTICATION_LOG_TP',
  5  col_mapping => 'TRANSACTIONID TRANSACTIONID,CREATEBY CREATEBY,CREATETIME
  6  CREATETIME,LASTMODIFYBY LASTMODIFYBY,LASTMODIFYTIME LASTMODIFYTIME,ACCOUNTID ACCOUNTID,ACCOUNTTYPE ACCOUNTTYPE,
  7  ACCOUNTPASSWORD ACCOUNTPASSWORD,PWDTYPE PWDTYPE,SRCSYSID SRCSYSID,DSTSYSID DSTSYSID,RSPCODE RSPCODE,RSPMESSAGE RSPMESSAGE,
ELAPSEDDATE ELAPSEDDATE,RECORDID RECORDID,REQMESSAGE REQMESSAGE',
  8    9  options_flag => DBMS_REDEFINITION.CONS_USE_PK);
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-12091: cannot online redefine table "CT10000UAM"."UISS_AUTHENTICATION_LOG"
with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2

 

SQL> select * from dba_mview_logs where master='UISS_AUTHENTICATION_LOG';

LOG_OWNER                      MASTER
------------------------------ ------------------------------
LOG_TABLE                      LOG_TRIGGER                    ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC
--- ---
CT10000UAM                     UISS_AUTHENTICATION_LOG
MLOG$_UISS_AUTHENTICATION_                                    NO  YES NO  NO
NO  NO

 

drop materialized view tb_mv;


SQL> drop materialized view log on CT10000UAM.UISS_AUTHENTICATION_LOG;

Materialized view log dropped.

SQL> begin
  2  DBMS_REDEFINITION.START_REDEF_TABLE(uname       => 'CT10000UAM',
  3  orig_table  => 'UISS_AUTHENTICATION_LOG',
int_table   => 'UISS_AUTHENTICATION_LOG_TP',
col_mapping => 'TRANSACTIONID TRANSACTIONID,CREATEBY CREATEBY,CREATETIME
CREATETIME,LASTMODIFYBY LASTMODIFYBY,LASTMODIFYTIME LASTMODIFYTIME,ACCOUNTID ACCOUNTID,ACCOUNTTYPE ACCOUNTTYPE,
ACCOUNTPASSWORD ACCOUNTPASSWORD,PWDTYPE PWDTYPE,SRCSYSID SRCSYSID,DSTSYSID DSTSYSID,RSPCODE RSPCODE,RSPMESSAGE RSPMESSAGE,
ELAPSEDDATE ELAPSEDDATE,RECORDID RECORDID,REQMESSAGE REQMESSAGE',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
  4    5    6    7    8    9   10  end;
 11 
 12 
 13 
 14 
 15 
 16  /
begin
*
ERROR at line 1:
ORA-23539: table "CT10000UAM"."UISS_AUTHENTICATION_LOG" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2


exec dbms_redefinition.abort_redef_table('CT10000UAM','UISS_AUTHENTICATION_LOG','UISS_AUTHENTICATION_LOG_TP');
SQL> exec dbms_redefinition.abort_redef_table('CT10000UAM','UISS_AUTHENTICATION_LOG','UISS_AUTHENTICATION_LOG_TP');

PL/SQL procedure successfully completed.

SQL> begin
DBMS_REDEFINITION.START_REDEF_TABLE(uname       => 'CT10000UAM',
orig_table  => 'UISS_AUTHENTICATION_LOG',
int_table   => 'UISS_AUTHENTICATION_LOG_TP',
col_mapping => 'TRANSACTIONID TRANSACTIONID,CREATEBY CREATEBY,CREATETIME
CREATETIME,LASTMODIFYBY LASTMODIFYBY,LASTMODIFYTIME LASTMODIFYTIME,ACCOUNTID ACCOUNTID,ACCOUNTTYPE ACCOUNTTYPE,
ACCOUNTPASSWORD ACCOUNTPASSWORD,PWDTYPE PWDTYPE,SRCSYSID SRCSYSID,DSTSYSID DSTSYSID,RSPCODE RSPCODE,RSPMESSAGE RSPMESSAGE,
ELAPSEDDATE ELAPSEDDATE,RECORDID RECORDID,REQMESSAGE REQMESSAGE',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
end;  2    3    4    5    6    7    8    9   10 
 11 
 12  /

PL/SQL procedure successfully completed.

Elapsed: 00:23:05.29
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
SQL>

 


 DECLARE
   num_errors PLS_INTEGER;
 BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname           => 'CT10000UAM',
                                           orig_table      => 'UISS_AUTHENTICATION_LOG',
                                           int_table       => 'UISS_AUTHENTICATION_LOG_TP',
                                           copy_indexes    => TRUE,
                                          copy_triggers   => TRUE,
                                          copy_constraints => FALSE,
                                           copy_privileges => TRUE,
                                           ignore_errors   => FALSE,
                                           copy_statistics => TRUE);
 END;
 /
 
 DBMS_REDEFINITION.cons_orig_params,
 
 declare
  n number;
  begin
  dbms_redefinition.COPY_TABLE_DEPENDENTS('CT10000UAM','UISS_AUTHENTICATION_LOG','UISS_AUTHENTICATION_LOG_TP',1,true,true,true,true,n,true);
  end;
  /


 
  begin
  dbms_redefinition.sync_redef_table(uname     => 'CT10000UAM',
                                       orig_table => 'UISS_AUTHENTICATION_LOG',
                                       int_table => 'UISS_AUTHENTICATION_LOG_TP');
end;
/                                   
 exec dbms_redefinition.sync_interim_table('CT10000UAM','UISS_AUTHENTICATION_LOG','UISS_AUTHENTICATION_LOG_TP');


begin
dbms_redefinition.finish_redef_table(uname => 'CT10000UAM',
orig_table => 'UISS_AUTHENTICATION_LOG',
int_table => 'UISS_AUTHENTICATION_LOG_TP');
end;
/

 exec dbms_redefinition.finish_redef_table('howey','test','int_test');

 

Elapsed: 00:00:00.00
SQL>
SQL>  exec dbms_redefinition.sync_interim_table('CT10000UAM','UISS_AUTHENTICATION_LOG','UISS_AUTHENTICATION_LOG_TP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> begin
  2  dbms_redefinition.finish_redef_table(uname => 'CT10000UAM',
  3  orig_table => 'UISS_AUTHENTICATION_LOG',
  4  int_table => 'UISS_AUTHENTICATION_LOG_TP');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.41

SQL> select * from dba_unused_col_tabs;

no rows selected

Elapsed: 00:00:00.07
SQL> select * from dba_tab_cols where wner='CT10000UAM' and column_name like 'SYS%' and table_name='UISS_AUTHENTICATION_LOG_TP';

no rows selected

Elapsed: 00:00:00.04
SQL>

驗證：
QL> desc ct10000uam.UISS_AUTHENTICATION_LOG_TP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRANSACTIONID                                      VARCHAR2(50 CHAR)
 CREATEBY                                           VARCHAR2(50 CHAR)
 CREATETIME                                         TIMESTAMP(6)
 LASTMODIFYBY                                       VARCHAR2(50 CHAR)
 LASTMODIFYTIME                                     TIMESTAMP(6)
 ACCOUNTID                                          VARCHAR2(100 CHAR)
 ACCOUNTTYPE                                        VARCHAR2(20 CHAR)
 ACCOUNTPASSWORD                                    VARCHAR2(200 CHAR)
 PWDTYPE                                            VARCHAR2(20 CHAR)
 SRCSYSID                                           VARCHAR2(20 CHAR)
 DSTSYSID                                           VARCHAR2(20 CHAR)
 RSPCODE                                            VARCHAR2(20 CHAR)
 RSPMESSAGE                                         VARCHAR2(4000 CHAR)
 ELAPSEDDATE                                        VARCHAR2(100 CHAR)
 RECORDID                                  NOT NULL VARCHAR2(50 CHAR)
 REQMESSAGE                                         VARCHAR2(4000 CHAR)

SQL> desc ct10000uam.UISS_AUTHENTICATION_LOG
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TRANSACTIONID                                      VARCHAR2(50 CHAR)
 CREATEBY                                           VARCHAR2(50 CHAR)
 CREATETIME                                         TIMESTAMP(6)
 LASTMODIFYBY                                       VARCHAR2(50 CHAR)
 LASTMODIFYTIME                                     TIMESTAMP(6)
 ACCOUNTID                                          VARCHAR2(100 CHAR)
 ACCOUNTTYPE                                        VARCHAR2(20 CHAR)
 ACCOUNTPASSWORD                                    VARCHAR2(200 CHAR)
 PWDTYPE                                            VARCHAR2(20 CHAR)
 SRCSYSID                                           VARCHAR2(20 CHAR)
 DSTSYSID                                           VARCHAR2(20 CHAR)
 RSPCODE                                            VARCHAR2(20 CHAR)
 RSPMESSAGE                                         VARCHAR2(4000 CHAR)
 ELAPSEDDATE                                        VARCHAR2(100 CHAR)
 RECORDID                                  NOT NULL VARCHAR2(50 CHAR)
 REQMESSAGE                                         VARCHAR2(4000 CHAR)
 MICHAELYANG                                        VARCHAR2(4000 CHAR)

SQL> set linesize 500
SQL> set pagesize 600
SQL>
SQL>  select partition_name from dba_tab_partitions where table_name='UISS_AUTHENTICATION_LOG';

PARTITION_NAME
------------------------------
UISS_AUTHENTICATION_LOG201210
UISS_AUTHENTICATION_LOG201211
UISS_AUTHENTICATION_LOG201212
UISS_AUTHENTICATION_LOG201301
UISS_AUTHENTICATION_LOG201302
UISS_AUTHENTICATION_LOG201303
UISS_AUTHENTICATION_LOG201304
UISS_AUTHENTICATION_LOG201305
UISS_AUTHENTICATION_LOG201306
UISS_AUTHENTICATION_LOG201307
UISS_AUTHENTICATION_LOG201308
UISS_AUTHENTICATION_LOG201309
UISS_AUTHENTICATION_LOG201310
UISS_AUTHENTICATION_LOG201311
UISS_AUTHENTICATION_LOG201312

15 rows selected.

SQL>  select partition_name from dba_tab_partitions where table_name='UISS_AUTHENTICATION_LOG_TP';

no rows selected


SQL>
SQL>
SQL> select count(*) from ct10000uam.UISS_AUTHENTICATION_LOG partition (UISS_AUTHENTICATION_LOG201210);

  COUNT(*)
----------
  20501328
  SQL> select count(*) from ct10000uam.UISS_AUTHENTICATION_LOG partition (UISS_AUTHENTICATION_LOG201211);

  COUNT(*)
----------
   4175763

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-751464/，如需轉載，請註明出處，否則將追究法律責任。

相關文章