Monday, 12 May 2014

Exclude Schema when exporting full database

######################
#
# Full export data pump excluding 'Oracle Schemas'

# To export whole database except one or more schemas
#
######################


for i in 1
do
PWD=`pwd`
DPAREX=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select DIRECTORY_NAME
 from dba_directories
where DIRECTORY_PATH like '${PWD}%'
/
END`
echo -e "The datapump directory is called: = \t${DPARE}\n\n"
SCHEMLIST=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select ''''||username||''','
 from dba_users
where username in
('ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBA_TOOLS','DBSNMP','DIP','DMSYS', \\  'ESMDBA','ESMDBA1','EXFSYS','HGIOSD','JPMCEPV','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','OPS$ORACLE','ORACLE', \\
'ORACLE_COM','ORDDATA','ORDPLUGINS','ORDSYS','OSDAUD','OUTLN','SCOTT','SECURITY','SI_INFORMTN_SCHEMA','SRDF_DBA', \\
'SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')
order by 1
/
END`
SL=`echo $SCHEMLIST | sed 's/ //g' | sed 's/,$//g'`
if [ -z ${DPAREX} ]
  then echo -e "Incorrect directory - change\n\tABORTING!!!"
else
  echo "Correct directory"
  DPARE=`echo ${DPAREX}`
  export DPARE
  echo ${DPARE}
  export ORACLE_OWNER=${LOGNAME}
  # DT=`date '+%Y%d%m%H%M'`
  DT=`date '+%Y%m%d_%H%M'`
  # FBDT=`date "+\\"TO_TIMESTAMP\(\'%d/%m/%Y %H:%M:%S\',\'DD-MM-YYYY HH24:MI:SS\'\)\\""`
  FBDT=`date "+\\"TO_TIMESTAMP\(\'%d/%m/%Y %H:%M:%S\',\'DD-MM-YYYY HH24:MI:SS\'\)"`
  #
  # Test Script
  #
  echo "nohup expdp \"/\" \\
  directory=${DPARE} \\
  dumpfile=expdp${SCHEMA}_${DT}_%U.dmp \\
  logfile=expdp${SCHEMA}_${DT}.log \\
  JOB_NAME=EXP_${SCHEMA}_${DT} \\
  COMPRESSION=ALL \\
  FLASHBACK_TIME="\\"${FBDT}"\\"\" \\
  PARALLEL=15 \\
  ESTIMATE_ONLY=Y \\
  FULL=Y \\
  EXCLUDE=SCHEMA:\"IN\(${SL}\)\" &"
  #
  # If schema list too long, then break using backslashes
  echo -e "\n\n"
fi
done

No comments:

Post a Comment