Extracting data from Names Server base tables

Home

Toolkit:

My OraFAQ Blog

Contact me

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. 

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;

Sample output: 

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...