ORA-39082 "..CREATE WITH COMPILATION WARNINGS"WHILE IMPORTING

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

. . imported "CLEPROD"."SCM_CORRECT_RESPONSE_SECOND"                 0 KB       0 rows

. . imported "CLEPROD"."SCM_GLOBAL_OBJECTIVE"                                        0 KB       0 rows

. . imported "CLEPROD"."SCM_GLOBAL_OBJ_PROGRESS_INFO"               0 KB       0 rows

. . imported "CLEPROD"."SCM_INTERACTION"                                                    0 KB       0 rows

. . imported "CLEPROD"."SCM_LEARNER_RESPONSE"                                     0 KB       0 rows

. . imported "CLEPROD"."SCM_LEARNER_RESPONSE_FIRST"                       0 KB       0 rows

. . imported "CLEPROD"."SCM_OBJECTIVE_PROGRESS_INFO"                    0 KB       0 rows

ORA-39082: Object type TRIGGER:"CLEPROD"."BEF_INS_ENTITYPROPS" created with compilation warnings

ORA-39082: Object type TRIGGER:"CLEPROD"."BEF_INS_ENTITYPROPS" created with compilation warnings

ORA-39082: Object type TRIGGER:"CLEPROD"."BEF_INS_ENTITYTAGAPPS" created with compilation warnings

ORA-39082: Object type TRIGGER:"CLEPROD"."BEF_INS_ENTITYTAGAPPS" created with compilation warnings

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ

Job "CLEPROD"."SYS_IMPORT_SCHEMA_01" completed with 6 error(s) at 08:39:47

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

3.)              SQL> ALTER PACKAGE <SchemaName>.<PackageName> COMPILE;

4.)              SQL> ALTER PACKAGE <SchemaName>.<PackageName> COMPILE BODY;

5.)              SQL> ALTER PROCEDURE my_procedure COMPILE;

6.)              SQL> ALTER FUNCTION my_function COMPILE;

7.)              SQL> ALTER TRIGGER my_trigger COMPILE;

8.)              SQL> ALTER VIEW my_view COMPILE;

9.)              SQL> EXEC DBMS_UTILITY.compile_schema(schema => 'cleprod')

10.)         SQL> EXEC UTL_RECOMP.recomp_serial('cleprod') ;

BASH: SQPLUS COMMAND NOT FOUND

bash: sqlplus: command not found

Oracle S/W is installed on Unix server, but  below error message is there.

[oracle@~]$ sqlplus

-bash: sqlplus: command not found

Solution:

Go to ORACLE_HOME (Path oracle is installed)

[oracle@~]$ cd /opt/oracle/product/11.2.0/

[oracle@ dbhome_1]$ export ORACLE_HOME=/opt/oracle/product/11.2.0

[oracle@dbhome_1]$ cd bin/

[oracle@ bin]$ ./sqlplus

 SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 20:53:19 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

To   fix this add following entries into .bash_profile file at /home/oracle location.

Export ORACLE_HOME=/opt/oracle/product/11.2.0

Export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

ORA-01919:

ORA-01919:

This example, tested on Oracle 10g , demonstrates the ORA-01919 error message, which some people find misleading. First create a user called ANAND, a role called newrole and another user called ANOTHER_USER

SQL> conn / as sysdba

Connected.

SQL> grant create session to anand

  2  identified by anand

  3  /

Grant succeeded.

SQL> create role newrole

  2  /

Role created.

SQL> create user another_user

  2  identified by another_user

  3  /

User created.

SQL>

Connect as ANAND then try to grant role NEWROLE to ANOTHER_USER. This fails but the error message is not helpful. Role NEWROLE  does exist but it has not been granted to ANAND yet:

SQL> conn anand/anand

Connected.

SQL> grant NEWROLE to another_user

  2  /

grant newrole to another_user

*

ERROR at line 1:

ORA-01919: role 'newrole' does not exist

SQL>

Grant the role NEWROLE to ANAND:

SQL> conn / as sysdba

Connected.

SQL> grant newrole to anand

  2  /

Grant succeeded.

SQL>

Then login as ANAND and try to grant NEWROLE to ANOTHER_USER again. This time the error message gives a better explanation:

SQL> conn anand/anand

Connected.

SQL> grant newrole to another_user

  2  /

grant blah to another_user

*

ERROR at line 1:

ORA-01932: ADMIN option not granted for role 'NEWROLE’

SQL>

Now grant the role to ANAND with the ADMIN option:

SQL> conn / as sysdba

Connected.

SQL> grant newrole  to anand with admin option

  2  /

Grant succeeded.

SQL>

Login as ANAND again. This time the GRANT statement works:

SQL> conn anand/anand

Connected.

SQL> grant newrole to another_user

  2  /

Grant succeeded.

SQL>

IMPORT THE DATA WITHOUT EXPORTING ,DIRECTLY IMPORTING BY USING IMPDP

1   )   Importing the data from remotely without taking the export by database link :

Steps:

   Remote side :

·         Create a new user name called <prod>

Sql> Create user prod identified by prod;

·         Grant connect, resource ,dba   , imp_full_database,  exp_full_database to user prod;

·         Now log into user which we created

Conn prod/prod

·         Now create a db link

Create database link  <newlink>  connect scott identified by tiger using ‘remotedb’;

·         Now execute the impdb comment

$ ] Impdp prod/prod@testdb network_link=newlink schemas=scott remap_schema=scott:prod

--------------------------------------------------------------------------------------------------------

2)    Exporting data by expdp :

Expdp  username/password  dumpfile=<filename>   logfile=<filename> schemas=<schema name>  directory=<directory name>

For Example:

Expdp  scott/tiger   dumpfile=scott.dmp logfile=scott.log schemas=scott  directory=data

3)    Importing data by impdb:

Impdp username/username dumpfile=<filename> logfile=<filename> remap_schema=<from schema: to schema> directory=<directory name>

            For Example:

          Impdp  prod/prod  dumpfile=scott.dmp logfile=scott1.log remap_schema=scott:prod    schemas=scott