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.


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.

| improve this answer | |
  • +1 the old exp/imp is also good + no need for directory on the server (if you SA is sensitive about that). – haki Apr 25 '13 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 Apr 26 '13 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.

| improve this answer | |

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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