Oracle匯出備份和匯入恢複自動產生sql原始碼

                        

以下是oracle匯出備份和匯入恢複自動產生sql原始碼各變數含義為：
EXP_BACK_DIR： 匯出ORACLE資料庫的DMP檔案所放目錄
SCRIPT_FILE： 此代碼所產生的批次檔路徑及檔案名稱
LOG_FILE： 匯出記錄檔存放路徑及檔案名稱
INT_USER：匯出的ORACLE資料庫的使用者名稱
INT_PWD： 匯出的ORACLE資料庫的使用者的口令
以下代碼存成sql檔案後
可以通過COMMAND ：sqlplus internal/oracle@sqcdb @產生備份SQL代碼的原檔案名稱 <參數> 來運行。

EXP匯出備份SQL源檔案
--You must have select privileges on the v$parameter
--v$logfile v$datafile and v$controlfile data
--dictionary views belonging to SYS to run this program

define EXP_BACK_DIR = e:\oradb\expbackups
define SCRIPT_FILE = e:\back\expbackup.bat
define LOG_FILE= e:\back\expbackup.log
define INT_USER = gas
define INT_PWD = gas
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set echo off
col a new_value b
col c new_value d
select value a,to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') c from v$parameter where name ='db_name';

spool &SCRIPT_FILE
prompt rem ***** EXP ORACLE DATABASE FOR &INT_USER USER of &b ON WINDOWS NT ON &d*****
prompt
prompt rem ***** SET BACKUP FILES DIRECTORY *****
prompt md e:\oradb
prompt md e:\oradb\expbackups
prompt
select 'del &EXP_BACK_DIR\exp&INT_USER'||'.dmp' from dual;
prompt
select 'exp Userid=&INT_USER/&INT_PWD file=&EXP_BACK_DIR\exp&INT_USER'||'.dmp Buffer=102400 log=&EXP_BACK_DIR\exp&INT_USER grants=y indexes=y' from dual;
prompt
select 'copy &EXP_BACK_DIR\exp&INT_USER'||'.dmp &EXP_BACK_DIR\exp&INT_USER'||to_char(sysdate, 'MMDDHH24MI')||'.dmp' from dual;
prompt
prompt set LogFile=&LOG_FILE
prompt echo COMPLETE EXP BACKUP FOR &INT_USER USER "&b" DATABASE STARTED ON &d ...> %logFile%
prompt exit

spool off

$&SCRIPT_FILE
$del &SCRIPT_FILE
exit

IMP整個使用者匯入的SQL源檔案
--You must have select privileges on the v$parameter
--v$logfile v$datafile and v$controlfile data
--dictionary views belonging to SYS to run this program

define EXP_BACK_DIR = e:\oradb\expbackups
define SCRIPT_FILE = e:\back\imprevall.bat
define LOG_FILE= e:\back\imprevall.log
define INT_USER = gas
define INT_PWD = gas
define TO_USER = gas
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set echo off
col a new_value b
col c new_value d
select value a,to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') c from v$parameter where name ='db_name';

spool &SCRIPT_FILE
prompt rem ***** IMP ORACLE DATABASE FOR &INT_USER USER OF &b ON WINDOWS NT ON &d*****
prompt
select 'imp Userid=&INT_USER/&INT_PWD fromuser=&INT_USER touser=&TO_USER commit=y ignore=y Buffer=102400 file=&EXP_BACK_DIR\exp&INT_USER'||'.dmp log=&EXP_BACK_DIR\imp&TO_USER ' from dual;
prompt
prompt set LogFile=&LOG_FILE
prompt echo COMPLETE IMP RECOVER FOR &INT_USER USER OF "&b" DATABASE STARTED ON &d ...> %logFile%
prompt exit

spool off

$&SCRIPT_FILE
$del &SCRIPT_FILE
exit

IMP單個表匯入的SQL源檔案
--You must have select privileges on the v$parameter
--v$logfile v$datafile and v$controlfile data
--dictionary views belonging to SYS to run this program

-- define tablename =a3

define EXP_BACK_DIR = e:\oradb\expbackups
define SCRIPT_FILE = e:\back\imprevtab.bat
define LOG_FILE= e:\back\imprevtab.log
define SQL_FILE = e:\back\truntab.sql
define TRIG_FILE = e:\back\entrig.sql
define INT_USER = gas
define INT_PWD = gas
define TO_USER = gas
set feedback off
set heading off
set pagesize 0
set linesize 128
set verify off
set echo off
col a new_value b
col c new_value d
select value a,to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') c from v$parameter where name ='db_name';
spool &SQL_FILE
select 'alter table &&1 disable all triggers;' from dual;
select 'truncate table &&1 ;' from dual;
select 'exit' from dual;
spool off
spool &TRIG_FILE
select 'alter table &&1 enable all triggers;' from dual;
select 'exit' from dual;
spool off
spool &SCRIPT_FILE
prompt rem ***** IMP ORACLE DATABASE FOR TABLE OF &INT_USER USER OF &b ON WINDOWS NT ON &d*****
prompt sqlplus gas/gas@sqcdb.sqc.com @&SQL_FILE
select 'imp Userid=&INT_USER/&INT_PWD fromuser=&INT_USER touser=&TO_USER commit=y ignore=y Buffer=102400'||' tables='||'&&1'||' file=&EXP_BACK_DIR\exp&INT_USER'||'.dmp ' from dual;
prompt sqlplus gas/gas@sqcdb.sqc.com @&TRIG_FILE
prompt set LogFile=&LOG_FILE
prompt echo COMPLETE IMP RECOVER FOR TABLE OF &INT_USER USER OF "&b" DATABASE STARTED ON &d ...> %logFile%
prompt exit
spool off

$&SCRIPT_FILE
$del &SQL_FILE
$del &TRIG_FILE
$del &SCRIPT_FILE
exit