oracle-04045,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 脚本

另外还有两种编译无效对象的方式：

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.

About UTLIRP.SQL

===============

+ What is UTLIRP.SQL ?

This is a SQL script which Invalidates PL/SQL modules viz.,procedures, functions, packages, types, triggers, views in a database This script expects standard.sql, dbmsstdx.sql files to be available in the current directory.

There should be no other DDL on the database while running the script.

+ What UTLIRP.SQL does?

In 9i:

The utlirp.sql script recompiles existing PL/SQL modules in the FORMAT required by the NEW DATABASE with the below steps:

A. This script first alters certain dictionary tables.

B. Then, it reloads package STANDARD and DBMS_STANDARD,which are necessary for using PL/SQL.

C. Finally, it triggers a recompile of all PL/SQL modules, such as packages,procedures, types, and so on. Primarily used for WORD-SIZE conversion

In 10g and higher：

The utlirp.sql script recompiles existing PL/SQL modules in the FORMAT required by the NEW DATABASE with the below steps:

A. This script first alters certain dictionary tables.

B. Then, it reloads package STANDARD and DBMS_STANDARD, which are necessary for using PL/SQL.  All DDL Triggers are compiled at this time as well.

C. Unlike in 9i, the database must be restarted and UTLRP.SQL run to recompile the remaining invalid objects.

+ Any other script called within UTLIRP.SQL?

Yes. It invokes utlip.sql and utlrp.sql

+ When it is best to run UTLIRP.SQL?

A) Must during WORD-SIZE conversion where it first Invalidates PLSQL modules and then recompiles PLSQL modules in the FORMAT required by the NEW DATABASE. This situation makes sense because an action was taken that caused the old code's format to be inconsistent with what it's supposed to be. e.g., when migrating a 32 bit database to a 64 bit database or vice-versa

B) Can also be run whenever UTLRP.SQL needs to be run to validate invalid objects as this script only invalidates and then validates all the PLSQL modules.

About UTLIP.SQL

==============

+ What is UTLIP.SQL

This script Invalidates PL/SQL modules. This script can be used to invalidate all existing PL/SQL modules (i.e. procedures, functions, packages, types, triggers, views) in a database so that they will be FORCED to be RECOMPILED LATER on either automatically or deliberately. Run this script connected as SYSDBA from SQL*PLUS. This script expects the following files to be available in the current directory: standard.sql, dbmsstdx.sql, dbmssql.sql, prvtssql.plb, prvtsql.plb, plitblm.sql

There should be no other DDL on the database while running the script.

+ What UTLIP.SQL does?

This script regenerates the compiled code because an action was taken that caused the old code's format to be inconsistent with what it's supposed to be. e.g., when migrating a 32 bit database to a 64 bit database or vice-versa. THIS IS THE ACTUAL SCRIPT WHICH DOES THE REGENERATION OF COMPILED CODE. The actual series of steps can be checked reading the script which has a few step by step operations

+ Any other script called within UTLIP.SQL?

Yes. It calls standard, dbmsstdx

+ When it is best to run UTLIP.SQL?

The best would be during WORD-SIZE conversion from 32bit to 64bit and vice-versa.

Where are these SCRIPTS located?

============================

The scripts are found in the standard admin directory of the Oracle Home.

$ORACLE_HOME/rdbms/admin

Which script is better?

=================

-> UTLIP.SQL - This is the actual script which does the regeneration of compiled code.

-> UTLRP.SQL - Validates all invalid objects and can be run anytime. (this in-turn is called by utlirp.sql)

-> UTLIRP.SQL - Simply calls utlip.sql and utlrp.sql(9i only) which MUST be run during WORD-SIZE conversion (If utlip.sql is not run manually) and during normal validating of invalid objects. (if utlrp.sql is not run)

Summary

========

The best would be to run UTLIRP.SQL which would do both regeneration of compiled code of PL/SQL modules (which UTLIP.SQL does) and then Recompiles all Invalid PL/SQL modules again. (which UTLRP.SQL does)