3.4 How do I…Determine who is logged on?Problem
I need to see which users are logged on to the database. If possible, I would also like to see more information on those users, such as name and program. How do I determine who is logged on?
Technique
By querying the V$SESSION view, you can determine who is logged on, as well as information such as the time of logon, the program running, and the operating system username. The V$SESSION view, which is owned by the SYS user account, can be viewed with any user that has been granted DBA, such as the WAITE account.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run the CHP3_5.sql script, shown in Figure 3.5. This shows all users currently logged on to the database. The output of the script is shown in Figure 3.6.
How It Works
The V$SESSION is a dynamic virtual table that holds information about each user logged on. By issuing a SELECT query against this virtual table, various aspects about each user account can be determined. The system username, the schema that the user is connected to, the date and time the user logged on, the program running (if any are applicable), and the operating system username can all be found in this virtual table.
The WAITE user has SELECT privileges to this view through the DBA permissions. Oracle keeps track of each user as they log on and off the database. The Monitor Session option in Server Manager runs off the V$SESSION view, and it can also be queried from within SQL*Plus.
Comments
When querying the V$SESSION view to determine the number of users, be sure to note that background processes, as well as the user issuing the query itself, are included in the total.
If the status column appears as KILLED, that session is marked by the database to be removed. Locks, memory, and all other resources held by that session will be released when the background processes remove the session from the database.