I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.

3 Answers 3


Use expdp and impdp, in combination with the remap_schema option.

For example...

Firstly, make sure the target user/schema is already created. It may also temporarily need the IMP_FULL_DATABASE role, but I haven't tested this.

Create a directory object to hold the dump files and logs (make sure this exists on your filesystem):

create directory DUMPDIR as '/path/to/your/dump/directory';
grant read, write on directory DUMPDIR to system;

Export the data, specifying the schema you wish to export ( FROMUSER in this case):

expdp fromuser/frompass@yourdb schemas=FROMUSER directory=DUMPDIR dumpfile=fromuser.dmp logfile=expdp.log

Import with impdp:

impdp touser/topass@yourdb directory=DUMPDIR dumpfile=fromuser.dmp logfile=impdp.log remap_schema=fromuser:touser

remap_schema=fromuser:touser does the magic!

You can also do this without creating an interim dumpfile by using the network_link= parameter. See the Oracle documentation for more information.

  • +1 the old exp/imp is also good + no need for directory on the server (if you SA is sensitive about that).
    – haki
    Commented Apr 25, 2013 at 18:52
  • I get the following on export: ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation
    – ThaSaleni
    Commented Apr 26, 2013 at 9:13

Have you looked at the DBMS_METADATA option? http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1019414

This gives you the schema for objects. It is fairly easy to take the results of this and do what you want with it. An example to get the tables and views of a user would be:

set serveroutput on;
  theSchema  VARCHAR2(30) := 'SCOTT';
  for theTbl in (select table_name from dba_tables where owner=theSchema) loop
    dbms_output.put_line(dbms_metadata.get_ddl('TABLE', theTbl.table_name, theSchema));
  end loop;
  for theIdx in (select index_name from dba_indexes where owner=theSchema) loop
    dbms_output.put_line(dbms_metadata.get_ddl('INDEX', theIdx.INDEX_name, theSchema));
  end loop;
  for theView in (select view_name from dba_views where owner=theSchema) loop
    dbms_output.put_line(dbms_metadata.get_ddl('VIEW', theView.view_name, theSchema));
  end loop;  

Please note that this will not handle permissions. I am assuming from your interest that you are looking for grants to object that the source schema doesn't own. Normally moving permissions is a little more touchy since this relates to security. I don't have a good example of this.


Red Gate have a tool call Data Compare for Oracle that will help you.

The software can sync your two schemas for you, or you can generate a script and and run it through SQL Developer, Toad etc.

(Disclaimer: I work for Red Gate)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.