totn Oracle / PLSQL

Oracle / PLSQL: Find Users logged into 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
SADDR
SID
SERIAL#
AUDSID
PADDR
USER#
USERNAME
COMMAND
OWNERID
TADDR
LOCKWAIT
STATUS
SERVER
SCHEMA#
SCHEMANAME
OSUSER
PROCESS
MACHINE
TERMINAL
PROGRAM
TYPE
SQL_ADDRESS
SQL_HASH_VALUE
SQL_ID
SQL_CHILD_NUMBER
PREV_SQL_ADDR
PREV_HASH_VALUE
PREV_SQL_ID
PREV_CHILD_NUMBER
MODULE
MODULE_HASH
ACTION
ACTION_HASH
CLIENT_INFO
FIXED_TABLE_SEQUENCE
ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#
ROW_WAIT_ROW#
LOGON_TIME
LAST_CALL_ET
PDML_ENABLED
FAILOVER_TYPE
FAILOVER_METHOD
FAILED_OVER
RESOURCE_CONSUMER_GROUP
PDML_STATUS
PDDL_STATUS
PQ_STATUS
CURRENT_QUEUE_DURATION
CLIENT_IDENTIFIER
BLOCKING_SESSION_STATUS
BLOCKING_INSTANCE
BLOCK_SESSION
SEQ#
EVENT#
EVENT
P1TEXT
P1
P1RAW
P2TEXT
P2
P2RAW
P3TEXT
P3
P3RAW
WAIT_CLASS_ID
WAIT_CLASS#
WAIT_CLASS
WAIT_TIME
SECONDS_IN_WAIT
STATE
SERVICE_NAME
SQL_TRACE
SQL_TRACE_WAITS
SQL_TRACE_BINDS