How to create copy of full schema on same database in oracle

Question

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:

1. Datapump Over Db link
2. IMPDP on network link
3. EXPDP and then IMPDP.

What would be the advantages and/or drawbacks of each technique?

Solution 1

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.

Solution 2

Try this:

Copy schema into same database

I tested and it worked. but you have to create a public link like mentioned in the comments.

If you create a .par file which remaps your tablespaces you can reuse it. With some search replace of course.

O and don't forget to put the transaform in the .par file:

TRANSFORM=oid:n

Otherwise you might get errors.

Comments

[Matthias]

That worked for me, although I had some struggle with permissions that I eventually resolved by running the commands with a sysdba login - the schemas / remap_schema parameters still tell what to export/import. The directory param can be omitted when the default is ok for you, see orafaq.com/wiki/Datapump#Create_database_directories.

[mmmmmpie]

@Matthias Do not EXP/IMP as SYS. :)

[u123]

In the above example where you run the impdp command is it assumed that you have already created the new 'toUser' schema? Or will the command take care of that?

[Chris Maggiulli]

Interesting but that solution terrifies me

[AJPerez]

the link is broken