Re: import full (8i ->10g) and system schema, what really happens ?

On Sat, 16 Jun 2007 19:46:01 +0200, "KCS" <KCS_at_hotmail.it> wrote:

>FIRST: Import full is possible from 8.1.7.4 to 10g R2 (see metalink), the
>matrix is:
>Migration to Oracle10g release 2 - 10.2.0.x :
> - Oracle8i : 8.1.7.4
> - Oracle9i : 9.0.1.4 (or higher) or 9.2.0.4 (or higher)
> - Oracle10g: 10.1.0.2 (or higher)
>
>Now the question is: what happens to system user in my 10g database ?
>
>I've read many articles in metalink and not, one of the most interesting
>(from oracle) is this (the note specified in it is not public):
>
>From: Oracle, Edward Murphy 16-Sep-03 20:01
>Subject: Re : Recreate user SYSTEM
>Hi Scott,
>The user SYSTEM is created at database creation time, just like the user
>SYS. The difference is that SYSTEM is created with no schema objects and
>remains empty unless replication or advanced queueing objects are created by
>the corresponding cat.... scripts.
>Another difference from SYS is that the export/import utilities will
>access the SYSTEM schema when it is specified (or FULL=Y).
>We do not support dropping the SYSTEM user and therefore do not have a
>corresponding creation script for it (short of re-creating the database or
>restoring a backup to a point in time before the drop).
>That being said if you access our Technical Libraries on MetaLink and look
>up Note 101068.1 you will find an UNSUPPORTED method of creating a new
>SYSTEM user. Afterward you should still run a full export and recreate the
>database.
>Regards,
>Ed
>
>So we can say that:
>1) System must exist
>2) with ignore=y I will avoid creation statement error
>
>BUT what about rows inserted in existing table of the system schema in the
>destination db ?

Tables have primary keys, haven't they?
>
>May I reset the situation with a CATALOG.sql + CATPROC.sql after the import
>full was ended ?

If you have too much time on your hands, by all means do. Run catalog.sql and catproc.sql everyday to 'reset the situation' ;)
>Tell me what do you think about this.
>Thank you.
>

-- 
Sybrand Bakker
Senior Oracle DBA