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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13750068/viewspace-751464/