[Oracle小经验]解决由于物化视图日志导致的不能进行表重命名【转贴项目组数据库工程师】

背景：

系统组兄弟相对一个Oracle普通用户表进行重命名，理论上是可以操作的。

在执行过程中报错，考虑到有些表间约束可能限制了操作，因此建议解除相关限制，例如物化视图，表间调用等

 

oracle10@emipkf00:[/export/home1/oracle10]sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Sep 23 14:02:33 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> conn eos52/eos52

Connected.

SQL> alter table EOS52.TBL_COMM_COMMONTICKET rename to TBL_COMM_COMMONTICKET_B1;

alter table EOS52.TBL_COMM_COMMONTICKET rename to TBL_COMM_COMMONTICKET_B1

*

ERROR at line 1:

ORA-26563: renaming this table is not allowed

SQL> 

SQL> conn / as sysdba

Connected.

SQL> 

SQL> SELECT MASTER, LOG_TABLE FROM DBA_MVIEW_LOGS WHERE LOG_OWNER ='EOS52'; /*查找物化视图日志记录*/

MASTER                         LOG_TABLE

------------------------------ ------------------------------

EOSOPERATOR                    MLOG$_EOSOPERATOR

EOSOPERATORROLE                MLOG$_EOSOPERATORROLE

EOSORG_T_EMPLOYEE              MLOG$_EOSORG_T_EMPLOYEE

EOSORG_T_EMPPOSITION           MLOG$_EOSORG_T_EMPPOSITION

EOSORG_T_ORGANIZATION          MLOG$_EOSORG_T_ORGANIZATIO

EOSORG_T_POSITION              MLOG$_EOSORG_T_POSITION

EOSROLE                        MLOG$_EOSROLE

TBL_COMM_COMMONTICKET          MLOG$_TBL_COMM_COMMONTICKE

TBL_COMM_COMMONTICKET_C        MLOG$_TBL_COMM_COMMONTICKE1

TBL_COMM_PARTICIPANT           MLOG$_TBL_COMM_PARTICIPANT

TBL_COMM_PROC_STATUS           MLOG$_TBL_COMM_PROC_STATUS

MASTER                         LOG_TABLE

------------------------------ ------------------------------

TBL_COMM_REPORT_TICKETTIMEOUT  MLOG$_TBL_COMM_REPORT_TICK

TBL_COMM_STATUSREG             MLOG$_TBL_COMM_STATUSREG

TBL_TSCL_ACTIVITYDEF_ROLE      MLOG$_TBL_TSCL_ACTIVITYDEF

TBL_TSCL_COMMTICKET            MLOG$_TBL_TSCL_COMMTICKET

TBL_TSCL_DEAL                  MLOG$_TBL_TSCL_DEAL

TBL_TSCL_DEALLOG               MLOG$_TBL_TSCL_DEALLOG

TBL_TSCL_DEAL_H                MLOG$_TBL_TSCL_DEAL_H

TBL_TSCL_ROLECLASS             MLOG$_TBL_TSCL_ROLECLASS

TBL_TSCL_TICKET                MLOG$_TBL_TSCL_TICKET

TBL_TSCL_TIDEALINFO            MLOG$_TBL_TSCL_TIDEALINFO

TBL_TYGZ_AFFIRM                MLOG$_TBL_TYGZ_AFFIRM

MASTER                         LOG_TABLE

------------------------------ ------------------------------

TBL_TYGZ_AFFIRM_C              MLOG$_TBL_TYGZ_AFFIRM_C

TBL_TYGZ_AUDITINGINFO          MLOG$_TBL_TYGZ_AUDITINGINF

TBL_TYGZ_AUDITINGINFO_C        MLOG$_TBL_TYGZ_AUDITINGINF1

TBL_TYGZ_REPLY                 MLOG$_TBL_TYGZ_REPLY

TBL_TYGZ_REPLY_C               MLOG$_TBL_TYGZ_REPLY_C

TBL_TYGZ_SIGNINFO              MLOG$_TBL_TYGZ_SIGNINFO

TBL_TYGZ_SIGNINFO_C            MLOG$_TBL_TYGZ_SIGNINFO_C

TBL_TYGZ_SQ_INFO               MLOG$_TBL_TYGZ_SQ_INFO

TBL_TYGZ_SQ_INFO_C             MLOG$_TBL_TYGZ_SQ_INFO_C

TBL_TYGZ_TICKETINFO            MLOG$_TBL_TYGZ_TICKETINFO

TBL_TYGZ_TICKETINFO_C          MLOG$_TBL_TYGZ_TICKETINFO_

MASTER                         LOG_TABLE

------------------------------ ------------------------------

TBL_WLSJGL_COMMTICKET          MLOG$_TBL_WLSJGL_COMMTICKE

TBL_WLSJGL_DEAL                MLOG$_TBL_WLSJGL_DEAL

TBL_WLSJGL_SUB_TASKINFO        MLOG$_TBL_WLSJGL_SUB_TASKI

TBL_WLSJGL_TICKET              MLOG$_TBL_WLSJGL_TICKET

37 rows selected.

SQL> select * from dba_objects where OBJECT_NAME like '%MLOG$_TBL_COMM_COMMONTICKE%';/*查看是否存在真正的DBA对象*/

OWNER

------------------------------

OBJECT_NAME

--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

------------------------------ ---------- -------------- -------------------

CREATED      LAST_DDL_TIM TIMESTAMP           STATUS  T G S

------------ ------------ ------------------- ------- - - -

EOS52

MLOG$_TBL_COMM_COMMONTICKE1

                                    78204          78204 TABLE

28-APR-09    28-APR-09    2009-04-28:18:03:12 VALID   N N N

SQL> 

SQL> 

SQL> 

SQL> 

SQL> conn eos52/eos52

Connected.

SQL> drop materialized view log on TBL_COMM_COMMONTICKET;/*到所在用户下，丢弃物化视图日志*/

Materialized view log dropped.

SQL> 

SQL> rename TBL_COMM_COMMONTICKET to TBL_COMM_COMMONTICKET_B1; /*即可重命名表*/

Table renamed.

SQL>