At times, you would end up wanting to find active Sessions in Oracle Database in order to make certain decisions. Some of those could be;
- You just want to see what other Queries / Processes running in the Database
- You notice slowness in while running your queries and you want to see if there is any other Query or Stored Procedure is running that occupied more space in Oracle’s temporary database
- You notice your query is stuck forever and suspect some other query is blocking(deadlock) your query/process
Simple SQL Query to find all Active Sessions in Oracle database:
V$SESSION in Oracle helps achieve this.
To get a detail information the active Sessions in Oracle, you can follow the below query to find out the active Sessions in Oracle Database:
SELECT V$DATABASE.NAME DATABASE_NAME, PHYSICAL_READS, (V$SESS_IO.BLOCK_GETS + V$SESS_IO.CONSISTENT_GETS - V$SESS_IO.PHYSICAL_READS) / (DECODE(V$SESS_IO.BLOCK_GETS + V$SESS_IO.CONSISTENT_GETS,0,1, V$SESS_IO.BLOCK_GETS + V$SESS_IO.CONSISTENT_GETS)) * 100 HIT, V$SESSION.SID SID, V$SESSION.SERIAL# SERIAL, SPID PID, V$SESSION.USERNAME USERNAME, OSUSER, LOGON_TIME, SQL_ADDRESS, AUDIT_ACTIONS.NAME COMMAND, START_TIME, V$SESSION.STATUS STATUS, SQL_HASH_VALUE, V$SESSION.PROGRAM, MACHINE, V$SESSION.TERMINAL, V$PROCESS.PROGRAM, V$PROCESS.USERNAME PROCESS_TYPE, BLOCK_GETS, CONSISTENT_GETS, BLOCK_CHANGES, CONSISTENT_CHANGES, PREV_HASH_VALUE, MODULE FROM V$DATABASE, V$SESSION, V$PROCESS, V$SESS_IO, V$TRANSACTION, AUDIT_ACTIONS WHERE V$SESSION.COMMAND = AUDIT_ACTIONS.ACTION(+) AND V$SESSION.SADDR = V$TRANSACTION.SES_ADDR(+) AND V$SESS_IO.SID = V$SESSION.SID AND V$SESSION.PADDR = V$PROCESS.ADDR(+) AND TYPE = 'USER' AND V$SESSION.USERNAME Is Not NULL AND V$SESSION.STATUS IN ('ACTIVE','KILLED') ORDER BY V$SESSION.STATUS, 11, V$SESSION.USERNAME;
The above query looks little lengthy but just the Columns that are being selected alone is huge.
Below is how the result will look like;
You can simply add the below statements before and after the above query and run it from Oracle SQLPlus just get the list of active of sessions in Oracle spooled out into a .TXT file.
SET PAGESIZE 120; SET LINESIZE 80; SET ECHO OFF; SET FEEDBACK OFF; SPOOL AciveSessions.txt /* Paste the above Query here */ SPOOL OFF;
If you want to kill an active Oracle session, please read the below article:
Thank you for reading.
Please visit https://www.sql-scripts.com for anything that is related SQL Queries, Scripts and How to stuffs.