unix 命令行下的oracle数据备份脚本,创建临时存储过程,读取user_tab_columns表中,各个字段的属性,灵活的配置生成数据的格式
gentbdata.sh:
#!/bin/ksh
fun_expdata () {
echo EXP ">>" TABLE[ $4 ] EXPFILE[ $6 ] CONDITION[ $5 ] SID[ $1/$2@$3 ]
$ORACLE_HOME/bin/sqlplus -S $1/$2@$3>/dev/null <<ORA
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set trim on
set trimspool on
set serveroutput on
set linesize 32767
set heading off
set feedback off
set pagesize 0
set verify off
spool ${6}
DECLARE
p_table varchar2(100) := '$4';
p_condition varchar2(1024) := '$5';
--
l_column_list VARCHAR2(32767);
l_value_list VARCHAR2(32767);
l_query VARCHAR2(32767);
l_cursor NUMBER;
ignore NUMBER;
l_insertline1 varchar2(32767);
l_insertline2 varchar2(32767);
cmn_file_handle UTL_FILE.file_type;
--
FUNCTION get_cols(p_table VARCHAR2)
RETURN VARCHAR2
IS
l_cols VARCHAR2(32767);
CURSOR l_col_cur(c_table VARCHAR2) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = upper(c_table)
ORDER BY column_id;
BEGIN
l_cols := null;
FOR rec IN l_col_cur(p_table)
LOOP
l_cols := l_cols || rec.column_name || ',';
END LOOP;
RETURN substr(l_cols,1,length(l_cols)-1);
END;
--
FUNCTION get_query(p_table IN VARCHAR2)
RETURN VARCHAR2
IS
l_query VARCHAR2(32767);
CURSOR l_query_cur(c_table VARCHAR2) IS
SELECT 'decode('||column_name||',null,''null'','||
decode(data_type,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
,'CHAR','''''''''||'||column_name ||'||'''''''''
,'DATE','''TO_DATE(''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''', ''''YYYY-MM-DD HH24:MI:SS'''')'''
,column_name
) || ')' column_query
FROM user_tab_columns
WHERE table_name = upper(p_table)
ORDER BY column_id;
BEGIN
l_query := 'SELECT ';
FOR rec IN l_query_cur(p_table)
LOOP
l_query := l_query || rec.column_query || '||'',''||';
END LOOP;
l_query := substr(l_query,1,length(l_query)-7);
RETURN l_query || ' FROM ' || p_table || ' ' || p_condition;
END;
--
BEGIN
l_column_list := get_cols(p_table);
l_query := get_query(p_table);
l_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 32767);
ignore := DBMS_SQL.EXECUTE(l_cursor);
--
LOOP
IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
l_insertline1:='insert into '||upper(p_table)||' ('||l_column_list||')';
l_insertline2:=' values ('||l_value_list||');';
DBMS_OUTPUT.put_line(l_insertline1);
DBMS_OUTPUT.put_line(l_insertline2);
ELSE
EXIT;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
spool off
exit
ORA
}
#==========================================================================#
# Copyright (C), 2011-2020, huateng #
# Version : 2.0 #
# Description: export data which format is SQL through sqlplus #
# File : gendata.sh #
# Author : #
# Date : 2015/02/13 #
# History: #
#==========================================================================#
#=================================START====================================#
DBUSER="$DBUSER"
DBPWD="$DBPWD"
DBNAME="$DBNAME"
TABLENAME=""
CONDITION="where 1=1"
FILENAME=".dat"
case $# in
0|4)
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
echo "FORMAT:gendata.sh tablename condition filename user passwd sid"
echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
exit
;;
1)
TABLENAME=$1
FILENAME=${1}$FILENAME
;;
2)
TABLENAME=$1
FILENAME=${1}$FILENAME
CONDITION=`echo $2 | sed "s/\'/\'\'/g"`
;;
3)
TABLENAME=$1
CONDITION=`echo $2 | sed "s/\'/\'\'/g"`
FILENAME=${3}$FILENAME
;;
5)
TABLENAME=$1
CONDITION=`echo $2 | sed "s/\'/\'\'/g"`
FILENAME=${3}$FILENAME
DBUSER=$4
DBPWD=$5
;;
6)
TABLENAME=$1
CONDITION=`echo $2 | sed "s/\'/\'\'/g"`
FILENAME=${3}$FILENAME
DBUSER=$4
DBPWD=$5
DBNAME=$6
;;
esac
fun_expdata $DBUSER $DBPWD $DBNAME $TABLENAME "$CONDITION" $FILENAME
exit
#=================================END======================================#
根据备份列表,调用上面的脚本,生产备份数据
gendbdata.sh:
#/bin/ksh
if [ $# != 2 ]
then
echo "FORMAT: gen.sh outputfile listfile!"
exit 1;
fi
filename=$1
DIR_TO_MAKE="../$2"
STAMP=`date +%Y%m%d%H%M%S`
mkdir $STAMP
cd $STAMP
cat $DIR_TO_MAKE | grep -v "^ *#" | tr a-z A-Z | sed 's/ //g' | sed 's/.*/~&\.\.\.!~&;!prompt!~&\.\.\./' | sed 's/~/prompt!prompt Deleting /' | sed 's/~/delete from /' | sed 's/~/prompt Loading /' > ${filename}_tmp11.sql
cat $DIR_TO_MAKE | grep -v "^ *#" | sed 's/ //g' | tr A-Z a-z > ${filename}_tmp21.sql
cp ${filename}_tmp21.sql ${filename}_tmp31.sql
sed 's/./=/g' ${filename}_tmp31.sql > ${filename}_tmp32.sql
sed 's/^/prompt ===========/' ${filename}_tmp32.sql > ${filename}_tmp33.sql
paste -d! ${filename}_tmp11.sql ${filename}_tmp33.sql > ${filename}_tmp51.sql
sed 's/^/prompt!@@/' ${filename}_tmp21.sql > ${filename}_tmp22.sql
sed 's/ *$/.dat;!prompt!commit;!prompt Done./' ${filename}_tmp22.sql > ${filename}_tmp52.sql
paste -d! ${filename}_tmp51.sql ${filename}_tmp52.sql > ${filename}_tmp53.sql
echo "---------------------------------------------" > ${filename}_tmp61.sql
echo "-- Compose Shell author is andy --" >> ${filename}_tmp61.sql
echo "-- Created by andy on 2014/11/13, 10:14:30 --" >> ${filename}_tmp61.sql
echo "---------------------------------------------" >> ${filename}_tmp61.sql
echo "spool ${filename}.log" >> ${filename}_tmp61.sql
echo "set define off" >> ${filename}_tmp61.sql
cat ${filename}_tmp61.sql ${filename}_tmp53.sql > ${filename}_tmp62.sql
echo "spool off" >> ${filename}_tmp62.sql
echo "set define on" >> ${filename}_tmp62.sql
cat ${filename}_tmp62.sql | tr '!' '\n' > ${filename}.sql
rm -f ${filename}_tmp11.sql
rm -f ${filename}_tmp21.sql ${filename}_tmp22.sql
rm -f ${filename}_tmp31.sql ${filename}_tmp32.sql ${filename}_tmp33.sql
rm -f ${filename}_tmp51.sql ${filename}_tmp52.sql ${filename}_tmp53.sql
rm -f ${filename}_tmp61.sql ${filename}_tmp62.sql
cat $DIR_TO_MAKE | grep -v "^ *#" | sed 's/ //g' | tr A-Z a-z | while read LINE
do
if [ "$LINE" = "" ]
then
continue;
fi
echo $LINE
echo "======== gendata $LINE ========"
gendata.sh $LINE ""
done
cd ..