Online Redefinition

本文详细阐述了一次数据库重构过程,包括使用PL/SQL脚本进行表结构调整、迁移依赖项及同步临时表,最终完成重构并验证重构效果。

 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/

本项目采用C++编程语言结合ROS框架构建了完整的双机械臂控制系统,实现了Gazebo仿真环境下的协同运动模拟,并完成了两台实体UR10工业机器人的联动控制。该毕业设计在答辩环节获得98分的优异成绩,所有程序代码均通过系统性调试验证,保证可直接部署运行。 系统架构包含三个核心模块:基于ROS通信架构的双臂协调控制器、Gazebo物理引擎下的动力学仿真环境、以及真实UR10机器人的硬件接口层。在仿真验证阶段,开发了双臂碰撞检测算法和轨迹规划模块,通过ROS控制包实现了末端执行器的同步轨迹跟踪。硬件集成方面,建立了基于TCP/IP协议的实时通信链路,解决了双机数据同步和运动指令分发等关键技术问题。 本资源适用于自动化、机械电子、人工智能等专业方向的课程实践,可作为高年级课程设计、毕业课题的重要参考案例。系统采用模块化设计理念,控制核心与硬件接口分离架构便于功能扩展,具备工程实践能力的学习者可在现有框架基础上进行二次开发,例如集成视觉感知模块或优化运动规划算法。 项目文档详细记录了环境配置流程、参数调试方法和实验验证数据,特别说明了双机协同作业时的时序同步解决方案。所有功能模块均提供完整的API接口说明,便于使用者快速理解系统架构并进行定制化修改。 资源来源于网络分享,仅用于学习交流使用,请勿用于商业,如有侵权请联系我删除!
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值