Extracting tnsnames.ora from Names Server base tables



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. This can be used to extract an equivalent tnsnames.ora .

Note: The easiest way to put the names server's data in a tnsnames.ora file is to enter namesctl and use the dump_tnsnames command. However, if something's wrong with the names server, you may not be able to start it to issue this command. In this situation, it's handy to be able to extract the data from the region database.

set pages 0
set lines 1000
spool tnsnames.ora.new
select substr(astr,1,length(astr)-2) tnsname
(select name_p || '=
 ' || substr(zd_value1_p, instr(zd_value1_p,'(DESCRIP'), 100000000000) 
 from onrs_region
 where instr(upper(zd_value1_p),'SID',1)!=0
spool off

Sample output:






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