Wednesday, November 30, 2011

ORA-39082 ".... created with compilation warnings" while Importing



ORA-39082   generally  occur during  the  import . The error  message  states that  the  object in  the  SQL statement  following  this  error was  created with  compilation errors. If  this  error  occurred  for a  view,  it  is  possible  that  the base  table of  the view  was  missing . Here  is  an scenario  of  ora-39082 error ....

C:\>impdp  system/xxxx@xe  remap_schema=shaik9sep:shaik9sep11g dumpfile=SHAIK9SEP10G.DMP logfile=shaik9sep10g_import.log 

Import: Release 10.2.0.1.0 - Production on Wednesday, 30 November, 2011 14:40:06
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_04" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_04":  system/********@xe  remap_schema=shaik9sep:shaik9sep11g dumpfile=SHAIK9SEP10G.DMP logfile=shaik9sep10g_import.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SHAIK9SEP11G" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SHAIK9SEP11G"."AD_ARCHIVE"                       188.3 MB    1785 rows
. . imported "SHAIK9SEP11G"."T_REPORTSTATEMENT"          54.13 MB    952472 rows
. . imported "SHAIK9SEP11G"."FACT_ACCT"                        23.79 MB   115203 rows
. . imported "SHAIK9SEP11G"."AD_QUERYLOG"                     20.35 MB   78411 rows
. . imported "SHAIK9SEP11G"."T_TRIALBALANCE"                 12.54 MB   55310 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"SHAIK9SEP11G"."DSI_FUNC_PRODUCTREP" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"SHAIK9SEP11G"."INVOICEOPEN" created with compilation warnings
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"SHAIK9SEP11G"."T_INVENTYVALUE_CREATE" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"SHAIK9SEP11G"."AD_SYNCHRONIZE" created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"SHAIK9SEP11G"."M_STORAGE_V" created with compilation warning

All the above error ORA-30082 is a warning . This error occurs due to improper or re-order the sequence of importing the objects or due to the dependency on others objects . For example  , in above case data pump import create procedures before views, if our procedure have dependency on views then we will have the ORA-39082 compilation errors . There are various ways to solve this issues . 

1.)  Run  utlrp.sql  to recompile all invalid objects within the database after the import is complete. This script is in the $ORACLE_HOME\rdbms\admin  directory or alternatively we can use the built-in  DBMS_RECOMP package . This will usually clean up all the invalid objects. utlrp.sql  will compile objects in the database across schemas. In  case of  Re-mapping objects from one schema to another and utlrp.sql  won't be able to compile them .

2.) After the import is completed, recompile the every errors . This is useful when you have few errors. The below command are used to recompile the objects as 

SQL> ALTER PACKAGE <SchemaName>.<PackageName> COMPILE;
SQL> ALTER PACKAGE <SchemaName>.<PackageName> COMPILE BODY;
SQL> ALTER PROCEDURE my_procedure COMPILE;
SQL> ALTER FUNCTION my_function COMPILE;
SQL> ALTER TRIGGER my_trigger COMPILE;
SQL> ALTER VIEW my_view COMPILE;
SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'shaik9sep11g') ; or 
SQL> EXEC UTL_RECOMP.recomp_serial('shaik9sep11g') ;

In case of synonym, we need to recreate the synonym.

3.) Use SQLFILE option from impdp to generate the DDL from the export dump and replace the schema name globally , edit and execute the script from sqlplus. This should resolve most of the errors. If we still have errors, proceed  with utlrp.sql. This is the one of good option to deal with this type of error .

4.) There are Bugs which return similar error during import( i.e, impdp). Check metalink  ORA-39082 When Importing Wrapped Procedures [ID 460267.1]

Check the below link for more info and examples about this errors  : 
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:286816015990



Enjoy   :-) 


5 comments:

Anonymous said...

thanks

Angel Perez said...

I'm creating a linux script to automate import and export tasks, is ther a way to avoid this error, lets say that by passes the compilation and at the end I can you run the utlrp.sl script?

Anonymous said...

Cool stuff Neeraj.

Sandeep Chotalia.
Oracle DBA (IBM AUSTRALIA)

Anonymous said...

Thanks.. It helped

Anonymous said...

Thanks for explanations - great.
You are right: "This error occurs due to improper or re-order the sequence of importing the objects or..."

Can we re-order or controll the order of the sequence during importing objects?