Re: How to copy data across different schema

From: Pohl <news_at_thomas-pohl.net>
Date: Fri, 09 Nov 2001 16:58:03 +0100
Message-ID: <3BEBFD0B.6020505@thomas-pohl.net>

Moin,

If you try to copy schemes IMHO export, import is easiest possibility. If you are on UNIX the executable is named exp not exp80. --> exp80 help=y
--> imp80 help=y

-------------sample-------------------

exp80 scheme1/pw_at_orcl owner=scheme1 file=filename.dmp log=logfile.log consistent=y

imp80 scheme2/pw_at_orcl fromuser=scheme1 touser=scheme2 log=otherogfile.log file=filename.dmp

--------------/sample------------------------

> 1. Giving 'select grant' on source schema to destination schema.
> 2. Creating a synonym. But how?

something like the following script will create a file called cre_syn.sql this will produce what you asked for. You get not a copy of scheme1 but a reference to it!

----------------------------script--------------------------------

spool cre_syn.sql
set head off
set echo off

select 'connect scheme1/pw_at_orcl' from dual;

select 'grant select,insert,update,delete on '||table_name||' to scheme2;' from cat
--where table_name like 'PREFIX%';

select 'connect scheme2/pw_at_orcl' from dual;

select 'CREATE SYNONYM '||table_name||' FOR scheme1.'||table_name||';' from cat
--where table_name like 'PREFIX%';

spool off

• select 'exit' from dual; exit; --------------------------------/script------------------------

h2h

Thomas Received on Fri Nov 09 2001 - 09:58:03 CST