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.
impdp, in combination with the
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
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.
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; declare theSchema VARCHAR2(30) := 'SCOTT'; begin 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; end; /
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)