查询被锁表和机器信息
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;
查询表是否被锁
SELECT sid
FROM v$lock
WHERE id1 = (
SELECT object_id
FROM user_objects
WHERE object_Name = '替换成表名')
AND request = 0;
查询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 A.serial#,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查询当前连接的用户和执行的SQL
SELECT 'alter system kill session ''' || SE.sid || ',' || SE.serial#||''';',
SE.sid,SE.serial#,
PR.spid,SE.STATUS,SUBSTR(SE.program,1,20) PROG,SUBSTR(SE.machine,1,30) MACH,SQ.sql_text FROM v$SESSION SE,v$sqlarea SQ,v$process PR WHERE SE.paddr=PR.ADDR (+) AND SE.sql_address=SQ.address (+) AND schemaname<> 'SYS' ORDER BY SE.sid;
查询oracle数据库占用资源比较大
Select b.USERNAME,
b.SID,
a.SQL_ID,
a.SQL_TEXT,
a.sql_fulltext,
b.EVENT,
a.executions,
trunc(((decode(a.EXECUTIONS,0,0,a.cpu_time / a.executions)) / 10000)) c_time, ---单位零点秒
trunc(((decode(a.EXECUTIONS,0,0,a.ELAPSED_TIME / a.executions)) / 10000)) e_time,
trunc(cpu_time/10000) cpu_time,
trunc(a.ELAPSED_TIME/10000) ELAPSED_TIME ,
a.DISK_READS,
a.BUFFER_GETS,
b.MACHINE,
b.PROGRAM
From v$sqlarea a, v$session b
Where executions > =0
And b.status = 'ACTIVE'
and a.SQL_ID = b.SQL_ID
-- and b.USERNAME='DB_WTDZ'
and trunc(((a.cpu_time / a.executions) / 1000000))>5