数据文件和日志文件的批量迁移脚本

来源：互联网发布：hive sql insert into 编辑：程序博客网时间：2024/05/30 07:13

来自乐沙弥的世界

脚本内容如下：

Prompt
Prompt Step 1, Coping file to destination from source
Prompt ============================================
Prompt
set linesize 200
set heading off verify off feedback off termout off pagesize 999
define src_dir='ORCL'
define tar_dir='ORACLE'
spool /tmp/cp_files.sql
SELECT 'ho cp ' || name || ' ' || REPLACE (name, '&src_dir', '&tar_dir')
FROM v$datafile
UNION ALL
SELECT 'ho cp ' || name || ' ' || REPLACE (name, '&src_dir', '&tar_dir')
FROM v$tempfile
UNION ALL
SELECT 'ho cp ' || MEMBER || ' ' || REPLACE (MEMBER, '&src_dir', '&tar_dir') FROM v$logfile;
spool off;

@/tmp/cp_files.sql

set termout on
Prompt
Prompt Step 2, updating files to control file
Prompt ============================================
Prompt
set termout off
spool /tmp/update_cntl.sql
SELECT 'alter database rename file '''
|| name
|| ''' to '''
|| REPLACE (name, '&src_dir', '&tar_dir')
|| ''''
|| ';'
FROM v$datafile
UNION ALL
SELECT 'alter database rename file '''
|| name
|| ''' to '''
|| REPLACE (name, '&src_dir', '&tar_dir')
|| ''''
|| ';'
FROM v$tempfile
UNION ALL
SELECT 'alter database rename file '''
|| MEMBER
|| ''' to '''
|| REPLACE (MEMBER, '&src_dir', '&tar_dir')
|| ''''
|| ';'
FROM v$logfile;
spool off;
set termout on;
@/tmp/update_cntl.sql
set heading on verify on feedback on termout on

0 0