Exporting schema data (tables, permisions) into another schema on same database

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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)