查看系统等待事件和ＳＱＬ的语句

--查看系统等待事件
select c.spid,a.program,a.sid,a.serial#,a.username,     
          b.event,b.state,b.wait_time,b.seconds_in_wait 
   from v$session a,v$session_wait b, v$process c       
   where status='ACTIVE' and a.sid=b.sid                
     and a.paddr=c.addr                                 
   and a.username is not null                           
  order by b.event    ;

--当前正在执行的ＳＱＬ语句
select b.sql_text ,A.SID
  from v$session a,v$sqlarea b
  where a.sql_hash_value=b.hash_value  and a.USERNAME='SCFUSER' AND A.STATUS='ACTIVE';

select instance_name ,status from v$instance;

--get sql 
SELECT /*+ ORDERED */
 sql_text
  FROM v$sqltext 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 = '&pid'))
 ORDER BY piece ASC ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8102208/viewspace-604962/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8102208/viewspace-604962/