SQL by Session ID (sqlbysid.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script retrieves the complete SQL text currently being executed by a specific session ID. Essential for diagnosing performance issues and understanding what SQL statements are running in problematic sessions.

The Script

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT a.sql_text
FROM   v$sqltext a,
       v$session b
WHERE  a.address = b.sql_address
AND    a.hash_value = b.sql_hash_value
AND    b.sid = &sid
ORDER BY a.piece;

PROMPT
SET PAGESIZE 14

Parameters

The script prompts for:

• sid: Session ID to analyze

Usage

-- Basic usage (will prompt for SID)
@sqlbysid.sql

-- Example when prompted:
-- Enter value for sid: 123

Sample Output

SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ FIRST_ROWS(100) */
  c.customer_id,
  c.customer_name,
  o.order_date,
  o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.region = 'NORTH'
  AND o.order_date >= SYSDATE - 30
ORDER BY o.order_date DESC