Home » RDBMS Server » Server Utilities » ORA-31684 and ORA-39082 while exp/imp
ORA-31684 and ORA-39082 while exp/imp [message #569538] Mon, 29 October 2012 05:59 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,

i want to import scott schema in a already created TEST user but it gives 3 errors.

C:\Users\Neetesh>expdp system/Prodb@prodb schemas=scott dumpfile=scott.dmp grants=n

Export: Release 11.2.0.1.0 - Production on Mon Oct 29 16:03:36 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "grants=FALSE" Location: Command Line, Replaced with: "ex
clude=grant"
Legacy Mode has set reuse_dumpfiles=true parameter.
Legacy Mode has set nologfile=true parameter.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@prodb schemas=scott d
umpfile=scott.dmp exclude=grant reuse_dumpfiles=true nologfile=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
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
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  C:\ADMIN\PRODB\DPDUMP\SCOTT.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:04:02

C:\Users\Neetesh>impdp system/testdb@testdb schemas=scott dumpfile=scott.dmp remap_schema=scott:test

Import: Release 11.2.0.1.0 - Production on Mon Oct 29 16:06:11 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02":  system/********@testdb schemas=scott d
umpfile=scott.dmp remap_schema=scott:test
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT"                               5.937 KB       4 rows
. . imported "TEST"."EMP"                                8.570 KB      14 rows
. . imported "TEST"."SALGRADE"                           5.867 KB       5 rows
. . imported "TEST"."BONUS"                                  0 KB       0 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/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"TEST"."PROC_DEPT" created with compilati
on warnings
ORA-39082: Object type ALTER_PROCEDURE:"TEST"."PROC_EMP" created with compilatio
n warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 3 error(s) at 16:06:16


errors are- (1) one error is of that the TEST user is already exists,
then how to import a schema in already exists user?

(2) 2 errors are because of procedures are created with compilation error.
and i compiled them then i found that the these procedures throws errors because of public sysnonyms.
then how can we include public synonym in export file?

thanks in advance........

[Updated on: Mon, 29 October 2012 06:06]

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569548 is a reply to message #569538] Mon, 29 October 2012 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

(1) You can just ignore this "error" (I think as it as a warning not an error).

(2) You cannot export PUBLIC SYNONYM on schema mode export. Use a (independent) SQL script to do this, using dbms_metadata or querying all_synonyms

Regards
Michel

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569561 is a reply to message #569548] Mon, 29 October 2012 09:53 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
ok thanks alot sir,
finally we can create a script of DDL statements for the creation of synonyms and run it independently.
and can we ignore the error message from impdp script?

thanks again....

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569563 is a reply to message #569561] Mon, 29 October 2012 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Before import run the script to create the private synonyms, then the remaining errors may be important.

Regards
Michel

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569784 is a reply to message #569563] Thu, 01 November 2012 09:00 Go to previous messageGo to next message
sudheergodgeri
Messages: 7
Registered: December 2010
Location: Mumbai, India
Junior Member
Dear Neetesh,

You need to avoid:
1) using public synonyms if you are using multiple schemas as there may be a mismatch i.e. EMP public synonym may point to emp table in
scott schema, which is ok, but u might export it along with RAMESH schema, which also has EMP table - which may be of different structure.
This will lead to errors during IMPORT only, as its ok in export db.

2) for best complete schema export/import - don't use SYSTEM id to export - use the source username to export - as this will make sure u
don't pull stuff that is not valid for the schema you are exporting.

3) routine compilation errors are normal during import across db's - just run dbms_utility.compile_schema('schemaName') where schema name
is the name of target user/schema u are importing into. Do this repeatedly till your errors go off - if they don't u are referring to
some other schema in your function/procedure/package -- u need to check it out.
use the user_dependencies view to find the details of the same.


Regards,
SudheerGodgeri

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569790 is a reply to message #569784] Thu, 01 November 2012 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
2) for best complete schema export/import - don't use SYSTEM id to export - use the source username to export - as this will make sure u
don't pull stuff that is not valid for the schema you are exporting.


What does that mean? Which invalid stuff can you export using SYSTEM and schema mode?

Quote:
3) routine compilation errors are normal during import across db's - just run dbms_utility.compile_schema('schemaName') where schema name
is the name of target user/schema u are importing into.


And here we know the problem: public sysnonyms. How this part is relevant to the question?

Regards
Michel

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569872 is a reply to message #569790] Fri, 02 November 2012 11:49 Go to previous messageGo to next message
sudheergodgeri
Messages: 7
Registered: December 2010
Location: Mumbai, India
Junior Member
Dear Michel,

1. I assumed that in most development setups, developers inadvertently create objects/public synonyms in SYS/ SYSTEM schemas, often create objects there due to lack
of strick policy about distinct tablespaces for distinct object types, so exporting
as owner ( non SYS/SYSTEM) is the best way to find/purge this dependency.

2. The 31684 - user/object already exists error - will b thrown as you would
normally create the user before importing into it, it's good practice to
create distinct physical layouts rather than have oracle create it .

The 39083 errors - usually either compilation warnings / some dependency on
out of source schema objects - which you must know - you will not if u use
SYS/SYSTEM to export/import as when you import back into different schema in
the same database - you will not get errors due to wrong ("invalid/unplanned")
for dependencies in the target schema, when you import as that schema owner
itself.

Regards,
Sudheer Godgeri.
sudheer.godgeri@rediffmail.com

Report message to a moderator

Re: ORA-31684 and ORA-39082 while exp/imp [message #569874 is a reply to message #569872] Fri, 02 November 2012 12:18 Go to previous message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. I would not rely on export/import to detect these errors. Compliance tools should be there to verify. Anyway, I wonder how a developer could create any object in SYS or SYSTEM schema or any schema as developers do NOT create objects, designers describe the object which should be created by DBA-like account NOT by developers.

2. Why should I create the user before importing? When I create the database objects of an application, why should I not use an import that also creates the important accounts for the application (those that are used to manage the application objects, including users)?

I don't understand you last paragraph. Post an example.

Regards
Michel

Report message to a moderator

Previous Topic: SQLLDR connection error
Next Topic: What is LKFILE in oracle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu May 15 02:45:07 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.