copy schema and data into new schema in a different existing db instance

Toad for Oracle
1

Hi -

From an existing schema, what is a quick way to create the same schema with a different name in a different database instance? And also copy the data from the existing schema into the new schema?

Please advise. Thanks, in advance.

2

Hi there, (solution below)
Due to a transition, our Dev team can’t easily see this content but monitors our Yahoo group daily, along with novice and expert users: http://tech.groups.yahoo.com/group/toad/

Would this work (from Help)?
"To copy objects to another schema:

  • On the standard toolbar, click Database | Schema Browser.
  • Right-click the object you want to copy in the left-hand side and select Create in another schema.
  • Select export settings and click OK. (See Export DDL for more information.)
  • Enter the destination connection and destination schemas.
    To review the script to create the objects, click the Script tab.
  • Click Execute.

Hope this helps, and thanks for using Toad for Oracle!

3

This helped me get the new schema defined, and with a different name.

How do I import data into this new name schema, from an export from the “source” schema? using Toad

I think i have to use REMAP_xxxx in the parameter file.

Message was edited by: 8ball

4

Hey guys,

It’s a nice and handy feature to use, BUT I faced “Out of memory” issue when tried to copy several large objects to new schema. The reason is that Toad caches all the scripts into RAM and when object is too large it just stucks.

Any idea on how this can be bypassed?

Regards,

Michael.

5

Michael Z - what version of Toad are you running?

6

Can this be done in script (instead of navigating),

if I want to run it as a part of big script in the task scheduler?

7

Parag121 - you can use Toad’s automation designer to

generate a schema script

run it in the target schema

Do a “copy table data” action after that.

But it would probably be faster to do all of this in one step with a Data Pump Import action. If you make a database link from the target database to the source database, datapump can pull the source schema through the DB Link and import into the target database all in one motion. The database link is chosen on the “Params” tab of Toad’s datapump import window.