Copy database.

[jayjaybigs]

Hello All,

I was givien a list schemas to copy from one database to the other. I intend to use export - import method like the following:

exp system@ODSUGPL OWNER=<user> CONSISTENT=Y COMPRESS=Y STATISTICS=NONE
FILE=file.dmp
LOG=name.log

imp system@ODSUGPL FROMUSER=<user> IGNORE=Y TOUSER=<new_user> STATISTICS=NONEGRANTS=N
FILE=file.dmp
LOG=name.log


However, I was wondering if any one has a script that I can use for this mutiple export/import as there are many mutiple schemas. Using a parameter file containing the schemas name will be ideal.

Please give a start that I can work with o r point me into a proper direction.

thanks for your help.

Tayo.

 

[jayjaybigs]

Addtional Info, this is a 9i database.

 

[jayjaybigs]

Also, I am only interested in copying the schemas not whole database.

 

[SantaMufasa]

Tayo,

I use the following "importer.sql" and "exporter.sql" scripts. (Since there is an ACCEPT...PROMPT sequence, you must save the code sets to scripts, not just copy and paste from here into your SQL> prompt.) I've widened the display here so that it more closely resembles the all-on-1-line behaviour of both the "exp" and "imp" command lines.

Section 1 -- Invocation example of the scripts:

SQL> @exporter
Enter (a portion) of the user(s) to export: test
exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.TEST.dump log=TEST_Exp.log statistics=none owner=TEST userid=TEST/TEST@dhunt
exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.TEST1.dump log=TEST1_Exp.log statistics=none owner=TEST1 userid=TEST1/TEST1@dhunt
exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y file=DHUNT.TESTNEW.dump log=TESTNEW_Exp.log statistics=none owner=TESTNEW userid=TESTNEW/TESTNEW@dhunt

wrote 'TempExp.bat'

SQL> @importer
Enter (a portion) of the user(s) to import: test
imp buffer=15000000 grants=y feedback=1000 fromuser=TEST touser=TEST file=DHUNT.TEST.dump log=TEST_Imp.log  userid=TEST/TEST@dhunt
imp buffer=15000000 grants=y feedback=1000 fromuser=TEST1 touser=TEST1 file=DHUNT.TEST1.dump log=TEST1_Imp.log  userid=TEST1/TEST1@dhunt
imp buffer=15000000 grants=y feedback=1000 fromuser=TESTNEW touser=TESTNEW file=DHUNT.TESTNEW.dump log=TESTNEW_Imp.log  userid=TESTNEW/TESTNEW@dhunt

'Wrote "TempImp.bat"'
**************************************************************************************************************************************************************************

You can then either copy and paste the displayed command lines at an o/s prompt, or you can execute the respective "*.bat" files.

Section 2 -- "exporter.sql" code:

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
accept userstr prompt "Enter (a portion) of the user(s) to export: "
spool TempExp.bat
select 'exp buffer=15000000 compress=n grants=y feedback=1000 consistent=y '
   ||'file='||upper(instance_name)||'.'
   ||username||'.dump log='||username||'_Exp.log statistics=none '
   ||'owner='||username
   ||' userid='||username||'/'||username||'@'||instance_name
from dba_users, v$instance
where username not in ('SYS','SYSTEM','DBSNMP')
  and username like upper('&userstr%')
order by username
/
spool off
prompt
prompt wrote 'TempExp.bat'
prompt
set feedback on
set linesize 180

Section 3 -- "importer.sql" code:

set pagesize 0
set trimspool on
set linesize 500
set feedback off
set verify off
accept userstr prompt "Enter (a portion) of the user(s) to import: "
spool TempImp.bat
select 'imp buffer=15000000 grants=y feedback=1000 fromuser='
   ||username||' touser='||username
   ||' file='||upper(instance_name)||'.'
   ||username||'.dump log='||username||'_Imp.log '
   ||' userid='||
   username||'/'||username||'@'||instance_name
from dba_users, v$instance
where username not in ('SYS','SYSTEM','DBSNMP')
  and username like upper('%&userstr%')
order by username
/
spool off
set feedback on
set linesize 180
prompt
prompt 'Wrote "TempImp.bat"'
prompt

You are welcome to modify my code to match your "exp"/"imp" preferences.

Let us know how this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jayjaybigs]

Thanks Mufasa for this effort.