Problem solve Get help with specific problems with your technologies, process and projects.

Finding users and their network addresses

I want to find out all the users logged into Oracle along with their network addresses. How can I do it? For example, if there are two users, user1 and user2, logged on the database from the network addresses xxx.yyy.zzz.aa1 and xxx.yyy.zzz.aa2 respectivly, then I want to find that information.

Unfortunately, the Oracle database does not have a way to natively determine a user's IP address. The closest you can come to is to query V$SESSION for the MACHINE that they connect from. But they need to be on your network in order for this to give you a correct result.

Another way is to have your client session query for their IP address. This can be done with the following SQL statement:

SELECT sys_context('userenv','ip_address') FROM dual;
If you want this their IP address to show up in V$SESSION, then create a logon trigger which issues this query after a user creates a database connection. Once this trigger has the IP address, the trigger will then make calls to DBMS_APPLICATION_INFO to define the application information which is accessible in V$SESSION.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.