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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!