| |
 |
|
Oracle Concepts - Query for active users with v$session
Oracle Tips by Burleson Consulting
|
See what SQL users are running on the system
Once we figure out who is on the system, we will
probably want to know what they are doing. In this case, we will join the
v$session view we just queried with another view, the V$SQL view. The V$SQL view
will provide us with the SQL that is being executed on our system. Let’s see
GRUMPY’s session details:
SQL>
select a.sid, a.serial#, b.sql_text
2 from
v$session a, v$sqlarea b
3
where a.sql_address=b.address
4 and
a.username='GRUMPY';
SID SERIAL# SQL_TEXT
---------- ---------- ----------------------------------------------
122 61521 select count(*) from gen_person where gen_person_id=95000
See what session is blocking other sessions
Blocking sessions are a problem for the DBA and we need
a way to find them so we can deal with them. Blocking sessions occur when a
session issues an insert, update or delete command that changes a row.
When the
change occurs, the row is locked until the session either commits the change,
rolls the change back or the user logs off the system. You can see where
problems might occur, for example a user might make a change and then forget to
commit it and leaves for the weekend without logging off the system.
We can use this query to find these nasty blocking
sessions. We use our old friend, v$session to find the blocking session, and
also a list of sessions locked by that session.
Here is a query that gives us a list of blocking
sessions and the sessions that they are blocking:
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- --------
148 135 61521 Idle 64
In this case, we
find that session 148 is blocking session 135 and has been for 64 seconds. We
would then want to find out who is running session 148, and go find them and see
why they are having a problem.
For complete scripts
for detecting blocking session, see the
Oracle script collection.This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It’s only $19.95 when you buy it directly from the
publisher
here.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
