如果数据库里有上百个DATABASE LINK， 而且同时要管理几十套这样的数据库，在日后改数据库用户密码时就要格外注意是否有DB LINK在使用，否则只改了LOCAL DB 的用户密码，没有级连修改REMOTE DB 的Database Link 密码，造成访问DB LINK时出错，现在去统计所有DB Link 是件很费时间的事。

自己整理了个简单的SHELL 去收集LOCAL 的所有DB LINKS，功能是如果DB LINK创建使用的是简单方式（没有配置TNSNAMES.ORA）直接取IP:PORT, 或如果使用TNSNAME Alias Name调用TNSPING 转换成IP, 同时还会判断tnsping ip port 里否通?

— I hope it’s useful

#

# file : dl.sh

# author: weejar (anbob.com)

# desc: tend to collect all DB links

# call: sh dl.sh

# date: 2015-5-5

# hp-ux , aix have tested.

# version: 0.3

# 0.1 to tnsping ip

# 0.2 to add isvalid flag

# 0.3 fixed string is not UPPER host, unable get ip , ip split char, ...

if [ -f ~/.profile ]; then

    . ~/.profile

fi 

# the file from db

FILE_DBLINK=dl`hostname`_`date +%Y%m%d`.txt

# the result file

FILE_DBLINK1=anbob_dls.csv

sqlplus -s / as sysdba << ! |sed  '/^$/d'  > $FILE_DBLINK

set timing off time off

set feed off

set lines 200  pages 1000

col owner for a20

col db_link for a40

col HOST for a20

col created for a10

set colsep " ,"

SELECT owner,

       db_link,

       username,

       CASE

          WHEN INSTR (UPPER (HOST), 'DESCRIPTION') > 1

          THEN

               REGEXP_SUBSTR (

                   UPPER (

                      REGEXP_SUBSTR (

                         UPPER (HOST),

                         'HOST[^=]*=[^0-9]*[0-9]+.[0-9]+.[0-9]+.[0-9]+')),

                   '[0-9]+.[0-9]+.[0-9]+.[0-9]+')

             ||':'|| REGEXP_SUBSTR (

                   UPPER (

                      REGEXP_SUBSTR (UPPER (HOST), 'PORT[^=]*=[^0-9]*[0-9]+')),

                   '[0-9]+')

          ELSE

              host

       END as host

       ,TO_CHAR (created, 'yyyymmdd') created

  FROM dba_db_links;

!

if [ -f $FILE_DBLINK1 ]; then

    cat FILE_DBLINK1 >> FILE_DBLINK1.bak

fi  

head -n 1  $FILE_DBLINK > $FILE_DBLINK1

sed '1,2d' $FILE_DBLINK | while read line

do

# TNS=`echo "$line"|awk '$4 !~ /:/ && NF==5 {print $4}'`

TNS=`echo "$line"|awk 'NF==5 {print $4}'|awk '{sub(/^\,*/,"",$0);print $0 ;}'`

echo "to convert tnsnames alias $TNS to IP..."

# linux

# IPS=`tnsping $TNS|grep "DESCRIPT"|grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}'|tr "\\n" ","`

# HP unix

   TNSTR=`tnsping $TNS|tail -n 2`

   IPS=`echo $TNSTR|grep "DESCRIPT"|tr '[a-z]' 'A-Z'|sed -e 's/.*HOST *= *//' -e 's/ *).*//'|tr "\\n" ";"| awk '{sub(/\;*$/,"",$0);print $0 ;}'`

   ISOK=`echo $TNSTR|tail -n 1|grep "OK"|wc -l`

   if [ -z "$IPS" ]; then

      IPS="N/A"

   fi

   if [ 1 -eq "$ISOK" ]; then

    # awk '{if ($1 ~ /^all/) print $0, "anotherthing"; else print $0}'

    # linux

    # sed -i "/$line/ s/$/\t valid/" $FILE_DBLINK

    ISVALID="YES"

   else

     ISVALID="N/A"

   fi

   echo "$line  ,$IPS   ,$ISVALID" >>$FILE_DBLINK1

done 

echo "Note: " >>$FILE_DBLINK1

echo "The last 1 column is "IS_valid" flag that tnsping ip and port is ok! and the last 2th column is "IP"  of cut from tnsping output." >>$FILE_DBLINK1

echo "RESULT FILE NAME: $FILE_DBLINK1"

echo "================== if had errors to contact weejar@gmail.com  ========================"

echo "done."