How to create copy of full schema on same database in oracle
I want to create full copy of the schema on the same database. What is the best technique to do that?
I have few ideas in mind:
- Datapump Over Db link
- IMPDP on network link
- EXPDP and then IMPDP.
What would be the advantages and/or drawbacks of each technique?
Answer
You don't need a fancy network link or anything just the expdp/impdp commands. First export the schema that you want:
expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt
Tip: make sure that that user has the ability to write to DUMPDIR or whatever it is you call it
Then reimport the schema using the remap_schema parameter:
impdp toUser/toPword directory=DUMPDIR dumpfile=dump.dmp logfile=implog.txt remap_schema=fromUser:toUser
If you really want to use the network_link parameter (so you don't create a dumpfile for some reason) then I recommend reading this or this.
Great info on your question is found here also.