Andrew Fraser DBA

List all crons and database links with scripts

I’m currently listing all interfaces – database links and cron jobs – into excel, and used these two scripts to automate the process.

Shell script to list all crons (note that this assumes crons in /etc/cron.d run as root):

for fle in `ls /var/spool/cron/* | grep -v tmp`
do
    echo $fle ===============
    grep -v '^#' $fle
done
echo Root crons
for fle in `ls /etc/cron.d/*`
do
    grep -v '^#' $fle | sed -e 's/root//'
done

And another shell script to check database links (here, change oracle_sid names at end to match names of your databases):

go () {
export ORACLE_SID=$1
sqlplus -s '/ as sysdba' <<e1
set pages 0 doc off lines 112 feed off
select lower('$ORACLE_SID '||owner||' '||host||' '||username||' '||db_link)
from dba_db_links
order by 1
/
e1
}
go SID1
go SID2

Paste the output into excel, and then use ‘data’ > ‘text to columns’ to turn it into readable content that can be sorted and filtered.