sql 代码
- ----杀掉oracle进程的方法----
- 1.通常可以使用以下命令Kill进程:
- alter system kill session 'sid,serial#';
- 2.
- select logon_time,username,action,program,terminal,machine,userfrom v$session where terminal = 'CZYS'
- select spid from v$process where addr in
- (select paddr from v$session where terminal ='CZYS'
- andlogon_time <= to_date('2006-2-17 16:28:56','yyyy-mm-ddhh24:mi:ss'));
- -------------------------------
- 为了更快速的释放资源,通常我们使用如下步骤来Kill进程:
- 1.首先在操作系统级kill进程
- 2.在数据库内部kill session
- 这样通常可以快速中止进程,释放资源。
- 首先查询得到该session对应的OS进程号:
- SQL> select 'kill -9 '||spid from v$process where addr = (selectpaddr from v$session where sid=&sid);
- Enter value for sid: 154
- old 1: select 'kill -9 '||spidfrom v$process where addr = (select paddr from v$session wheresid=&sid)
- new 1: select 'kill -9 '||spidfrom v$process where addr = (select paddr from v$session wheresid=154)
- 'KILL-9'||SPID
- --------------------
- kill -9 22702
- SQL> !
- 在操作系统级kill该进程:
- [oracle@danaly ~]$ ps -ef|grep 22702
- oracle 22702 1 0 Oct25? 00:00:02 oracledanaly (LOCAL=NO)
- oracle 1208212063 0 11:12pts/1 00:00:00 grep 22702
- [oracle@danaly ~]$ kill -9 22702
- [oracle@danaly ~]$ ps -ef|grep 22702
- oracle 1208812063 0 11:12pts/1 00:00:00 grep 22702
- [oracle@danaly ~]$ exit
- exit
- SQL> select sid,username,status from v$session;
- SIDUSERNAME STATUS
- ---------- ------------------------------ --------
- ...
- 154SCOTT KILLED
- ...
- 30 rows selected.
- SQL> select sid,serial#,username from v$session wheresid=154;
- SID SERIAL#USERNAME
- ---------- ---------- ------------------------------
- 154 56090 SCOTT
- 再次在数据库中kill该session,并指定immediate选项:
- SQL> alter system kill session '154,56090' immediate;
- System altered.
- SQL> select sid,serial#,username from v$session wheresid=154;
- no rows selected
- 此时该进程被迅速清除。
- --------------------------------------------------------
- 查找锁定的session
- SELECT sn.username,m.SID,sn.SERIAL#, m.TYPE,
- DECODE (m.lmode,
- 0, 'None',
- 1, 'Null',
- 2, 'Row Share',
- 3, 'Row Excl.',
- 4, 'Share',
- 5, 'S/Row Excl.',
- 6, 'Exclusive',
- lmode, LTRIM (TO_CHAR (lmode, '990'))
- ) lmode,
- DECODE (m.request,
- 0, 'None',
- 1, 'Null',
- 2, 'Row Share',
- 3, 'Row Excl.',
- 4, 'Share',
- 5, 'S/Row Excl.',
- 6, 'Exclusive',
- request, LTRIM (TO_CHAR (m.request, '990'))
- ) request,
- m.id1, m.id2
- FROMv$session sn, v$lock m
- WHERE (sn.SID = m.SID ANDm.request !=0) --存在锁请求,即被阻塞
- OR ( sn.SID =m.SID --不存在锁请求,但是锁定的对象被其他会话请求锁定
- AND m.request = 0
- AND lmode != 4
- AND (id1, id2) IN (
- SELECT s.id1, s.id2
- FROM v$lock s
- WHERE request != 0 AND s.id1 = m.id1
- AND s.id2 = m.id2)
- )
- ORDER BY id1, id2, m.request;
- --------------------------------------------------------------
- 通过数据字典V$SESSION与V$LOCK来了解正在等待锁资源的用户
- select a.username,a.sid,a.serial#,b.id1 from v$session a ,v$lockb
- Where a.lockwait = b.kaddr;
- 了解锁住其他用户的用户进程,方法如下:
- select a.username,a.sid,a.serial#,b.id1 from v$session a,v$lockb
- Where b.id1 in
- (select distinct e.id1 from v$session d,v$lock e where d.lockwait =e.kaddr)
- And a.sid = b.sid and b.request =0;