oracle如何kill锁中的sid,ora-00031:session marked for kill处理oracle中杀不掉的锁一例

最新推荐文章于 2021-09-07 13:39:29 发布

看来我摸

最新推荐文章于 2021-09-07 13:39:29 发布

阅读量368

点赞数

文章标签： oracle如何kill锁中的sid

SQL> col user_name format a10

SQL> col object_name format a20

SQL> set linesize 120

SQL> l

1 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,

2 o.owner,o.object_name,o.object_type,s.sid,s.serial#

3 FROM v$locked_object l,dba_objects o,v$session s

4 WHERE l.object_id=o.object_id

5 AND l.session_id=s.sid

6* ORDER BY o.object_id,xidusn DESC

SQL> /

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

SYS WRH$_SQLTEXT TABLE 201 15652

SYS WRH$_SQL_PLAN TABLE 201 15652

SYS WRH$_SQL_BIND_METADA TABLE 201 15652

SYS WRH$_OPTIMIZER_ENV TABLE 201 15652

SYS WRH$_ACTIVE_SESSION_ TABLE 197 10300

HISTORY

SYS WRM$_DATABASE_INSTAN TABLE 197 10300

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample FA TABLE 543 7064

sample sample FB TABLE 543 7064

sample sample FB TABLE 542 12037

sample sample FBOOKNO TABLE 543 7064

sample sample FC TABLE 543 7064

sample sample FD TABLE 543 7064

sample sample FKM TABLE 543 7064

sample sample FPZCODE TABLE 543 7064

sample sample TEMP_sample TABLE 511 52168

sample sample TEMP_sample TABLE 477 2076

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_sample TABLE 481 2089

sample sample TEMP_sample TABLE 543 7064

sample sample TEMP_sample TABLE 469 831

sample sample TEMP_sample TABLE 510 23890

sample sample TEMP_sample TABLE 466 30325

sample sample TEMP_sample TABLE 250 32922

sample sample TEMP_sample TABLE 204 25745

sample sample TEMP_sample TABLE 474 51576

sample sample TEMP_sample TABLE 187 60239

sample sample TEMP_sample TABLE 193 9253

sample sample TEMP_sample TABLE 217 51691

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_sample TABLE 500 38620

sample sample TEMP_sample TABLE 475 27828

sample sample TEMP_sample TABLE 548 50503

sample sample TEMP_OCID TABLE 511 52168

sample sample TEMP_OCID TABLE 214 45665

sample sample TEMP_OCID TABLE 477 2076

sample sample TEMP_OCID TABLE 481 2089

sample sample TEMP_OCID TABLE 523 50248

sample sample TEMP_OCID TABLE 209 43981

sample sample TEMP_OCID TABLE 192 38700

sample sample TEMP_OCID TABLE 531 54851

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_OCID TABLE 526 23682

sample sample TEMP_OCID TABLE 469 831

sample sample TEMP_OCID TABLE 510 23890

sample sample TEMP_OCID TABLE 466 30325

sample sample TEMP_OCID TABLE 250 32922

sample sample TEMP_OCID TABLE 204 25745

sample sample TEMP_OCID TABLE 240 38332

sample sample TEMP_OCID TABLE 474 51576

sample sample TEMP_OCID TABLE 187 60239

sample sample TEMP_OCID TABLE 193 9253

sample sample TEMP_OCID TABLE 181 59811

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_OCID TABLE 180 32018

sample sample TEMP_OCID TABLE 217 51691

sample sample TEMP_OCID TABLE 500 38620

sample sample TEMP_OCID TABLE 475 27828

sample sample TEMP_OCID TABLE 519 36884

sample sample TEMP_OCID TABLE 211 60007

sample sample TEMP_OCID TABLE 232 26240

sample sample TEMP_SMALL2 TABLE 507 27955

sample sample MLOG$_FB TABLE 542 12037

sample sample MLOG$_FKM TABLE 543 7064

sample sample MLOG$_FPZCODE TABLE 543 7064

60 rows selected.

SQL> select spid, osuser, s.program

2 from v$session s,v$process p

3 where s.paddr=p.addr and s.sid=543;

SPID OSUSER PROGRAM

------------ ------------------------------ ------------------------------------------------

13225 Administrator hffas.exe

SQL> !kill -9 13225

SQL> !ps -ef|grep 13225

oracle 16517 15689 1 16:08:28 pts/0 0:00 /sbin/sh -c ps -ef|grep 13225

oracle 16519 16517 0 16:08:28 pts/0 0:00 grep 13225

SQL> l

1 select spid, osuser, s.program

2 from v$session s,v$process p

3* where s.paddr=p.addr and s.sid=543

SQL> /

SPID OSUSER PROGRAM

------------ ------------------------------ ------------------------------------------------

13225 Administrator hffas.exe

SQL> !

$ su - root

nodehp2#

nodehp2#kill -9 13225

kill: 13225: The specified process does not exist.

nodehp2#ps -ef|grep 13225

root 16822 16660 0 16:09:22 pts/0 0:00 grep 13225

nodehp2#exit

logout root

$ ps -ef|grep 13225

oracle 16890 16638 1 16:09:33 pts/0 0:00 grep 13225

$ exit

SQL> l

1 select spid, osuser, s.program

2 from v$session s,v$process p

3* where s.paddr=p.addr and s.sid=543

SQL> /

no rows selected

SQL> SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,

o 2 .owner,o.object_name,o.object_type,s.sid,s.serial#

3 FROM v$locked_object l,dba_objects o,v$session s

W 4 HERE l.object_id=o.object_id

A 5 ND l.session_id=s.sid

6 ORDER BY o.object_id,xidusn DESC ;

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

SYS WRH$_SQLTEXT TABLE 201 15652

SYS WRH$_SQL_PLAN TABLE 201 15652

SYS WRH$_SQL_BIND_METADA TABLE 201 15652

SYS WRH$_OPTIMIZER_ENV TABLE 201 15652

SYS WRH$_ACTIVE_SESSION_ TABLE 197 10300

HISTORY

SYS WRM$_DATABASE_INSTAN TABLE 197 10300

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample FB TABLE 542 12037

sample sample TEMP_sample TABLE 511 52168

sample sample TEMP_sample TABLE 477 2076

sample sample TEMP_sample TABLE 481 2089

sample sample TEMP_sample TABLE 469 831

sample sample TEMP_sample TABLE 510 23890

sample sample TEMP_sample TABLE 466 30325

sample sample TEMP_sample TABLE 250 32922

sample sample TEMP_sample TABLE 204 25745

sample sample TEMP_sample TABLE 474 51576

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_sample TABLE 170 95

sample sample TEMP_sample TABLE 187 60239

sample sample TEMP_sample TABLE 217 51691

sample sample TEMP_sample TABLE 500 38620

sample sample TEMP_sample TABLE 475 27828

sample sample TEMP_sample TABLE 548 50503

sample sample TEMP_OCID TABLE 511 52168

sample sample TEMP_OCID TABLE 214 45665

sample sample TEMP_OCID TABLE 477 2076

sample sample TEMP_OCID TABLE 481 2089

sample sample TEMP_OCID TABLE 523 50248

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_OCID TABLE 209 43981

sample sample TEMP_OCID TABLE 192 38700

sample sample TEMP_OCID TABLE 531 54851

sample sample TEMP_OCID TABLE 526 23682

sample sample TEMP_OCID TABLE 469 831

sample sample TEMP_OCID TABLE 510 23890

sample sample TEMP_OCID TABLE 466 30325

sample sample TEMP_OCID TABLE 250 32922

sample sample TEMP_OCID TABLE 204 25745

sample sample TEMP_OCID TABLE 240 38332

sample sample TEMP_OCID TABLE 474 51576

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_OCID TABLE 187 60239

sample sample TEMP_OCID TABLE 181 59811

sample sample TEMP_OCID TABLE 180 32018

sample sample TEMP_OCID TABLE 217 51691

sample sample TEMP_OCID TABLE 500 38620

sample sample TEMP_OCID TABLE 475 27828

sample sample TEMP_OCID TABLE 519 36884

sample sample TEMP_OCID TABLE 211 60007

sample sample TEMP_OCID TABLE 232 26240

sample sample TEMP_SMALL2 TABLE 507 27955

sample sample MLOG$_FB TABLE 542 12037

49 rows selected.

SQL> select spid, osuser, s.program

2 from v$session s,v$process p

3 where s.paddr=p.addr and s.sid=542;

SPID OSUSER PROGRAM

------------ ------------------------------ ------------------------------------------------

7944 Administrator hffas.exe

SQL> !

$ ps -ef|grep 7944

oracle 7944 1 0 Jan 8 ? 0:14 oraclesample2 (LOCAL=NO)

oracle 17672 17630 0 16:11:52 pts/0 0:00 grep 7944

$ kill -9 7944

$ ps -ef|grep 7944

oracle 17877 17630 0 16:12:21 pts/0 0:00 grep 7944

$ exit

SQL> l

1 select spid, osuser, s.program

2 from v$session s,v$process p

3* where s.paddr=p.addr and s.sid=542

SQL> /

no rows selected

SQL> SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,

2 o.owner,o.object_name,o.object_type,s.sid,s.serial#

3 FROM v$locked_object l,dba_objects o,v$session s

4 WHERE l.object_id=o.object_id

A 5 ND l.session_id=s.sid

6 ORDER BY o.object_id,xidusn DESC ;

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

SYS WRH$_SQLTEXT TABLE 201 15652

SYS WRH$_SQL_PLAN TABLE 201 15652

SYS WRH$_SQL_BIND_METADA TABLE 201 15652

SYS WRH$_OPTIMIZER_ENV TABLE 201 15652

SYS WRH$_ACTIVE_SESSION_ TABLE 197 10300

HISTORY

SYS WRM$_DATABASE_INSTAN TABLE 197 10300

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_sample TABLE 511 52168

sample sample TEMP_sample TABLE 477 2076

sample sample TEMP_sample TABLE 481 2089

sample sample TEMP_sample TABLE 469 831

sample sample TEMP_sample TABLE 510 23890

sample sample TEMP_sample TABLE 466 30325

sample sample TEMP_sample TABLE 250 32922

sample sample TEMP_sample TABLE 204 25745

sample sample TEMP_sample TABLE 474 51576

sample sample TEMP_sample TABLE 170 95

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_sample TABLE 187 60239

sample sample TEMP_sample TABLE 217 51691

sample sample TEMP_sample TABLE 500 38620

sample sample TEMP_sample TABLE 475 27828

sample sample TEMP_OCID TABLE 511 52168

sample sample TEMP_OCID TABLE 214 45665

sample sample TEMP_OCID TABLE 477 2076

sample sample TEMP_OCID TABLE 481 2089

sample sample TEMP_OCID TABLE 209 43981

sample sample TEMP_OCID TABLE 192 38700

sample sample TEMP_OCID TABLE 531 54851

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_OCID TABLE 526 23682

sample sample TEMP_OCID TABLE 469 831

sample sample TEMP_OCID TABLE 548 50503

sample sample TEMP_OCID TABLE 510 23890

sample sample TEMP_OCID TABLE 466 30325

sample sample TEMP_OCID TABLE 250 32922

sample sample TEMP_OCID TABLE 523 50261

sample sample TEMP_OCID TABLE 204 25745

sample sample TEMP_OCID TABLE 240 38332

sample sample TEMP_OCID TABLE 474 51576

sample sample TEMP_OCID TABLE 187 60239

USER_NAME OWNER OBJECT_NAME OBJECT_TYPE SID SERIAL#

---------- ------------------------------ -------------------- ------------------- ---------- ----------

sample sample TEMP_OCID TABLE 181 59811

sample sample TEMP_OCID TABLE 180 32018

sample sample TEMP_OCID TABLE 217 51691

sample sample TEMP_OCID TABLE 500 38620

sample sample TEMP_OCID TABLE 475 27828

sample sample TEMP_OCID TABLE 519 36884

sample sample TEMP_OCID TABLE 211 60007

sample sample TEMP_OCID TABLE 232 26240

sample sample TEMP_OCID TABLE 228 21753

sample sample TEMP_SMALL2 TABLE 507 27955

48 rows selected.

SQL>

看来我摸

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle如何kill锁中的sid,ora-00031:session marked for kill处理oracle中杀不掉的锁一例

SQL> col user_name format a10SQL> col object_name format a20SQL> set linesize 120SQL> l1 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,2 o.owner,o.obj...
复制链接

扫一扫