This is an old revision of the document!
Table of Contents
Database_sessions
Sessions sorted by login time
0@@
Free space in TEMP tablespaces
1@@
Live TEMP free space monitoring
2@@
Sessions currently holding segments in TEMP tablespace
3@@
More detailed sessions currently holding segments in TEMP tablespace
4@@
or
5@@
SQL statements used by TEMP sessions
6@@
Show a users current sql
select sql_text from v$sqlarea where 1=1 and (address, hash_value) in
(
select sql_address
, sql_hash_value
from v$session
where 1=1
and username like '&username'
);
Top 10 SQL statements
Lists all SQL statements in the library cache with CPU time > &1 &1 % of the total of all statements
7@@
Show what SQL statements are being run by all user sessions
8@@
Show blocking sessions using Oracle standard script
Run this if never run before (to create needed tables)
9@@
then to show locking processes in a tree structure (non indented process is blocking indented sessions)
10@@
Show blocking sessions
select s1.username || '@' || s1.machine||
' ( SID=' || s1.sid || ' ) is blocking '||
s2.username || '@' || s2.machine ||
' ( SID=' || s2.sid || ' ) ' blocking_status
from v$lock l1 , v$session s1 , v$lock l2 , v$session s2 where 1=1 and s1.sid = l1.sid and s2.sid = l2.sid and l1.block = 1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;
Show blocking sessions with lock type
11@@
Blocking sessions in tree layout
Show all ddl locks in the system
14@@
Show all locks, internal also
15@@
Show object locks
16@@
Generate kill statement for ddl locking sessions
17@@
Display any long operations
18@@
Show waits for (blocking) datapump sessions
19@@
Show current sessions that perform a lot of hard parses
20@@
All active sql
21@@
Identify the number of SQL statements in the library cache that are not using bind variables
These SQL statements cause expensive hard parse events in the shared pool
22@@
List number of open cursors by user
23@@
Show SQL statements generating the high number of cursors
Use SID from query above.
24@@
Show current setting for max open cursors and highest number used since startup
25@@