Wednesday, March 28, 2012

Copy Oracle Table from one schema to another

Do you want to copy a table and it's data from one oracle schema to another?

It can be done by using the GRANT command. Grant all/ required privileges on the object(table) and use the Create table command in the second schema.

By doing this table structure and the data will be transferred from 1st schema to 2nd schema.

Example : 

SCHEMA1.TABLE1 is to be moved to SCHEMA2.

Connect to schema1 and execute the following command.
GRANT ALL ON SCHEMA1.TABLE1 TO SCHEMA2


Note - Complete Grant command definition can be found here - 
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9013.htm#SQLRF01603 

Now connect to schema2 and execute the following command.
CREATE TABLE TABLE1 AS SELECT * FROM SCHEMA1.TABLE1;



1 comment:

  1. i want to copy all tables
    without usin import/export what to do.

    ReplyDelete