I want to delete connections in tnsnames.ora. I need to figure out which ones are currently in use, so that I don't delete them mistakenly

Question

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 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1539))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (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 12.2.0.1.0 - Production on 28-JAN-2020 23:51:38

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

Used parameter files:
/opt/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora


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

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.