oracle 更新后查询 事务,查询oracle正在执行的SQL和事务

查询Oracle正在执行的sql语句及执行该语句的用户：

SELECT b.sid oracleID,

b.username 登录Oracle用户名,

b.serial#,

spid 操作系统ID,

paddr,

sql_text 正在执行的SQL,

b.machine 计算机名

FROM v$process a, v$session b, v$sqlarea c

WHERE a.addr = b.paddr

AND b.sql_hash_value = c.hash_value

查看正在执行sql的发起者的发放程序：

SELECT OSUSER 电脑登录身份,

PROGRAM 发起请求的程序,

USERNAME 登录系统的用户名,

SCHEMANAME,

B.Cpu_Time 花费cpu的时间,

STATUS,

B.SQL_TEXT 执行的sql

FROM V$SESSION A

LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS

AND A.SQL_HASH_VALUE = B.HASH_VALUE

ORDER BY b.cpu_time DESC

查出oracle当前的被锁对象：

SELECT l.session_id sid,

s.serial#,

l.locked_mode 锁模式,

l.oracle_username 登录用户,

l.os_user_name 登录机器用户名,

s.machine 机器名,

s.terminal 终端用户名,

o.object_name 被锁对象名,

s.logon_time 登录数据库时间

FROM v$locked_object l, all_objects o, v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid

ORDER BY sid, s.serial#;

kill掉当前的锁对象:

alter system kill session 'sid， s.serial#‘;

查询当前正在执行的事务:

SELECT s.sid,

s.serial#,

s.event,

a.sql_text,

a.sql_fulltext,

s.username,

s.status,

s.machine,

s.terminal,

s.program,

a.executions,

s.sql_id,

p.spid,

a.direct_writes

FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s

LEFT JOIN v$sqlarea a

ON s.sql_id = a.sql_id

INNER JOIN v$process p

ON s.paddr = p.addr

查看Oracle 数据库中的长事务:

set linesize 200

set pagesize 5000

col transaction_duration format a45

with transaction_details as

( select inst_id

, ses_addr

, sysdate - start_date as diff

from gv$transaction

)

select s.username

, to_char(trunc(t.diff))

|| ' days, '

|| to_char(trunc(mod(t.diff * 24,24)))

|| ' hours, '

|| to_char(trunc(mod(t.diff * 24 * 60,24)))

|| ' minutes, '

|| to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))

|| ' seconds' as transaction_duration

, s.program

, s.terminal

, s.status

, s.sid

, s.serial#

from gv$session s

, transaction_details t

where s.inst_id = t.inst_id

and s.saddr = t.ses_addr

order by t.diff desc

/

查询长事务SQL:

下面SQL 查询数据库中正在执行大于N秒的事务信息：

with ltr as (

select to_char(sysdate,'YYYYMMDDHH24MISS') TM,

s.sid,

s.sql_id,

s.sql_child_number,

s.prev_sql_id,

xid,

to_char(t.start_date,'YYYYMMDDHH24MISS') start_time,

e.TYPE,e.block,

e.ctime,

decode(e.CTIME, 0, (sysdate - t.start_date) * 3600*24, e.ctime) el_second

--  q.sql_text

from v$transaction t, v$session s,v$transaction_enqueue e

where t.start_date <= sysdate - interval '100' second     /*查询开始多少秒的事务*/

and t.addr = s.taddr

--and s.sql_child_number = q.CHILD_NUMBER(+)

--and s.sql_id = q.sql_id(+) and s.prev_sql_id = q.sql_id(+)

and t.addr = e.addr(+) )

select ltr.* , (select q1.sql_text from v$sql q1 where ltr.prev_sql_id = q1.sql_id(+)

and rownum = 1) prev_sql_text ,

(select q1.sql_text from v$sql q1 where ltr.sql_id = q1.sql_id(+)

and ltr.sql_child_number = q1.CHILD_NUMBER(+)) sql_text

from ltr ltr;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22996654/viewspace-2151533/，如需转载，请注明出处，否则将追究法律责任。