Oracle ORA-04045 errors during recompilation/revalidation

Oracle ORA-04045 errors during recompilation/revalidation

系统环境：centos release 5.10 - 64 bit
 
应用环境：oracle 10.2.0.5

错误摘要：
 
[oracle@hz_cpm50 ~]$ impdp / DIRECTORY=move dumpfile=new.dmp logfile=new.log table_exists_action=APPEND CONTENT=data_only
Import: Release 10.2.0.5.0 - 64bit Production on Monday, 24 March, 2014 14:15:35
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
 
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
Master table "POPUPUSER"."SYS_IMPORT_FULL_04" successfully loaded/unloaded
 
Starting "POPUPUSER"."SYS_IMPORT_FULL_04": popupuser/******** DIRECTORY=new dumpfile=new.dmp logfile=new.log table_exists_action=APPEND CONTENT=data_only
 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 
ORA-31693: Table data object "POPUPUSER"."STATIC_AGG_PROVINCE_DAY":"PART_AGGPROV_20140320"."SYS_SUBP662001" failed to load/unload and is being skipped due to error:
 
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_NETWORK_DROP_USER
 
ORA-01031: insufficient privileges
 
ORA-31693: Table data object "POPUPUSER"."STATIC_AGG_PROVINCE_DAY":"PART_AGGPROV_20140320"."SYS_SUBP662002" failed to load/unload and is being skipped due to error:
 
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_NETWORK_DROP_USER
 
ORA-01031: insufficient privileges
 
ORA-06512: at "SYS.KUPD$DATA", line 1779
 
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_NETWORK_DROP_USER
 
ORA-01031: insufficient privileges
 
ORA-31693: Table data object "POPUPUSER"."STATIC_AGG_PROVINCE_DAY":"PART_AGGPROV_20140320"."SYS_SUBP662003" failed to load/unload and is being skipped due to error:
 
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_NETWORK_DROP_USER
 
ORA-01031: insufficient privileges
 
ORA-06512: at "SYS.KUPD$DATA", line 1779
 
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_NETWORK_DROP_USER
 
ORA-01031: insufficient privileges
 
ORA-31693: Table data object "POPUPUSER"."STATIC_AGG_PROVINCE_DAY":"PART_AGGPROV_20140320"."SYS_SUBP662004" failed to load/unload and is being skipped due to error:
 
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_NETWORK_DROP_USER
 
ORA-01031: insufficient privileges
 
ORA-06512: at "SYS.KUPD$DATA", line 1779

错误原由：
 
  由10.2.0.1升级到10.2.0.5，upgrade mode运行utlirp.sql而没有重新刷新对象导致：
 

DOC>#######################################################################
 
DOC>#######################################################################
 
DOC>  utlirp.sql completed successfully. All PL/SQL objects in the
 
DOC>  database have been invalidated.
 
DOC>
 
DOC>  Shut down and restart the database in normal mode and run utlrp.sql to
 
DOC>  recompile invalid objects.
 
DOC>#######################################################################
 
DOC>#######################################################################

解决方法：
 
  sysdba执行ORACLE_HOME/rdbms/admin/utlrp.sql 脚本

醒文

文字排版美化生图工具

131

查看详情

  另外还有两种编译无效对象的方式： 
 
      1 使用alter **** compile 语句进行编译 
 
      2 用DBMS_UTILITY包来进行编译.

相关介绍：

====================================================================
 
=====What is the Difference between UTLRP.SQL, UTLIRP.SQL and UTLIP.SQL?====
 
====================================================================

About UTLRP.SQL
 ===============
 
+ What is UTLRP.SQL?
 This script recompiles invalid PLSQL modules.
 NOTE: This script DOES NOT Invalidate any PLSQL modules like utlirp.sql and utlip.sql
 
+ What this script does?
 This script recompiles all existing invalid PL/SQL modules in a database.
 This is a fairly general script that can be used at any time to recompile all existing invalid PL/SQL modules in a database If run as one of the last steps during migration/upgrade/downgrade this script will validate all PL/SQL modules (i.e. procedures, functions, packages, triggers, types, views, libraries) during the migration step itself.
 
+ Any other script called within UTLRP.SQL?
 Yes. It invokes utlrcmp.sql
 
+ When it is best to run UTLRP.SQL?
 Although invalid PL/SQL modules get automatically recompiled on use, it is useful to run this script ahead of time (e.g. as one of the last steps in your migration), since this will either eliminate or minimize subsequent latencies caused due to on-demand automatic recompilation at runtime. Oracle highly recommends running this script towards the end of any database migration/upgrade/downgrade.