Posts Tagged ‘v$session’

Oracle – Blocking Session

Show blocking sessions

Blocking sessions occur when an insert, update, delete is being issued and a commit has not been performed, this locks the row and prevents other users from making any changes to it.

SELECT blocking_session
, username
, sid
, serial#
, wait_class
, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY blocking_session;

From here we can see which user(s) are blocking the sessions and go and find out why.

Oracle – Check Users Connected to Database

To find how many users are on the database issue the following:

SQL> SELECT username
FROM v$session;

Show what users are running

, a.serial#
, a.username
, b.sql_text
FROM v$session a
, v$sqlarea b
WHERE a.sql_address=b.address;

You can add the following to the script if you have a lot of users and want to find one specific users code, but you will need to remove a.username from the above script:

AND a.username = '<username>';