Would you like to know who is connected to your Oracle database? Is there anybody currently connected? How to check where the connections come from? If you ask yourself such questions, you can find answers in this short article. Identifying a source of connections is a quite common task for a database administrator. As it is one of troubleshooting steps for various problems, it is crucial to know how to do it. Here you can quickly possess this necessary peace of knowledge.

Why would you want to know it?

Oh, there are multiple reasons. Let me give you a few of them.

1. You work with a systems that has been in use on production for a few years. It consists of a database and many components like a web application, periodically run processes that do some recalculations, a data mining tool, a data auditing component etc. After a few years you might lose control of what those components are but your database hardware has got old and it is time to replace it. Then you need to identify all the components that use the database to make sure they are switched to the new one at the right time.
2. Data in the database gets locked regularly and the application cannot use it. The locks are not expected and you suspect it might be something else than your application which causes this.
3. The systems experiences a high workload and you are not sure what is a source of it.
4. You think you shut down all applications using the database but you want to be sure before shutting down the database.

For sure there are many more possibilities. Identifying connections to a database is a very widely needed skill.

Connection identification with v$session

If you have a need to identify what is connected to your Oracle database, my first choice would be using v$session view. It is a system view which contains all current sessions. It provides a lot of useful information but for purpose of checking WHO is connected, you actually need only three columns: username, machine and program. The usage is presented by the following query:

SELECT username, machine, program
FROM v$session
WHERE type = 'USER';

The query filters by the type USER, because you are probably not interested in internal Oracle processes. The first column, USERNAME, contains a name of a database account that was used to establish the connection. The second column, MACHINE, is a name of a client host which the connection comes from. The last column, PROGRAM, provides a name of the application that is connected. It is a name that comes from the client operating system so you cannot 100% rely on it as it can be fraudulent but it in most cases it provides with useful information that can cause "ah, yes, I know what it is" in your mind.

What privileges are needed to be able to use v$session?

v$session is a system view so a regular user does not have an access to it. Granting the access is easy but as much as it could be. Executing a simple GRANT statement does not work:

GRANT SELECT ON v$session TO web;

ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Why? The puzzle is solved when you know that v$session is a synonym for the sys.v_$session table. Running the grant statement on the sys.v_$session does the job:

GRANT SELECT ON sys.v_$session TO web;

Now, the web user can query v$session view/synonym.