Oracle Names Server can be configured to store TNS connect data in database tables. The
TNS connect string data is found in NAMES.ONRS_REGION. Unfortunately the whole connect
string is present as one field; the different attributes, such as SID and HOST, are not
separated out into fields. A bit of tricky SQL can do this for you so that you can easily
query & compare different TNS aliases pointing to the same SID, the same host, etc.
Sample output:
select name_p TNS_ALIAS,
upper(substr(zd_value1_p,
DECODE(instr(upper(zd_value1_p),'SID',1),
0,NULL,
instr(upper(zd_value1_p),'SID',1))+4,
INSTR(SUBSTR(ZD_VALUE1_P,INSTR(upper(ZD_VALUE1_P),'SID',1),1000),')',1)-5)
) SID,
upper(substr(zd_value1_p,
DECODE(instr(upper(zd_value1_p),'HOST',1),
0,NULL,
instr(upper(zd_value1_p),'HOST',1))+5,
INSTR(SUBSTR(ZD_VALUE1_P,INSTR(upper(ZD_VALUE1_P),'HOST',1),1000),')',1)-6)
) HOST,
upper(substr(zd_value1_p,
DECODE(instr(upper(zd_value1_p),'PORT',1),
0,NULL,
instr(upper(zd_value1_p),'PORT',1))+5,
INSTR(SUBSTR(ZD_VALUE1_P,INSTR(upper(ZD_VALUE1_P),'PORT',1),1000),')',1)-6)
) PORT,
zd_value1_p CONNECT_DATA
from onrs_region
where instr(upper(zd_value1_p),'SID',1)!=0;
TNS_ALIAS SID HOST PORT
----------------------------------- ------- ----------------------------------- ----
aspdev.World ASPDEV ABI.FAS.HARVARD.EDU 5121
test_aspeRIn.World ASPT LAKE.FAS.HARVARD.EDU 5121
dwhrprd3.World DWHRPRD DWPROD1.HARVARD.EDU 8103
tst_anakin.World ASPT ANAKIN.HMS.HARVARD.EDU 2687
dwhrtst.World DWHRTST DWDEV1.HARVARD.EDU 8003
HIRES.World PROD920 HIRESDB.HARVARD.EDU 1521
fintest.cadm.harvard.edu FINTEST APOLLO4.CADM.HARVARD.EDU 8803
asperin.World ASPY LAKE.FAS.HARVARD.EDU 5122
training.World ASPF ABI.FAS.HARVARD.EDU 5122
fintest.harvard.edu FINTEST APOLLO4.CADM.HARVARD.EDU 8803
Note: Proofread any scripts before using. Always try scripts on a test
instance
first. I'm not responsible for any damage, even if you somehow manage to make my
scripts corrupt every last byte of your data, set your server on fire and serve you
personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...