以前用的自动备份脚本

-- 得到创建exp参数文件的脚本
set pagesize 0
set feedback off
set head off
set echo off
spool d:partition_backupexp_tables_partitions.sql
select 'set head off' || chr(10) ||
'spool d:partition_backupexp_' || lower(segment_name) ||
'_' || lower(partition_name) ||
'.parfile' || chr(10) || 'select ''' ||
'userid=usr/pwd@sid' || chr(10) ||
'buffer=31457280' || chr(10) ||
'tables=' || lower(owner) || '.' || lower(segment_name) ||
':' || lower(partition_name) || chr(10) ||
'file=d:partition_backupexp_' || lower(segment_name) ||
'_' || lower(partition_name) || '.dmp' || chr(10) ||
'log=d:partition_backuplogexp_' || lower(segment_name) ||
'_' || lower(partition_name) || '.log'' from dual ;' ||
chr(10) || 'spool off' obj
from user_segments
where segment_type = 'TABLE PARTITION'
;
spool off
set pagesize 20
set feedback on
set head on
set echo off

-- 得到导出数据的批处理文件
set pagesize 0
set feedback off
set head off
set echo off
spool d:partition_backupexp_tables_partitions.cmd
select 'exp parfile=d:partition_backupexp_' || lower(segment_name) ||
'_' || lower(partition_name) || '.parfile' || chr(10) ||
'rar a d:partition_backuppt_' || lower(segment_name) ||
'_' || lower(partition_name) || '.rar *.log *.dmp' || chr(10) ||
'del *.dmp' || chr(10) || 'del *.log' obj
from user_segments
where segment_type = 'TABLE PARTITION'
order by obj
;
select 'exit' from dual ;
spool off
set pagesize 20
set feedback on
set head on
set echo off

-- 得到创建imp参数文件的脚本
set pagesize 0
set feedback off
set head off
set echo off
spool d:partition_backupimp_tables_partitions.sql
select 'set head off' || chr(10) ||
'spool d:partition_backupimp_' || lower(segment_name) ||
'_' || lower(partition_name) || '.parfile' || chr(10) ||
'select ''' || 'userid=usr/pwd@sid' || chr(10) ||
'ignore=y' || chr(10) || 'fromuser=dxsq' || chr(10) || 'touser=dxsq' || chr(10) ||
'buffer=31457280' || chr(10) ||
'tables=' || lower(owner) || '.' || lower(segment_name) ||
':' || lower(partition_name) || chr(10) ||
'file=d:partition_backupexp_' || lower(segment_name) ||
'_' || lower(partition_name) || '.dmp' || chr(10) ||
'log=d:partition_backupimp_' || lower(segment_name) ||
'_' || lower(partition_name) ||
'.log'' from dual ;' || chr(10) ||
'spool off' obj
from user_segments
where segment_type = 'TABLE PARTITION'
;
spool off
set pagesize 20
set feedback on
set head on
set echo off

-- 得到导入数据的批处理文件
set pagesize 0
set feedback off
set head off
set echo off
spool d:partition_backupimp_tables_partitions.cmd
select 'rar e d:partition_backuppt_' || lower(segment_name) ||
'_' || lower(partition_name) || chr(10) ||
'exp parfile=d:partition_backupimp_' || lower(segment_name) ||
'_' || lower(partition_name) || '.parfile' || chr(10) ||
'del *.dmp' || chr(10) || 'del *.log' obj
from user_segments
where segment_type = 'TABLE PARTITION'
order by obj
;
select 'exit' from dual ;
spool off
set pagesize 20
set feedback on
set head on
set echo off