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

背景：

系统组兄弟相对一个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

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> 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>