＋设为首页 ＋添加到收藏夹
我想用存储过程来备份数据库,最好是将所有表备在一个文件中,文件名用系统日期,谁做过吗?
方法很多若用存储过程，你执行一条动态含有EXP命令sql即可，如
select sysdate into s_date from dual;
s_sql:=exp user=system/manager owner(user1,user1) file=d:\||s_date||.dmp
你最好先去看看oracle的备份和恢复一本书，说实话，从来没听说过用存储过程进行备份方案的
以下是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
用exp 好了！
www.oradb.net
我有类似方案可以给你发一份
