----检查那个表被锁
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode
from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
----查看哪条SQL语句导致死锁
select a.username username, a.sid sid, a.serial# serial,b.id1 id1, c.sql_text sqltext
from v$session a, v$lock b, v$sqltext c
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 c.hash_value = a.sql_hash_value
and b.request = 0;
----解锁
alter system kill session 'sess.sid,sess.serial#';
alter system kill session '999,9817';
----用户解锁
login as: ubuntu
ubuntu@192.168.2.10's password:
Linux bsw-oracle 2.6.24-23-virtual #1 SMP Wed Apr 1 22:56:35 UTC 2009 i686
The programs included with the Ubuntu system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Ubuntu comes with ABSOLUTELY NO WARRANTY, to the extent permitted by
applicable law.
To access official Ubuntu documentation, please visit:
http://help.ubuntu.com/
Last login: Thu Sep 16 23:17:49 2010 from 192.168.2.51
ubuntu@bsw-oracle:~$ sudo -s
[sudo] password for ubuntu:
root@bsw-oracle:~# su - oracle
Your account has expired; please contact your system administrator
su: User account has expired
(Ignored)
oracle@bsw-oracle:~$ export ORACLE_SID=orcl
oracle@bsw-oracle:~$ sqlplus '/as sysdba' --/"as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Sep 16 23:22:12 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER USER "JPTASTEMP" ACCOUNT UNLOCK;
User altered.
