!/bin/sh
#
# 2013-04-09 14:29
# Copyright (c) 2013 C.X All rights reserved.
#
set -x

###################################
#
# usage: BAKDDL
#
###################################

[ -f $HOME/.bash_profile ] && . $HOME/.bash_profile
[ -f $HOME/.profile ] && . $HOME/.profile
wdir=/u01/app/oracle/dba_scripts
dtime=`date +’%Y%m%d’`
day=`date +’%Y%m%d’`
hour=`date +’%Y%m%d%H’`
[ -d ${wdir}/spool ] || mkdir -p ${wdir}/spool
[ -d ${wdir}/conf ] || mkdir -p ${wdir}/conf
BakPath=/backup/ddl/28
BakFile=${BakPath}/${day}
conffile=${wdir}/conf/Backup_DDL.local.${dtime}.sql
spoolfile=${wdir}/spool/local.Backup_DDL.${dtime}.sql
SQLPLUS_CMD=”$ORACLE_HOME/bin/sqlplus / as sysdba @${conffile}”

cat << EOF >${conffile}
set verify off feedback off
set wrap on
set head on
set trimspool on
set linesize 200
set long 1000000
set longchunksize 1000000
SET linesize 161;
set pages 0;

spool ${spoolfile}
select username from dba_users where account_status = ‘OPEN’ and username not like ‘%SYS%’;
spool off
quit;
EOF

${SQLPLUS_CMD}

for username in `cat ${spoolfile}`
do
${wdir}/SCHDDL ${username}
done

tar -cvf ${BakFile}.tar ${BakPath}/*${day}*
gzip ${BakFile}.tar
if [ ! -z ${BakFile}.tar.gz ]; then
rm -f ${BakPath}/*${day}.sql
fi