oracle-wiki.net - List the SQL statement Held for a Specific Session ID

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

List the SQL statement Held for a Specific Session ID

-- List the SQL statement held for a specific Session ID.

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

SET VERIFY OFF

COLUMN sql_text FORMAT a60

SELECT oc.sql_id, oc.sql_text, oc.last_sql_active_time as "Last Run", oc.sql_exec_id, oc.address, oc.hash_value, oc.sql_id

FROM v$open_cursor oc

WHERE oc.sid = &Session_ID

Notes on List SQL Statement for a Session (V$OPEN_CURSOR)

Purpose

Lists the SQL statements currently open for a specific session ID. It is a quick way to see exactly what SQL a given sessionhas run or is running, along with identifying details needed to look the statement up elsewhere.

Using this Script

Use this when you have a session ID and need to see the actual SQL it is running or has recently run. For example, when you need to identify a long running statement, or confirm exactly what a session is executing, or to get a SQL ID and address to use in other scripts such as a DBMS_XPLAN.

Variables used by the Script

Running the script will prompt for:

Session_ID - the SID of the session whose open cursors you want to list

Published on

Published 25th September 2026

Page updated

Google Sites

Report abuse