Find Users logged into Oracle/PLSQL
Course- Oracle/PLSQL >
This Oracle tutorial explains how to find all users currently logged into the Oracle database.
Description
You can find all users currently logged into Oracle by running a query from a command prompt. In Oracle/PLSQL, there is a system view called V$SESSION which shows the session information for each current session in the database. You can run a query against this system view to return all users that currently have a connection running in the Oracle/PLSQL database.
Syntax
The syntax to retrieve the users logged into Oracle is:
SELECT USERNAME FROM V$SESSION;
This SELECT statement will return each username that is logged in.
The V$SESSION view contains the following columns:
| Column | Explanation |
|---|---|
| SADDR | Address for session |
| SID | Identifier for session |
| SERIAL# | Serial number for session |
| AUDSID | Auditing session ID |
| PADDR | Address of the process that owns the session |
| USER# | User identifier |
| USERNAME | User name (ie: root, techonthenet, etc) |
| COMMAND | Last statement parsed |
| OWNERID | User identifier who owns the migratable session |
| TADDR | Address of the transaction state object |
| LOCKWAIT | Address for lock wait |
| STATUS | Status of the session. It can be one of the following: ACTIVE, INACTIVE, KILLED, CACHED, or SNIPED. |
| SERVER | Type of server. It can be one of the following: DEDICATED, SHARED, PSEUDO, or NONE. |
| SCHEMA# | User identifier for schema |
| SCHEMANAME | User name for schema |
| OSUSER | Operation system client user name |
| PROCESS | Operating system client process ID |
| MACHINE | Operating system machine name |
| TERMINAL | Operating system terminal name |
| PROGRAM | Operating system program name |
| TYPE | Type of session |
| SQL_ADDRESS | Identifies the SQL statement currently being executed (used with SQL_HASH_VALUE) |
| SQL_HASH_VALUE | Identifies the SQL statement currently being executed (used with SQL_ADDRESS) |
| SQL_ID | SQL identifier for the SQL statement currently being executed |
| SQL_CHILD_NUMBER | Child number for the SQL statement currently being executed |
| PREV_SQL_ADDR | Identifies the last SQL statement executed (used with PREV_HASH_VALUE) |
| PREV_HASH_VALUE | Identifies the last SQL statement executed (used with PREV_SQL_ADDR) |
| PREV_SQL_ID | SQL identifier for the last SQL statement executed |
| PREV_CHILD_NUMBER | Child number for the last SQL statement executed |
| MODULE | Name of the currently executing module (as per DBMS_APPLICATION_INFO.SET_MODULE) |
| MODULE_HASH | Hash value of the currently executing module |
| ACTION | Name of the currently executing action (as per DBMS_APPLICATION_INFO.SET_ACTION) |
| ACTION_HASH | Hash value of the currently executing action |
| CLIENT_INFO | Client information (as per DBMS_APPLICATION_INFO.SET_CLIENT_INFO) |
| FIXED_TABLE_SEQUENCE | Sequence number incremented each time there has been an intervening select from a dynamic performance table |
| ROW_WAIT_OBJ# | Object identifier for table specified by ROW_WAIT_ROW# |
| ROW_WAIT_FILE# | Identifier for datafile specified in ROW_WAIT_ROW# |
| ROW_WAIT_BLOCK# | Identifier for block specified in ROW_WAIT_ROW# |
| ROW_WAIT_ROW# | Row that is currently locked |
| LOGON_TIME | Time that user logged in |
| LAST_CALL_ET | If STATUS is ACTIVE, LAST_CALL_ET is the elapsed time (in seconds) since the session became active. If STATUS is INACTIVE, LAST_CALL_ET is the elapsed time (in seconds) since the session became inactive. |
| PDML_ENABLED | Replaced by PDML_STATUS |
| FAILOVER_TYPE | What type of transparent application failover is enabled for the session. It can be one of the following: NONE, SESSION, or SELECT. |
| FAILOVER_METHOD | Method of transparent application failure for the session. It can be one of the following: NONE, BASIC, or PRECONNECT. |
| FAILED_OVER | YES or NO to indicate whether failover has occurred |
| RESOURCE_CONSUMER_GROUP | Resource consumer group for the session |
| PDML_STATUS | ENABLED or DISABLED |
| PDDL_STATUS | ENABLED or DISABLED |
| PQ_STATUS | ENABLED or DISABLED |
| CURRENT_QUEUE_DURATION | Length of time that session has been queued |
| CLIENT_IDENTIFIER | Client identifier for the session |
| BLOCKING_SESSION_STATUS | It can be one of the following values: VALID, NO HOLDER, GLOBAL, NOT IN WAIT, or UNKNOWN |
| BLOCKING_INSTANCE | Instance identifier of blocking session |
| BLOCK_SESSION | Session identifier of blocking session |
| SEQ# | Sequence number that is incremented for each wait |
| EVENT# | Event number |
| EVENT | Resource that the session is waiting for |
| P1TEXT | Description of the first additional parameter |
| P1 | First additional parameter |
| P1RAW | First additional parameter |
| P2TEXT | Description of the second additional parameter |
| P2 | Second additional parameter |
| P2RAW | Second additional parameter |
| P3TEXT | Description of the third additional parameter |
| P3 | Third additional parameter |
| P3RAW | Third additional parameter |
| WAIT_CLASS_ID | Identifier of the wait class |
| WAIT_CLASS# | Number of the wait class |
| WAIT_CLASS | Name of the wait class |
| WAIT_TIME | Value of session's last wait time. If 0, then the session is currently waiting |
| SECONDS_IN_WAIT | If WAIT_TIME > 0, then SECOND_IN_WAIT is the number of seconds since the start of the last wait. If WAIT_TIME = 0, then SECONDS_IN_WAIT is the number of seconds elapsed in the current wait. |
| STATE | 0 means WAITING -2 means WAITED UNKNOWN TIME -1 means WAITED SHORT TIME >0 means WAITED KNOWN TIME |
| SERVICE_NAME | Service name of the session |
| SQL_TRACE | ENABLED or DISABLED |
| SQL_TRACE_WAITS | TRUE or FALSE |
| SQL_TRACE_BINDS | TRUE or FALSE |