| |
 |
|
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
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address =
sqlarea.address
and sesion.username is not null
For complete scripts
for detecting blocking session, see the
Oracle script collection.
[http://dba-oracle.com/include_tuning_book3.htm]
|
