常用处理方式如下：
先查看哪些表被锁住了
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
OWNER OBJECT_NAME SESSION_ID LOCKED_MODE
------------------------------ -------------------------------------- -------
WSSB SBDA_PSHPFTDT 22 3
WSSB_RTREPOS WB_RT_SERVICE_QUEUE_TAB 24 2
WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB 29 2
WSSB_RTREPOS WB_RT_NOTIFY_QUEUE_TAB 39 2
WSSB SBDA_PSDBDT 47 3
WSSB_RTREPOS WB_RT_AUDIT_DETAIL 47 3
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
USERNAME SID SERIAL# LOGON_TIME
------------------------------ ---------- ---------- -----------
WSSB_RTACCESS 39 1178 2006-5-22 1
WSSB_RTACCESS 29 5497 2006-5-22 1
杀会话
alter system kill session 'sid,serial#';
e.g
alter system kill session '29,5497';
如果有ora-00031错误，则在后面加immediate;
alter system kill session '29,5497' immediate;
-------------
１.查哪个过程被锁
查V$DB_OBJECT_CACHE视图:
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND CLOCKS!='0';
2. 查是哪一个SID,通过SID可知道是哪个SESSION.
查V$ACCESS视图:
SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';
3. 查出SID和SERIAL#
查V$SESSION视图:
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'
查V$PROCESS视图:
SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
4. 杀进程
(1).先杀ORACLE进程:
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';
(2).再杀操作系统进程:
KILL -9 刚才查出的SPID
或
ORAKILL 刚才查出的SID 刚才查出的SPID
------------------
oracle的死锁
查询数据库死锁
select t2.username||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_text
from v$locked_object t1,v$session t2,v$sqltext t3
where t1.session_id=t2.sid
and t2.sql_address=t3.address
order by t2.logon_time;
查询出来的结果就是有死锁的session了，
下面就是杀掉
拿到上面查询出来的SID和SERIAL#，填入到下面的语句中
alter system kill session 'sid,serial#';
一般情况可以解决数据库存在的死锁了，
或通过session id 查到对应的操作系统进程，在unix中杀掉操作系统的进程。
SELECT a.username,c.spid AS os_process_id,c.pid AS oracle_process_id FROM v$session a,v$process c
WHERE c.addr=a.paddr and a.sid= and a.serial#= ;
然后采用kill （unix） 或 orakill（windows ）
在unix中
ps -ef|grep os_process_id
kill -9 os_process_id
ps -ef|grep os_process_id
----
经常在oracle的使用过程中碰到这个问题，所以也总结了一点解决方法：）
1）查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
2）kill掉这个死锁的进程：
alter system kill session ‘sid,serial#’; （其中sid=l.session_id）
3）如果还不能解决，
select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
其中sid用死锁的sid替换。
exit
ps -ef|grep spid
其中spid是这个进程的进程号，kill掉这个Oracle进程。
经过以下处理，对于释放服务器资源，效果非常明显
--查询死锁的进程
select 'ALTER SYSTEM KILL SESSION '''||b.sid||', '||b.serial#||''';'
from v$access a,v$session b
where a.SID=b.SID
and b.program like 'dis%'
group by b.sid,b.serial#
--查询SID对应的操作系统进程
select spid, osuser, s.program from v$session s,v$process p where
s.paddr=p.addr and s.sid in (54,82,500,195,303,492,496)
--查看操作系统进程
ps -ef | more
--从操作系统杀掉会话不能杀的进程
kill -9 操作系统进程号
一般再pl/sql中常用：kill掉这个死锁锁表的进程：
alter system kill session ‘sid,serial#’;
alter system kill session ‘23,678234’;
不行的时候用：
alter system kill session ‘233,1231421’ immediate;
（其中sid=l.session_id）
在ORACLE数据库当中，有时候会使用ALTER SYSTEM KILL SESSION 'sid,serial#'杀掉一个会话进程，但是使用这个SQL语句杀掉会话后，数据库并不会立即释放掉相关的资源，有时候你会发现锁定的资源很长时间也不会释放，即使会话状态为“KILLED”，依然会阻塞其它会话。
下面根据Eygel的"Oracle中Kill session的研究"，构造一个案例看看kill session到底做了什么。如下所示
会话1：
SQL> conn etl/etl
Connected.
SQL> update test set status='invalid';
55944 rows updated.
SQL> update test2 set dropped='Y';
3090 rows updated.
会话2：
SQL> show user
USER is "SYS"
SQL> update etl.test2 set dropped='Y';
3090 rows updated.
会话3
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025F1D1FC8 ETL INACTIVE
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025F1D5F08 SYS ACTIVE
SQL> alter system kill session '27,33353';
System altered.
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025F1D5F08 SYS INACTIVE

如下所示，我杀掉了其中两个会话后，这两个会话的地址都变为000000025C21A0B0了（请见PADDR列）。当在Oracle中kill session以后, Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.此时v$process和v$session失去关联,进程就此中断。 然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间. 如果此时被Kill的process，重新尝试执行任务，那么马上会收到进程中断的提示，process退出，此时Oracle会立即启动PMON 来清除该session.这被作为一次异常中断处理.

SQL> alter system kill session '63,54311';
System altered.
SQL> select saddr,sid,serial#,paddr,username,status
from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED
我们根据下面SQL找到进程的地址，然后在v$process里面找到对应的spid,然后从操作系统中杀掉该进程。
SQL> select p.addr from v$process p where pid <> 1
2 minus
3 select s.paddr from v$session s;
ADDR
----------------
000000025F1D1FC8
000000025F1D5F08
SQL> select saddr,sid,serial#,paddr,username,status from v$session
where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C233B00 27 33353 000000025C21A0B0 ETL KILLED
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
000000025C24BC50 63 54311 000000025C21A0B0 SYS KILLED
SQL> select addr, pid, spid, username from v$process where addr in ('000000025F1D1FC8','000000025F1D5F08');
ADDR PID SPID USERNAME
---------------- ---------- ------------ ---------------
000000025F1D1FC8 22 12959 oracle
000000025F1D5F08 38 12971 oracle
SQL> ! kill -9 12959
SQL> ! kill -9 12971
SQL> select saddr,sid,serial#,paddr,username,status
from v$session where username =upper('etl') or username =upper('sys');
SADDR SID SERIAL# PADDR USERNAME STATUS
---------------- ---------- ---------- ---------------- ------------------------------ --------
000000025C23A608 37 11448 000000025F1D27B0 SYS ACTIVE
在ORACLE数据库杀掉会话进程有三种方式：
1： ALTER SYSTEM KILL SESSION
关于KILL SESSION Clause ，如下官方文档描述所示，alter system kill session实际上不是真正的杀死会话，它只是将会话标记为终止。等待PMON进程来清除会话。
The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open. Your session and the session to be terminated must be on the same instance unless you specify integer3.You must identify the session with the following values from the V$SESSION view:
For integer1, specify the value of the SID column.
For integer2, specify the value of the SERIAL# column.
For the optional integer3, specify the ID of the instance where the target session to be killed exists. You can find the instance ID by querying the GV$ tables.
If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.
Whether or not the session has an ongoing transaction, Oracle Database does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been terminated.
可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE 来快速回滚事物、释放会话的相关锁、立即返回当前会话的控制权。
Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.
2： ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM DISCONNECT SESSION 杀掉专用服务器(DEDICATED SERVER)或共享服务器的连接会话，它等价于从操作系统杀掉进程。它有两个选项POST_TRANSACTION和IMMEDIATE， 其中POST_TRANSACTION表示等待事务完成后断开会话，IMMEDIATE表示中断会话，立即回滚事务。
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
3： KILL -9 SPID （Linux） 或 orakill ORACLE_SID spid （Windows）
可以使用下面SQL语句找到对应的操作系统进程SPID，然后杀掉。当然杀掉操作系统进程是一件危险的事情，尤其不要误杀。所以在执行前，一定要谨慎确认。
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
在数据库如果要彻底杀掉一个会话，尤其是大事务会话，最好是使用ALTER SYSTEM DISCONNECT SESSION IMMEDIATE或使用下面步骤：
1：首先在操作系统级别Kill掉进程。
2：在数据库内部KILL SESSION
或者反过来亦可。这样可以快速终止进程，释放资源。
参考资料：