Re: "Import succeded with warnings" and ERRORS!?

I think you've made rather hard for yourself by misunderstanding what Import does when the paths to the data files are different between different databases. Import couldn't give a monkeys: Table A from source tablespace DATA01 will quite happily be created in target tablespace DATA01, regardless of where the datafiles for the target tablespace are physically located.

So, no, Import will not "fail if the paths of the DB Files differ".

Import doesn't even fail if the tablespace names themselves are different: at that point, it creates the tables in the default tablespace of the User doing the import (which is why it's not such a good idea to run import as SYS (which is what "/ as sysdba" does for you), since his default tablespace is invariably going to be SYSTEM).

If you're doing a full import then, yes, all Users should be created for you. You might want to open up the dump file in a text editor and just have a look near the top at the list of Users it proposes to create (but for God's sake don't save it afterwards, since the dump file is not a text file, and saving it as one renders it useless).

You don't want even *think* about dropping the SYSTEM user (I'm pretty sure you won't be able to do so anyway). First he owns things, so a cascade option would be required -and at that point, you'd be dropping a whole raft of objects which are required for the database to function.

I'm intrigued to know how the dump file was produced in the first place, since a full database export would not ordinarily include SYS's schema at all, so quite what all those error messages about 'importing SYS's object' are all about, Lord alone knows.

To investigate further, I'd like to see the complete command line that was used to export the original database, and the command line you are using when running Import.

Regards
HJR

--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================


"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:8tb1ttoqj477n56pfmembms29555t71kvi_at_4ax.com...


> Hello

>

> I got a full dump file from another company and imported it into our

> development DB (8.1.7, Solaris). I did this my first time and it did

> not work at my first try since the objects in the target DB are

> populated, and we need an exact copy of the DB which produced the dump

> file. Just using IGNORE=Y is not enough for our needs. The dump

> corresponds to a newer, more experimental version of the DB than the

> one contained at the target.

>

> I could have created a new, empty instance but the tablespaces are

> needed too because otherwise the import will fail if the paths of the

> DB files differ (they probably do). So I dropped cascade all users but

> SYS and SYSTEM in the target DB and did the import. But I got hundreds

> of errors along a final notice that "the import succeded with

> warnings". Now I am quite confused.

>

> First of all, I was warned that the exporting and importing user do

> not match (I imported as user / as sysdba).

>

> Then lots of errors occured because users MDSYS and CTXSYS weren't

> found. Well, I thought there were supposed to be created!!

>

> It seems that constraints are violated, views or roles or users do not

> exists (weren't they supposed to be created?). Perhaps I should have

> dropped the SYSTEM user too, not sure.

>

> I never thought that achieving the right conditions for an import

> would be so hard. Of course, I could have created the tablespaces on a

> new instance, but how the hell I know which tablespaces are going to

> be needed, and how they were created in the originating DB?

>

> What is the standard procedure in order to import a dump file when the

> paths of the datafiles on the origin and on the target differ?

> (Because if they didn't, I could use a clean instance).

>

> See more errors below (exerpts):

>

> Import SYS's Objects in SYS

> IMP-00017: Following commands did not succeed because Oracle error

> 4042

>  "BEGIN  "

>  "SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON

> DBMS_DEFER_QUERY TO DBA');"

>  "COMMIT; END;"

> IMP-00003: Oracle error 4042 found

> ORA-04042: Procedure, function, package or ... does not exist

>  "SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_REPCAT TO

> SYSTEM"

> ORA-06512: in "SYS.DBMS_SYS_SQL", line 824

> ORA-06512: in "SYS.DBMS_SQL", line 32

> ORA-06512: in "SYS.DBMS_UTILITY", line 409

>  "SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON

> DBMS_INTERNAL_REPCAT TO EXECUTE_CATALOG_ROLE');"

>  "SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON

> DBMS_RECTIFIER_DIFF TO EXECUTE_CATALOG_ROLE');"

> PLS-00302: Component 'QUEUE_IMPORT_CHECK' must be declared

> Oracle Error 6550  "BEGIN

> SYS.DBMS_DEFER_IMPORT_INTERNAL.QUEUE_IMPORT_CHECK('METATEST.US.ORAC"

>  "LE.COM','IBMPC/WIN_NT-8.1.0'); END;"

> IMP-00019: line rejected  because Oracle Error 1

> IMP-00003: Oracle Error 1 found

> ORA-00001: Violation of uniqueness rule (SYSTEM.HELP_TOPIC_SEQ)

> IMP-00019: line rejected  because Oracle Error 1

> IMP-00003: Oracle Error 1 found

> ORA-00001: Violation of uniqueness rule (SYSTEM.PBSYSPBE_IDX)

> ... rule SYSTEM.PBSYSCATFRMTS_IDX)

> ... rule (SYSTEM.REPCAT$_AUDIT_ATTRIBUTE_PK)

> Object compiled with warnings...

> IMP-00017: Following command failed due to Oracle Error 942:

>  "GRANT DELETE ON "ALIQUOT_VIEW" TO "PIMS_USER""

> IMP-00003: Oracle Error 942 found

> ORA-00942: Table or view does not exist

>

> Thanks a lot in advance

>

> Rick