Identifying Locked Rows in Oracle Database using tables v$session and dba_objects

Purpose

This Oracle query pinpoints the exact row a specific session is waiting on due to a lock. It first finds the locked object and row location, then constructs a ROWID to fetch and display the entire locked row from the table. …

Identify Locked Objects in Oracle Database using v$locked_object, dba_objects, v$lock and v$session

Purpose

The provided Oracle Database code is a SQL query designed to identify locked objects within a database. It provides detailed information about the locked objects, including the username, session ID, object name, …

Oracle Database: Real-Time SQL Tracing for In-depth Performance Insights

Purpose

This Oracle database technique empowers you to dynamically enable or disable SQL tracing for specific sessions. SQL tracing captures detailed information about SQL statements executed within a session, providing valuable insights into …

Real-Time Oracle Rollback Detection: Is Your Database Undoing Changes?

This SQL query monitors real-time rollbacks in Oracle databases. It identifies which sessions are actively undoing changes and tracks their progress by observing the used_ublk value (the number of undo blocks in use). When used_ublk reaches zero, …

Show all Active SQL for Sessions using v$session and v$sqlarea

Gain real-time insights into currently active user sessions and the SQL statements they are executing to troubleshoot performance issues and optimize resources

SQL Code

 1set feedback off
 2set serveroutput on size 9999
 3column username format a20
 4 …

List all open oracle cursors by user or username

Track open cursor usage across user sessions to identify potential resource constraints or inefficiencies in database access patterns

SQL Code

 1set pages 999 lines 300
 2col username format a40
 3select 	sess.username as username
 4,	sess.sid as sid
 5,	sess.serial# as …

Display Session status associated with the specified os process id

SQL Code

 1select	s.username
 2,	s.sid
 3,	s.serial#
 4,	p.spid
 5,	last_call_et
 6,	status
 7from 	V$SESSION s
 8,	V$PROCESS p
 9where	s.PADDR = p.ADDR
10and	p.spid='&pid'
11/

Sample Oracle Output:

1Enter value for pid: 9999
2old  10: and …

Display the users current Session SQL

SQL Code

1Select sql_text
2from   v$sqlarea
3where  (address, hash_value) in
4(select sql_address, sql_hash_value 
5        from v$session
6        where username like '&username')
7/

Sample Oracle Output:

1Enter value for username: sys
2old   6:         where username …

Select Oracle user info including os pid

SQL Code

 1col "SID/SERIAL" format a10
 2col username format a20
 3col osuser format a15
 4col program format a50
 5select	s.sid || ',' || s.serial# "SID/SERIAL"
 6,	s.username
 7,	s.osuser
 8,	p.spid "OS PID"
 9,	s.program
10from	v$session s
11, …

Oracle sessions sorted by logon time

Track idle time for user sessions to uncover potential resource leaks, optimize database performance, and maintain security best practices.

SQL Code

1et lines 100 pages 999
2select username
3,      floor(last_call_et / 60) "Minutes"
4,      status
5from   v$session
6where …