找执行的SQL

--通过v$session找SQL
select sa.*, sel.sql_address,sel.sql_hash_value ,sel.*
from v$session  sel ,v$sqlarea sa
where HexToRaw(sel.sql_address)=sa.ADDRESS(+)
and sel.sql_hash_value=  sa.hash_value(+)
and sel.PROGRAM like 'dis%'
ORDER BY sa.address,sa.hash_value;

--通过v$session找SQL
select sa.*, sel.sql_address,sel.sql_hash_value ,sel.*
from v$session  sel ,v$sqltext  sa
where HexToRaw(sel.sql_address)=sa.ADDRESS(+)
and sel.sql_hash_value=  sa.hash_value(+)
and sel.PROGRAM like 'dis%'
ORDER BY sa.address,sa.hash_value;

--通过SPID找SQL
SELECT   /*+ ORDERED */
         sql_text
    FROM v$sqltext a
   WHERE (a.hash_value, a.address) IN (
            SELECT DECODE (sql_hash_value,
                           0, prev_hash_value,
                           sql_hash_value
                          ),
                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
              FROM v$session b
             WHERE b.paddr = (SELECT addr
                                FROM v$process c
                               WHERE c.spid = '&pid'))
ORDER BY piece ASC

--通过SID找SQL
select sql_text from v$sqltext a where a.hash_value=(select sql_hash_value from v$session b where b.sid='
&sid')
order by piece asc

 

--查询正在执行SQL

select sid,
v$session.username 用户名,
last_call_et 持续时间,
status 状态,
LOCKWAIT 等待锁,
machine 用户电脑名,
logon_time 开始登入时间,
sql_text
from v$session, v$process, v$sqlarea
where paddr = addr
and sql_hash_value = hash_value
and status = 'ACTIVE'
and v$session.username is not null
order by last_call_et desc;