51 lines
1.1 KiB
SQL
51 lines
1.1 KiB
SQL
-- @pid_opencursors_sqltext.sql
|
|
SET echo off
|
|
SET lines 200
|
|
SET PAGES 50
|
|
|
|
COLUMN SPID FORMAT a6
|
|
COLUMN SID FORMAT 999
|
|
COLUMN SERIAL# FORMAT 99999
|
|
COLUMN username FORMAT a10
|
|
COLUMN osuser FORMAT a10
|
|
COLUMN machine FORMAT a20
|
|
COLUMN program FORMAT a20
|
|
COLUMN sql_text FORMAT a30
|
|
|
|
|
|
select LPAD(p.sPID,5,' ') SPID
|
|
, s.SID
|
|
, s.SERIAL#
|
|
, s.username
|
|
, s.osuser
|
|
, TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI') logon_time
|
|
, s.MACHINE
|
|
, s.PROGRAM
|
|
, sql.piece
|
|
, sql.sql_text
|
|
from v$session s, v$process p, v$open_cursor c, v$sqltext sql
|
|
where s.PADDR = p.ADDR
|
|
AND p.spid = '&PID'
|
|
and s.sid = c.sid
|
|
and c.address = sql.address
|
|
and c.hash_value = sql.hash_value
|
|
order by spid, c.address, c.hash_value, piece
|
|
;
|
|
|
|
|
|
Prompt "####### Current SQL being executed ######################"
|
|
|
|
SELECT sql.piece, sql.sql_text
|
|
FROM v$sqltext_with_newlines sql, v$session s, v$process p
|
|
where s.PADDR = p.ADDR
|
|
AND s.SQL_ADDRESS = sql.address
|
|
AND s.SQL_HASH_VALUE = sql.hash_value
|
|
AND p.spid = '&&PID'
|
|
order by sql.piece
|
|
/
|
|
|
|
|
|
SET echo on
|
|
|
|
|