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
TA
SYS WRH$_OPTIMIZER_ENV TABLE 201 15652
SYS WRH$_ACTIVE_SESSION_ TABLE 197 10300
HISTORY
SYS WRM$_DATABASE_INSTAN TABLE 197 10300
CE
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
TA
SYS WRH$_OPTIMIZER_ENV TABLE 201 15652
SYS WRH$_ACTIVE_SESSION_ TABLE 197 10300
HISTORY
SYS WRM$_DATABASE_INSTAN TABLE 197 10300
CE
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
TA
SYS WRH$_OPTIMIZER_ENV TABLE 201 15652
SYS WRH$_ACTIVE_SESSION_ TABLE 197 10300
HISTORY
SYS WRM$_DATABASE_INSTAN TABLE 197 10300
CE
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>
