Move Tables from One Schema to Another

This is just an example of moving few tables like 1 or 10, for more tables it might be a time consuming process. Here i am considering moving 1 table from one schema to another schema.

Firstly create the DDL from 1 schema using this link: https://getsomeoracle.wordpress.com/2013/11/07/export-oracle-ddl-to-flat-file-using-sql-developer

and then simply change the Schema 2 in the script and create it in another schema.

and now simple transfer the data from 1st schema to 2nd schema using below query:

INSERT INTO schema1.my_table
SELECT * from schema2.my_table;
COMMIT;

Now we have the same table in 2nd Schema. Now we can drop from 1st schema if its required.

Thanks!

Hope it helps.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s