I'm using Oracle 12c in Debian 8 (on a vm)

This question is for educational purposes only. I'm not using any production servers, so anything that you can tell me won't have consecuences

To start with, I found this link to delete registers in tnsnames.ora. I didn't test it yet because first, I needed to know if there were active connections in the database using the information of the tnsnames.ora.
I'm asking here because I found no way of doing this, but it may be possible to do it.

For you to know, I'm using this command for connecting to the database, so that I clearly specify a tnsname

rlwrap sqlplus sys as sysdba@tnstest

The contents of my tnsnames.ora are the following

tnstest =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
      (SERVICE_NAME = orcl)

But, if I want to be 100% sure that this is working, I use this command

tnsping tnstest

Which results are...

TNS Ping Utility for Linux: Version - Production on 28-JAN-2020 23:51:38

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

After all of this I know that my configuration is working, I'm using a register in tnsnames.ora for the connection, but I don't know a way of checking...
"Hey, now that I'm connected, I want to know which register I used from the tnsnames file for connecting, if I used that file of course"

Is there any possibility that I could get this information?

Well, my following attempts of trying to figure this out, were checking the v$session view, if there was any field that I could use to get this information, but I couldn't find one.

After all of this, ultimately, I came here for asking. I don't know what else to try nor I have more ideas of what should I do next.

P.S: there's a chance that this problem is impossible to solve, because this is task I'm doing due to I'm a student of sysadmin, and our teacher intentionally added unsolvable questions.
Even so, I still think there might be a solution for this particular case


1 Answer 1


I don't think you can get that from the database server side. The tnsnames.ora file is local to the client, so you could have a net service name (connection name) of 'ORANGE' that connects to a database service called 'BANANA'.

I had a look at some of our listener log files, and they don't have this info. The SYS_CONTEXT function exposes a lot of info but not the client's local net service name. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086

If you want to differentiate connections, you could have multiple service names for the database, e.g. 'DB1_OLTP', 'DB1_REPORTS', 'DB1_NORMAL'. The service name can be used to assign the session to a resource consumer group to limit CPU etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.