Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how can I exclude a user from an import

Re: how can I exclude a user from an import

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Fri, 20 Dec 2002 00:47:26 +0100
Message-ID: <pgm40v46mbbi5fplitav5udi0hafjg6euc@4ax.com>


On Thu, 19 Dec 2002 21:30:40 +0100, Frederic Payant <> wrote:

>Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> :
>
>>On Wed, 18 Dec 2002 21:02:55 +0100, Frederic Payant <> wrote:
>>
>>>Hi,
>>>
>>>I want to migrate a database. I've a lot of users in it and I would
>>>prefer to avoid to recreate them individually.
>>>Is there a way to import the content of a full export without
>>>importing sys and system, but creating all other users (and their
>>>objects) ?
>>>
>>>(Oracle 817, Solaris 8)
>>>Regards
>>>Frederic PAYANT - junior DBA ;-)
>>
>>
>>SYS is never exported and never imported, because any DDL will modify
>>the SYS schema.
>>Of course also your SYSTEM schema doesn't contain any tables that you
>>want to preserve, except those generated by Oracle software, which
>>will be safe to import.
>>So actually this should be a non-issue, if you only want to exclude
>>SYSTEM.
>>
>>With importing in usermode (fromuser= followed by touser=) I am afraid
>>you will be forced to pre-create the accounts.
>>You can of course easily create a script to pre-create them
>>simply
>>select 'create user '||username||'identified by values
>>'''||password||''''
>>from dba_users
>>( I leave the default tablespace and temporary tablespace as an
>>excercise for you)
>>and the same mechanism you can use to generate a series of
>>imp <username/password> fromuser=<username> touser=<username>
>>
>>Unless of course you want to use the OEM point and shoot method, which
>>will definitely end up in a much slower import.
>>
>>Hth
>>
>>
>>Sybrand Bakker, Senior Oracle DBA
>>
>>To reply remove -verwijderdit from my e-mail address
>
>Hi Sybrand,
>Thanks a lot for your answer, it is of a great help.
>I'm asking me 2 more questions. 1 about full import :
>What does happen to Oracle packages and procs when importing from 1
>version to another of Oracle, or when importing to a database whith
>some packages that I don't want to install ?
>Will import create them anyway ?
>The second question is about importing user by user : do I have just
>to recreate users or must I affect them their roles etc...
>
>Thanks again and have a nice day
>Regards
>Frederic PAYANT - junior DBA ;-)

1 import will create them anyway. This can be a problem when you are importing 8.1.6 databases with advancesd replication in 8.1.7 2 Roles are owned by SYS. You'll need to precreate them as well (you can of course get them from imp show=y log=<any file>, editing <any file>)

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Dec 19 2002 - 17:47:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US