关于会话、进程、请求的几个常用SQL

1.检查自己的SID

SELECT sid FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

2. 几个ID之间的关系

SELECT s.sid session_id, p.spid os_process_id, p.pid oracle_process_id
  FROM v$process p, v$session s
 WHERE p.addr = s.paddr;

3.杀死Session和进程

SELECT s.sid session_id,
       p.spid os_process_id,
       p.pid oracle_process_id,
       'alter system kill session ''' || to_char(s.sid) || ',' || to_char(s.serial#) || ''' immediate;' kill_db_session,
       'kill -9 ' || p.spid kill_os_session
  FROM v$process p, v$session s
 WHERE p.addr = s.paddr
   AND s.sid = &sid;
   

4.正在执行的SQL

   SELECT sql_text
  FROM v$sqltext_with_newlines sqlt, v$session s
 WHERE sqlt.address = s.sql_address
   AND sqlt.hash_value = s.sql_hash_value
   AND s.sid = &session_id
 ORDER BY piece

5.引用对象的Session,锁表session

 SELECT acc.*,
       'alter system kill session ''' || to_char(ses.sid) || ',' || to_char(ses.serial#) ||
       ''' immediate'
  FROM v$access acc, v$session ses
 WHERE acc.OBJECT LIKE upper('TABLE_NAME%')
   AND acc.sid = ses.sid;

6. 请求的 Session和SQL

<span style="font-size:18px;">SELECT to_char(s.sid) || ',' || to_char(s.serial#), sql_text
  FROM applsys.fnd_concurrent_requests r,
       v$process                       p,
       v$session                       s,
       v$sqltext_with_newlines         sqlt
 WHERE r.oracle_process_id = p.spid
   AND p.addr = s.paddr(+)
   AND s.sql_address = sqlt.address(+)
   AND s.sql_hash_value = sqlt.hash_value(+)
   AND r.request_id = &request_id
 ORDER BY piece;
</span>