Using Find Users logged into Oracle / PLSQL in Oracle

This article is written about how to discover all customers currently logged into the Oracle database.

Description

You can discover all users currently logged into Oracle via strolling a question from a command prompt. In Oracle/PLSQL, there is a system view known as V$SESSION which indicates the session information for each modern-day session in the database. You can run a query towards this gadget view to return all customers that presently have a connection running in the Oracle/PLSQL database.

Syntax

The syntax to retrieve the customers logged into Oracle is:

SELECT USERNAME FROM V$SESSION;

This SELECT announcement 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 variety for session AUDSID Auditing session ID PADDR Address of the technique that owns the session USER# User identifier USERNAME User title (ie: root, techonthenet, etc) COMMAND Last declaration parsed OWNERID User identifier who owns the migratable session TADDR Address of the transaction kingdom 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 identify for schema OSUSER Operation gadget patron consumer name PROCESS Operating system consumer technique ID MACHINE Operating system computing device name TERMINAL Operating device terminal name PROGRAM Operating device application name TYPE Type of session SQL_ADDRESS Identifies the SQL statement presently being finished (used with SQL_HASH_VALUE) SQL_HASH_VALUE Identifies the SQL declaration presently being done (used with SQL_ADDRESS) SQL_ID SQL identifier for the SQL announcement currently being executed SQL_CHILD_NUMBER Child quantity for the SQL announcement currently being executed PREV_SQL_ADDR Identifies the closing SQL announcement carried out (used with PREV_HASH_VALUE) PREV_HASH_VALUE Identifies the final SQL declaration accomplished (used with PREV_SQL_ADDR) PREV_SQL_ID SQL identifier for the closing SQL declaration executed PREV_CHILD_NUMBER Child range for the remaining SQL assertion executed MODULE Name of the currently executing module (as per DBMS_APPLICATION_INFO.SET_MODULE) MODULE_HASH Hash fee of the presently executing module ACTION Name of the currently executing motion (as per DBMS_APPLICATION_INFO.SET_ACTION) ACTION_HASH Hash fee of the presently executing action CLIENT_INFO Client statistics (as per DBMS_APPLICATION_INFO.SET_CLIENT_INFO) FIXED_TABLE_SEQUENCE Sequence number incremented each time there has been an intervening pick from a dynamic performance table ROW_WAIT_OBJ# Object identifier for table specified by using ROW_WAIT_ROW# ROW_WAIT_FILE# Identifier for datafile specific in ROW_WAIT_ROW# ROW_WAIT_BLOCK# Identifier for block certain in ROW_WAIT_ROW# ROW_WAIT_ROW# Row that is currently locked LOGON_TIME Time that person logged in LAST_CALL_ET If STATUS is ACTIVE, LAST_CALL_ET is the elapsed time (in seconds) on account that the session became active. If STATUS is INACTIVE, LAST_CALL_ET is the elapsed time (in seconds) considering the session grew to become inactive. PDML_ENABLED Replaced by way of 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 point out whether or not failover has occurred RESOURCE_CONSUMER_GROUP Resource patron 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 off session BLOCK_SESSION Session identifier of blocking session SEQ# Sequence variety 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 extra parameter P2TEXT Description of the 2d extra parameter P2 Second extra parameter P2RAW Second extra parameter P3TEXT Description of the 1/3 additional parameter P3 Third extra 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 remaining wait time. If 0, then the session is currently waiting SECONDS_IN_WAIT If WAIT_TIME > 0, then SECOND_IN_WAIT is the range of seconds on the grounds that the begin of the remaining wait. If WAIT_TIME = 0, then SECONDS_IN_WAIT is the wide variety of seconds elapsed in the modern-day wait. STATE zero means WAITING -2 ability WAITED UNKNOWN TIME -1 means WAITED SHORT TIME >0 capacity 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