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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.