无效对象-WWV_FLOW_HELP
一朋友的数据库刚刚安装完毕,准备投产上线,在例行检查时发现了一个失效对象,如下:
1 2 3 4 5 6 7 |
SYS@ORA11GR2>SELECT owner,object_name,object_type,status FROM dba_objects WHERE status = 'INVALID'; OWNER OBJECT_NAME OBJECT_TYPE STATUS --------------- -------------------- ------------------- ------- PUBLIC WWV_FLOW_HELP SYNONYM INVALID SYS@ORA11GR2> |
通过上面的内容,我们知道,这个失效对象是个public的同义词。既然是失效,理论上一定是同义词的源对象不存在或失效导致的,我们可以通过查看这个同义词的创建脚本来确定源对象是什么。
1 2 3 4 5 6 7 8 9 |
SYS@ORA11GR2>set long 9999 SYS@ORA11GR2>select dbms_metadata.get_ddl('SYNONYM','WWV_FLOW_HELP','PUBLIC') from dual; DBMS_METADATA.GET_DDL('SYNONYM','WWV_FLOW_HELP','PUBLIC') ----------------------------------------------------------------------------------- CREATE OR REPLACE PUBLIC SYNONYM "WWV_FLOW_HELP" FOR "APEX_030200"."WWV_FLOW_HELP" SYS@ORA11GR2> |
可以看到,是APEX_030200下的WWV_FLOW_HELP,那么这个又是什么呢?我们再通过dba_objects看看它的类型
1 2 3 4 5 |
SYS@ORA11GR2>SELECT owner,object_name,object_type,status FROM dba_objects WHERE owner='APEX_030200' and object_name='WWV_FLOW_HELP'; no rows selected SYS@ORA11GR2> |
哦,原来这个对象是不存在的。这也是为什么public synonym无效的原因,那么解决这个问题,只需要把APEX_030200下的WWV_FLOW_HELP创建上即可。
APEX_030200这个用户是干嘛用的?Oracle Application Express(APEX),这个用户应该是用于开发应用程序默认创建的用户,当然,我没用过这个东西,所以没什么发言权,只是在此简单介绍一下。
在$ORACLE_HOME目录下有相关的安装包。
1 2 3 4 |
[oracle@crm ~]$ cd $ORACLE_HOME/apex/core [oracle@crm core]$ ls wwv_flow_help.sql wwv_flow_help.sql [oracle@crm core]$ |
由于APEX_030200用户是锁定的,那么在创建的时候,需要修改一下schema
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[oracle@crm core]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 9 12:17:14 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@ORA11GR2>alter session set current_schema=APEX_030200; Session altered. SYS@ORA11GR2>@wwv_flow_help.sql ...wwv_flow_help Package created. Grant succeeded. No errors. SYS@ORA11GR2> |
查看对象状态
1 2 3 4 5 6 7 |
SYS@ORA11GR2>SELECT owner,object_name,object_type,status FROM dba_objects WHERE owner='APEX_030200' and object_name='WWV_FLOW_HELP'; OWNER OBJECT_NAME OBJECT_TYPE STATUS --------------- -------------------- ------------------- ------- APEX_030200 WWV_FLOW_HELP PACKAGE VALID SYS@ORA11GR2> |
再次查看那个失效的同义词,它依旧失效
1 2 3 4 5 6 7 |
SYS@ORA11GR2>SELECT owner,object_name,object_type,status FROM dba_objects WHERE status = 'INVALID'; OWNER OBJECT_NAME OBJECT_TYPE STATUS --------------- -------------------- ------------------- ------- PUBLIC WWV_FLOW_HELP SYNONYM INVALID SYS@ORA11GR2> |
此时,只需要重新再创建一次那个同义词就可以了,创建完毕,已无失效对象
1 2 3 4 5 6 7 8 9 |
SYS@ORA11GR2>CREATE OR REPLACE PUBLIC SYNONYM "WWV_FLOW_HELP" FOR "APEX_030200"."WWV_FLOW_HELP"; Synonym created. SYS@ORA11GR2>SELECT owner,object_name,object_type,status FROM dba_objects WHERE status = 'INVALID'; no rows selected SYS@ORA11GR2> |
小结:
1)由于那个朋友对数据库不太了解,所以希望完美的编译成功。所以才有以上的操作。
2)我之前给的建议是把这个同义词删掉。或者执行一下@?/rdbms/admin/utlrp.sql这个脚本。这个脚本可以将数据库失效的对象编译一下,虽然APEX_030200的包不存在,重新编译也是能成功的。
3)如果不使用apex,那么可以考虑将其schema彻底删除,如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SYS@ORA11GR2>@?/apex/apxremov.sql ...Removing Application Express old 1: alter session set current_schema = &APPUN new 1: alter session set current_schema = APEX_030200 Session altered. PL/SQL procedure successfully completed. no rows selected old 2: if '&UPGRADE' = '1' then new 2: if '1' = '1' then PL/SQL procedure successfully completed. Session altered. PL/SQL procedure successfully completed. old 1: drop user &APPUN cascade new 1: drop user APEX_030200 cascade User dropped. old 2: if '&UPGRADE' = '1' then new 2: if '1' = '1' then PL/SQL procedure successfully completed. old 5: if '&UPGRADE' = '1' then new 5: if '1' = '1' then PL/SQL procedure successfully completed. old 2: if '&UPGRADE' = '1' then new 2: if '1' = '1' then PL/SQL procedure successfully completed. ...Application Express Removed SYS@ORA11GR2> SYS@ORA11GR2>select OWNER,object_name,object_type from dba_objects where status='INVALID'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------- ------------------- SYS HTMLDB_SYSTEM PACKAGE BODY SYS@ORA11GR2>drop package HTMLDB_SYSTEM; Package dropped. SYS@ORA11GR2> |
删除后,有个无效对象,将其也一并删除即可。