Re: Copying tables form ONE Schema to Another SChema in same instance

On 4 Oct 2004 13:29:30 -0700, hari_om_at_hotmail.com (HARI OM) wrote:

>SUBJECT: Copying tables form ONE Schema to Another SChema in same
>instance
>
>I am using Oracle 9.2.0.2 on IBM AIX 5.1L System.
>I have a instance names "KHAR" and have 2 SCHEMAS in it namely: "DIN"
>and "MUK".
>
>Schema DIN is in Tablespace DIN and
>Schema MUK is in Tablespace MUK.
>
>I have around 200 populated tables in "Schema DIN" and I took FULL
>Export of this User "DIN" - "din.dmp" last week.
>
>Now, I would like to replicate or copy these DIN Schema Tables to MUK
>Schema's. How can I do that.
>
>I tried doing IMPORT:
>./imp system/***@khar fromuser=DIN touser=MUK
>and enter the export file as "din.dmp"
>
>It did create these tables under Schema MUK. However, it created it
>under DIN Tablespace and not under MUK Tablespace.....
>
>what is the better way of doing this? what am I doing wrong?
>
>THANKS!
>
>HARI OM
Classical (documented) mistake:
Your user MUK has the RESOURCE role and/or UNLIMITED TABLESPACE privilege.
Imp will always use the original tablespace, unless the tablespace doesn't exist, or the new schema owner doesn't have QUOTA on that tablespace. In that case it will use the default tablespace of the new schema owner.

Solution:
revoke unlimited tablespace from muk;
alter user muk quota unlimited on muk quota 0 on din default tablespace muk;

If you don't have a separate index tablespace, you can start your import.
If you do have a separate index tablespace, import with INDEXES=N Then run imp ... INDEXFILE=

and edit the create index statements in the resulting file and apply it to the MUK user.

BTW, I posted this answer numerous times, please use Google before posting.

--
Sybrand Bakker, Senior Oracle DBA