查询Oracle正在执行的sql语句及当前被锁对象
--查询Oracle正在执行的sql语句及执行该语句的用户[color=silver][backcolor=rgb(248, 248, 248)][sql] [color=rgb(160,160,160)][size=9px]http://blog.csdn.net/jlds123/article/details/6572559#]view plain[color=rgb(160,160,160)][size=9px]http://blog.csdn.net/jlds123/article/details/6572559#]copy[color=rgb(160,160,160)][backcolor=inherit][size=9px]https://code.csdn.net/snippets/163690][img=12,12]https://code.csdn.net/assets/CODE_ico.png[/img][color=rgb(160,160,160)][backcolor=inherit][size=9px]https://code.csdn.net/snippets/163690/fork][img=12,12]https://code.csdn.net/assets/ico_fork.svg[/img]
[list=1]
[*][backcolor=inherit]SELECT b.sid oracleID,
[*][backcolor=inherit] b.username 登录Oracle用户名,
[*][backcolor=inherit] b.serial#,
[*][backcolor=inherit] spid 操作系统ID,
[*][backcolor=inherit] paddr,
[*][backcolor=inherit] sql_text 正在执行的SQL,
[*][backcolor=inherit] b.machine 计算机名
[*][backcolor=inherit]FROM v$process a, v$session b, v$sqlarea c
[*][backcolor=inherit]WHERE a.addr = b.paddr
[*][backcolor=inherit] AND b.sql_hash_value = c.hash_value
[*]--查看正在执行sql的发起者的发放程序
[color=silver][backcolor=rgb(248, 248, 248)][sql] [color=rgb(160,160,160)][size=9px]http://blog.csdn.net/jlds123/article/details/6572559#]view plain[color=rgb(160,160,160)][size=9px]http://blog.csdn.net/jlds123/article/details/6572559#]copy[color=rgb(160,160,160)][backcolor=inherit][size=9px]https://code.csdn.net/snippets/163690][img=12,12]https://code.csdn.net/assets/CODE_ico.png[/img][color=rgb(160,160,160)][backcolor=inherit][size=9px]https://code.csdn.net/snippets/163690/fork][img=12,12]https://code.csdn.net/assets/ico_fork.svg[/img]
[list=1]
[*][backcolor=inherit]SELECT OSUSER 电脑登录身份,
[*][backcolor=inherit] PROGRAM 发起请求的程序,
[*][backcolor=inherit] USERNAME 登录系统的用户名,
[*][backcolor=inherit] SCHEMANAME,
[*][backcolor=inherit] B.Cpu_Time 花费cpu的时间,
[*][backcolor=inherit] STATUS,
[*][backcolor=inherit] B.SQL_TEXT 执行的sql
[*][backcolor=inherit]FROM V$SESSION A
[*][backcolor=inherit]LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
[*][backcolor=inherit] AND A.SQL_HASH_VALUE = B.HASH_VALUE
[*][backcolor=inherit]ORDER BY b.cpu_time DESC
[*]--查出oracle当前的被锁对象
[color=silver][backcolor=rgb(248, 248, 248)][sql] [color=rgb(160,160,160)][size=9px]http://blog.csdn.net/jlds123/article/details/6572559#]view plain[color=rgb(160,160,160)][size=9px]http://blog.csdn.net/jlds123/article/details/6572559#]copy[color=rgb(160,160,160)][backcolor=inherit][size=9px]https://code.csdn.net/snippets/163690][img=12,12]https://code.csdn.net/assets/CODE_ico.png[/img][color=rgb(160,160,160)][backcolor=inherit][size=9px]https://code.csdn.net/snippets/163690/fork][img=12,12]https://code.csdn.net/assets/ico_fork.svg[/img]
[list=1]
[*][backcolor=inherit]SELECT l.session_id sid,
[*][backcolor=inherit] s.serial#,
[*][backcolor=inherit] l.locked_mode 锁模式,
[*][backcolor=inherit] l.oracle_username 登录用户,
[*][backcolor=inherit] l.os_user_name 登录机器用户名,
[*][backcolor=inherit] s.machine 机器名,
[*][backcolor=inherit] s.terminal 终端用户名,
[*][backcolor=inherit] o.object_name 被锁对象名,
[*][backcolor=inherit] s.logon_time 登录数据库时间
[*][backcolor=inherit]FROM v$locked_object l, all_objects o, v$session s
[*][backcolor=inherit]WHERE l.object_id = o.object_id
[*][backcolor=inherit] AND l.session_id = s.sid
[*][backcolor=inherit]ORDER BY sid, s.serial#[backcolor=inherit];
[*]
--kill掉当前的锁对象可以为alter system kill session 'sid， s.serial#‘;
查看具体pid的执行sql语句：
SELECT sql_text FROM v$sqlarea 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 = 29276));
查看具体pid的机器，用户和状态：
select b.server,b.username,b.sid,b.machine FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = 29306); 监测正在运行的SQL运行状态[backcolor=rgb(244, 251, 244)]select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null;
[backcolor=rgb(244, 251, 244)]select a.username,a.sid,b.sql_text from v$session a,v$sqlarea b where a.sql_address=b.address;
[backcolor=rgb(244, 251, 244)]select
b.sql_text, --SQL内容
a.MACHINE, --哪台机器运行的SQL
a.USERNAME,--哪个用户运行的SQL
a.MODULE,--是哪运行方式
c.sofar/totalwork*100,--工作进行了百分之多少
c.elapsed_seconds, --己经用了多少时间(秒)
c.time_remaining --还剩多少时间(秒)
from v$session a, v$sqlarea b,v$session_longops c
where a.sql_hash_value=b.HASH_VALUE
and a.sid=c.sid and a.SERIAL#=c.SERIAL#;[backcolor=rgb(244, 251, 244)]
如有个运行时候长的SQL,可通过此SQL查看到底还要运行多久,完成了多少工作等,对于SQL的性能调试还是挺有用的.尤其是对那些性能问题比较严重的SQL,就不必等太久就知道它的性能到底怎么样.[backcolor=rgb(244, 251, 244)]通过很多字典可以查看那些过程是否在正在运行中，比如v$[color=#066cc]http://biancheng.dnbcw.info/access/]access，dba_ddl_locks，v$db_object_cache等，当然通过v$session+v$sql/sqlarea等也是可以做到的
[color=rgb(156,36,35)][font=微软雅黑, 黑体, Verdana][size=20px]查看前五个最占用CPU的Oracle会话进程.以及他们正在执行的sql语句[color=rgb(86,86,86)] [color=rgb(86,86,86)]
[color=green]#!/bin/bash
ps -e -o pcpu -o pid -o user -o args | grep oraclemktdb | sort -k 1| tail -5r
spid=`ps -e -o pcpu -o pid -o user -o args | grep oraclemktdb | sort -k 1| tail -5r | awk '{print $2}'`
for i in $spid
do
sqlplus -S /nolog << EOF
conn / as sysdba
set feedback off
set linesize 200
set pagesize 70
column spid format 99999
column sid format 99999
column module format a20
column username format a8
column sql_text format a60
select distinct c.spid,b.sid,b.username,a.module,a.hash_value,sql_text
from v$sql a,v$session b,v$process c
where a.hash_value=b.sql_hash_value and a.address=b.sql_address and b.paddr=c.addr and c.spid =$i;
exit
EOF
done
查看造成等待事件的具体SQL语句[color=rgb(86,86,86)]
[color=rgb(86,86,86)]
[color=rgb(86,86,86)]
先查看存在的等待事件：
col event for a40
col WAIT_CLASS format a20
select sid,WAIT_CLASS,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait
where event not like 'SQL%' and event not like 'rdbms%';
复制上面查到的等待事件，替换下面红色字体。即可找到具体的SQL语句
col objn format a26
col otype format a10
select b.* ,a.sql_fulltext from v$sqlarea a,
(select * from (select
count(*),
sql_id,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,
CURRENT_FILE# fn,
CURRENT_BLOCK# blockn
from v$active_session_history ash
, all_objects o
where event like 'latch: cache buffers chains'
and o.object_id (+)= ash.CURRENT_OBJ#
group by sql_id, current_obj#, current_file#,
current_block#, o.object_name,o.object_type
order by count(*) desc )where rownum <=15) b
where a.sql_id=b.sql_id;
这是查看造成 latch: cache buffers chains 等待事件的前15条记录。
[color=rgb(86,86,86)] 查看oracle死锁进程并结束死锁
查看锁表进程SQL语句1：
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
查看锁表进程SQL语句2：
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;
杀掉锁表进程：
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK
alter system kill session '738,1429';
用这个可以查：
select s.sid,s.machine,o.object_name,l.oracle_username,l.locked_mode, 'ALTER SYSTEM KILL SESSION '''||s.sid||', '||s.serial#||''';' Command from v$locked_object l,v$session s,all_objects o where l.session_id=s.sid and l.object_id=o.object_id
可以查看哪台机器哪个用户锁了记录, 其中command是用来杀掉锁住记录的session
******************************************************************************************************************
SELECT A.OBJECT_ID, B.OBJECT_NAME, A.SESSION_ID, A.ORACLE_USERNAME, A.OS_USER_NAME, A.PROCESS, A.LOCKED_MODE FROM V$LOCKED_OBJECT A, DBA_OBJECTS B WHERE A.OBJECT_ID = B.OBJECT_ID;
SELECT T2.USERNAME, T2.SID, T2.SERIAL#, T2.LOGON_TIME FROM V$LOCKED_OBJECT T1, V$SESSION T2 WHERE T1.SESSION_ID = T2.SID ORDER BY T2.LOGON_TIME;
ALTER SYSTEM KILL SESSION 'sid, serial#';
********************************************************************************************************************
select V$SESSION.sid,v$session.SERIAL#,v$process.spid,
rtrim(object_type) object_type,rtrim(owner) || '.' || object_name object_name,
decode(lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode(request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode
,ctime, block b,
v$session.username,MACHINE,MODULE,ACTION,
decode(A.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType
from (SELECT * FROM V$LOCK) A, all_objects,V$SESSION,v$process
where A.sid > 6
and object_name<>'OBJ$'
and A.id1 = all_objects.object_id
and A.sid=v$session.sid
and v$process.addr=v$session.paddr;
同样也是通过写sql从数据字典里查出来。
// SELECT-SQL1
// 功能:检查被加锁的对象
//select obj.OWNER||'.'||obj.OBJECT_NAME as OBJ_NAME, // 对象名称(已经被锁住)
// obj.SUBOBJECT_NAME as SUBOBJ_NAME, // 子对象名称(已经被锁住)
// obj.OBJECT_ID as OBJ_ID, // 对象ID
// obj.OBJECT_TYPE as OBJ_TYPE, // 对象类型
// lock_obj.SESSION_ID as SESSION_ID, // 会话SESSION_ID
// lock_obj.ORACLE_USERNAME as ORA_USERNAME, // ORACLE系统用户名称
// lock_obj.OS_USER_NAME as OS_USERNAME, // 操作系统用户名称
// lock_obj.PROCESS as PROCESS // 进程编号
//from
// ( select * from all_objects where object_id in (select object_id from v$locked_object)) obj,
// v$locked_object lock_obj
//where obj.object_id=lock_obj.object_id;
//// SELECT-SQL2
//// 功能:检查被加锁的对象以及加锁的会话信息
//// 如果需要手工解除锁,请对照要解锁的对象,记下SESSION_ID,SERIAL#
//// 项,然后运行下面的ALTER-SQL1
//select LOCK_INFO.OWNER||'.'||LOCK_INFO.OBJ_NAME as OBJ_NAME, // 对象名称(已经被锁住)
// LOCK_INFO.SUBOBJ_NAME as SUBOBJ_NAME, // 子对象名称(已经被锁住)
// SESS_INFO.MACHINE as MACHINE, // 机器名称
// LOCK_INFO.SESSION_ID as SESSION_ID, // 会话SESSION_ID
// SESS_INFO.SERIAL# as SERIAL#, // 会话SERIAL#
// LOCK_INFO.ORA_USERNAME as ORA_USERNAME, // ORACLE系统用户名称
// LOCK_INFO.OS_USERNAME as OS_USERNAME, // 操作系统用户名称
// LOCK_INFO.PROCESS as PROCESS, // 进程编号
// LOCK_INFO.OBJ_ID as OBJ_ID, // 对象ID
// LOCK_INFO.OBJ_TYPE as OBJ_TYPE, // 对象类型
// SESS_INFO.LOGON_TIME as LOGON_TIME, // 登录时间
// SESS_INFO.PROGRAM as PROGRAM, // 程序名称
// SESS_INFO.STATUS as STATUS, // 会话状态
// SESS_INFO.LOCKWAIT as LOCKWAIT, // 等待锁
// SESS_INFO.ACTION as ACTION, // 动作
// SESS_INFO.CLIENT_INFO as CLIENT_INFO // 客户信息
//
//from
// (
// select obj.OWNER as OWNER,
// obj.OBJECT_NAME as OBJ_NAME,
// obj.SUBOBJECT_NAME as SUBOBJ_NAME,
// obj.OBJECT_ID as OBJ_ID,
// obj.OBJECT_TYPE as OBJ_TYPE,
// lock_obj.SESSION_ID as SESSION_ID,
// lock_obj.ORACLE_USERNAME as ORA_USERNAME,
// lock_obj.OS_USER_NAME as OS_USERNAME,
// lock_obj.PROCESS as PROCESS
// from
// ( select * from all_objects where object_id in (select object_id from v$locked_object)) obj,
// v$locked_object lock_obj
// where obj.object_id=lock_obj.object_id
// ) LOCK_INFO,
// (
// select SID,
// SERIAL#,
// LOCKWAIT,
// STATUS,
// PROGRAM,
// ACTION,
// CLIENT_INFO,
// LOGON_TIME,
// MACHINE
// from v$session
// ) SESS_INFO
//where LOCK_INFO.SESSION_ID=SESS_INFO.SID ;
//// 看清楚了 下面就可以 杀死它了。
// ALTER-SQL1
// 功能:杀死会话(SESSION_ID,SERIAL#),可以手工解除锁
// 请手工修改SESSION_ID,SERIAL#为相应值
// 注意:本功能慎重使用,有一定的破坏性,该SQL可以断开客户机和服务器的连接
//ALTER SYSTEM KILL SESSION 'SESSION_ID,SERIAL#';