how to import data only for a user in database

[m2001331]

hi,
i have two db instances -both identical to one another with different SIDs and i have similar users in both instances, what i'd like toi do is to be able to take export file from db1 and dump it into db2 without having to drop and recreate the user in db2.i have user acc1 in both db1 and db2.db1 is in the production server while db2 is in the development server, sometimes i need to dump production data into development for troubleshooting purpose.during the import process i am unable to import as the user acc1 already exist in db2.i have always been dropping and recreating the user before importing the export file from db1.
i believe there should be a method where i can only import data for all the tables in db1 into db2.please advise.
i am running oracle 9i on linux.
thank you.

 

[SantaMufasa]

M,

I am not certain that I understand your scenario, due, by and large, to possible issues with nomenclature that appears in your thread, but I'll do my best.

First, my presumptions:

1) You wish to recreate the contents of an Oracle user's schema from one database instance (SID) to another Oracle user's schema on another database instance (SID).

2) Following the import into your development database instance, you want the two Oracle user/schemas to be identical.

For all of this to occur, the target Oracle user in the target development database instance must exist, but it should be empty. If the target Oracle user/schema exists in the target Oracle development database, but it contains objects already, and if I am a DBA, then the quickest method of which I am aware to "empty out" the existing target schema is to issue a "DROP USER <target user> CASADE;" followed by a "CREATE USER <target user>..." command.

Then, to import just the source user's contents into the target user, I would issue (from your o/s command prompt) the following command-line (non-interactive) invocation of the Oracle's "imp" command:

imp buffer=15000000 grants=n feedback=1000 fromuser=<source user> touser=<target user> file=<dump-file name> userid=<dba-user>/<dba-password>@<target SID alias>

This command must appear on one physical line at your operating-system prompt. Replace all "<...>" entries with values appropriate for your situation.

Does this answer your questions?


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.