Thursday, February 7, 2013

Find and break Oracle jobs

When doing a production clone, it is often useful to drop all the jobs, database links, etc.  The following is a bash script to do just that.

########################################################################
# Break all the freakin' jobs so that Streams can instantiate!         #
########################################################################

if [ "${1}" = "" ] ; then
  echo "Usage ${0} sid,  exiting..."
  exit 1
fi
. /home/oracle/.bashrc.CLIENT

TARGET_SID=${1}
PRIVATE_FILE=/home/oracle/.private

MASTER_USER=system
MASTER_PASS=`cat $PRIVATE_FILE|grep -i "${TARGET_SID}:"|grep ":SYSTEM:"|head -1|awk -F: '{print $3}'`
echo $MASTER_USER
echo $MASTER_PASS

# Find 'em and Break 'em
sqlplus -s "$MASTER_USER/$MASTER_PASS@$TARGET_SID" <<EOF
set pages 9999
set lines 300
set trim on
set trims on
set feedback off
set termout off
set verify off
set head off
set newpage none
set echo off
set term off
spool OUT/create_procedures.sql

select 'create or replace procedure '||schema_user||'.drop_job_'||job||' as '||chr(10)||'begin'||chr(10)||'dbms_job.broken ('||job||', true);'||chr(10)||'end;'||chr(10)||'/'||chr(10)
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';


spool off;

spool OUT/run_procedures.sql

select 'exec '||schema_user||'.drop_job_'||job||';'||chr(10)||'commit;'||chr(10)
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';

spool off;

spool OUT/drop_procedures.sql

select 'drop procedure  '||schema_user||'.drop_job_'||job||';'
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';

spool off;

@ OUT/create_procedures.sql
@ OUT/run_procedures.sql
@ OUT/drop_procedures.sql
exit
EOF

No comments:

Post a Comment