Re: Import data in an other schema

this can be done provided that the dest user has unlimited tablespace revoked ( or via role) and has a quoto of 0k on the original tablespace and the desired tablespace is the default e.g.

alter user destuser
quota 0k on orig_ts

quota unlimited on dest_ts
quota unlimited on dest_idx_ts
default tablespace dest_ts;

You would then need to run imp with the indexes=n option. This will put the tables and data into the default tablespace as specified above. Then run imp with the indexfile=dest_idx.tmp to get a list of indexes. Then run some awk on the file to change the tablespace name and finally run the index file as the dest user.

eg

imp dest/dest file=orig.dmp fromuser=orig touser=dest ignore=y commit=y indexes=n
imp dest/dest file=orig.dmp fromuser=orig touser=dest ignore=y commit=y indexfile=dest_idx.tmp

awk '/ORIG_TS/ { sub("ORIG_TS","DEST_TS) } \

        /CONNECT/ { sub("CONNECT","") } { print $0 }' dest_idx.tmp > dest_idx.sql

sqlplus dest/dest @dest_idx.sql

That should do it fine.
Dan

Michel Dupont <mdupont_at_caramail.com> wrote in message news:37C3E5F0.59E40D46_at_caramail.com...
> We have an instance et need to export data to another schema
> we export all user objects of the instance and make
> imp dest/dest fromuser =
> but all data are in the same tablespace (data and indexes) and we need
> different for each like in the original schema.
>
> Can we make import with data in data tablespace of the new schema
> and indexes in the index tablespace
>
> Thanks
>
Received on Wed Aug 25 1999 - 17:13:32 CDT