Re: Simple Exp/Imp Question

Answers embedded.
Please also refer to the Oracle server utilities manual.

Regards,

Sybrand Bakker, Oracle DBA

"Kenny Lim" <kennylim_at_techie.net> wrote in message news:Ds3q5.265$p5.8765_at_newsread03.prod.itd.earthlink.net...
>
> Hi All,
>
> We currently have a simple test server running on a single drive.
>
> We got a new Compaq server with 3 drive (RAID 5, 3+1 Parity) and would
 like
> to perform a full migration from the old to the new server.
> (Oracle 8.15 to Oracle 8.16 )
>
> (1) The source drive is d:/ when performing a full export and the target
> drive
> will be e:/ when performing a full import. Is there a parameter that I can
> specify
> to change the target path to e:/ instead of defaulting to d:/ when using
> Oracle imp/exp
> utilities ?

Both utilities allow you to specify a full pathname with the file= parameter.
>
> (2) Since this is a test server, it comprise of more than 70 users
> sharing about 30 different tablespaces. Is there a parameter that I
> can specify to import the user into a designated new tablespace
> on a new drive path. ie.
>
> (a) Sys or System User to System Tablespace (e:/)
> (b) Non Sys or System user to User Tablespace (f:/)
> (c) All indexes to Index Tablespace (g:/)
>

The user SYS is *never* exported, as each create statement for a non sys user inserts in the datadictionary.
for other users to the following.
Pre-create the user and change the default tablespace of that user (don't ask how, refer to the sql reference manual). Make sure the user doesn't get the unlimited tablespace privileges and/or revoke that privilege. Grant the user unlimited quota on the target tablespace, grant 0 quota on all other tablespaces.
Run an import with indexes=n
run an import with indexfile=<filename>
edit the <filename> above changing the tablespace and run it through sql*plus.

> (3) While performing a full database import, wouldn't the process for the
> system objects be skipped as the system objects already exist on the
 target
> ?
> If so, is there a way to import all user schema avoid the sys and system
> tables during a full import.

The sys user is never exported. The only way to avoid the system user is to import in user mode (fromuser=... touser=...), user by user, no other solution is availble.

>
> Any advise would be greatly appreciated. Thanks in advance and you have a
> pleasant evening.
>
> Kenny-
>
>
>
>
>
Received on Sun Aug 27 2000 - 03:58:33 CDT