关于Oracle问题诊断过程常用SQL

–查看临时表空间使用情况
select tablespace_name ,sum(size_mb),sum(used_mb),round(sum(used_mb)/sum(size_mb),2) from (
SELECT
dtf.tablespace_name ，
dtf.status AS “Status”,
nvl(dtf.bytes / 1024 / 1024,0) AS Size_MB,
nvl(t.bytes_used / 1024 / 1024,0) AS Used_MB
FROM
sys.dba_temp_files dtf,
v$temp_extent_pool t
WHERE
dtf.file_id = t.file_id (+)
) group by tablespace_name

–查看当前sql语句执行情况
select t1.USERNAME,t1.SADDR,t1.STATUS,t1.LAST_CALL_ET,t2.SQL_TEXT,t1.EVENT,t3.ELAPSED_TIME from v$session t1,v$sql t2,v$sqlstats t3 where t1.SQL_ID = t2.SQL_ID and
status =’ACTIVE’ and t1.sql_id=t3.sql_id

–查看当前sql制定情况plus版
SELECT
s.username 用户名称,
s.status 状态,
s.machine 机器名称,
s.osuser 操作系统用户名称,
spid unix进程号,
‘kill -9 ‘ || p.spid unix级断开连接,
‘alter system kill session ‘ || ”” || s.sid || ‘,’|| s.serial#|| ”’;’ oracle级断开连接,
TO_CHAR(s.logon_time,’dd/mm/yyyy hh24:mi:ss’) 登陆时间,
s.last_call_et 空闲时间秒,
s.module 模块,
t.sql_text
FROM
v$session s,
v$process p,
v$sql t
WHERE
s.type = ‘USER’
AND p.addr = s.paddr
AND status != ‘KILLED’
AND s.sql_address = t.address
AND s.sql_hash_value = t.hash_value
AND s.last_call_et > 60 * 60 * 1
ORDER BY
s.last_call_et DESC