Oracle 查看实时运行的sql

– 查询实时运行的语句,etime单位是秒 3867
SELECT ‘ps -ef|grep ’ || SPID PS,
/* ‘alter system KILL session ‘’’||B.sid||’, ‘||B.serial#||’’’ immediate;’ KILL_SESSION,/
B.INST_ID,
B.SID,
B.SERIAL#,
B.USERNAME,
A.SQL_ID,
B.EVENT,
/ROUND(B.LAST_CALL_ET / 3600) “SES_T(小时)”,/
TRUNC(((A.ELAPSED_TIME / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)) /
1000000),
2) “ETIME”,
ROUND(A.BUFFER_GETS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) PER_BU,
A.EXECUTIONS,
A.SQL_FULLTEXT FULLSQL,
A.SQL_TEXT,
A.DISK_READS,
A.BUFFER_GETS,
B.OSUSER,
B.MACHINE,
B.PROGRAM,
A.MODULE,
A.CPU_TIME,
A.LAST_LOAD_TIME,
A.LAST_ACTIVE_TIME
FROM GV$SQLAREAA,GV$SESSION B, GV$PROCESS P
WHERE EXECUTIONS >= 0
AND B.STATUS = ‘ACTIVE’
AND A.HASH_VALUE = B.SQL_HASH_VALUE
AND A.SQL_ID = B.SQL_ID
AND B.PADDR = P.ADDR
/ AND B.USERNAME=‘SYSTEM’
AND B.EVENT=‘ASM file metadata operation’*/
ORDER BY (CPU_TIME / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)) DESC,
A.BUFFER_GETS DESC,
A.EXECUTIONS DESC,
A.SQL_ID;

select t.blocking_session,t.* from Gv$session t where t.blocking_session is not null;

–blocking_session 就是锁，在 session 查看状态就可以知道是什么情况引起的。

–alter system kill session ‘sid,serial#’ immediate

select a.type,a.owner,b.SID,b.SERIAL#,b.OSUSER,b.MACHINE,b.program,b.module
from dba_ddl_locks a,v$session b where a.session_id = b.SID and a.name = ‘SUMMARY_SPC_PKG’;

– 查看表空间
select tablespace_name ,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

select tablespace_name ,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

2020-08-18 08:44
select ‘alter system disconnect session ‘’’||s.sid||’, ‘||s.serial#||’’’ immediate;’ KILL_SESSION,
s.inst_id,s.event, s.last_call_et, a.SQL_TEXT, s.MACHINE, s.OSUSER,
s.PROGRAM, s.module, s.action, s.USERNAME, a.SQL_ID, a.CHILD_NUMBER, s.LOGON_TIME,
s.BLOCKING_SESSION,s.blocking_instance,s.service_name,
a.SQL_FULLTEXT
from gv$sessions,gv$sql a
where s.SQL_ID = a.sql_id(+)
and s.sql_child_number = a.child_number(+)
and s.STATUS = ‘ACTIVE’
– and s.type = ‘USER’
and s.username <> ‘SYS’
– and s.username =‘CDH_QUERY’
and s.last_call_et > 10
– and s.event=‘enq: TX - row lock contention’
and s.inst_id= a.inst_id
order by s.last_call_et desc;

知道堵塞别人的sid后，查看其session，查出serial#
select * from gv$session where sid=2852 and inst_id=2
然后去对应的实例上，alter system disconnect session ‘sid,serial#’ immediate;

–查看当前DB锁的表
select s.sid,
s.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name as table_locked_name,
s.username,
s.schemaname,
s.osuser,
s.process,
s.machine,
s.terminal,
lo.locked_mode
from v$locke{d}_{o}bjectlo,al{l}_{o}bjectsao,v$session s
where ao.object_id = lo.object_id
and lo.session_id = s.sid
– and lo.oracle_username =‘RPT’
order by s.sid asc;

–alter system kill session ‘sid,serial#’ immediate

–user表空间点检
select a.tablespace_name,a.total - b.free as USED, a.total, b.free as free_M,
round(b.free * 100 / a.total, 2) as free_pct
from (select tablespace_name, sum(bytes) / 1048576 as total
from dba_data_files
group by tablespace_name) a left join
(select tablespace_name, sum(bytes) / 1048576 as free
from dba_free_space
group by tablespace_name) b
on a.tablespace_name = b.tablespace_name
order by 5;

select * from DBA_TABLESPACE_USAGE_METRICS;