oracle 怎么锁用户不存在,oracle 锁问题的解决

可以用Spotlight软件对数据库的运行状态进行监控。

当出现session锁时，我们要及时进行处理.

1. 查看哪些session锁:

SQL语句：select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);

SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'

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

alter system kill session '132,731';

alter system kill session '275,15205';

alter system kill session '308,206';

alter system kill session '407,3510';

2. 查看session锁.

sql语句：select s.sid, q.sql_text from v$sqltext q, v$session s

where q.address = s.sql_address

and s.sid = &sid

order by piece;

SQL> select s.sid,q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid in (select sid from v$lock where block = 1) order by piece;

SID SQL_TEXT

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

77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED

77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON

77 E=9 WHERE PROFILE_USER.ID=:34

3 rows selected.

3. kill锁的进程.

SQL语句：alter system kill session '77,22198';

SQL> alter system kill session '391,48398';

System altered.

4. 查看谁锁了谁。

select s1.username || [email='@']'@'[/email] || s1.machine

|| ' ( SID=' || s1.sid || ' )  is blocking '

|| s2.username || [email='@']'@'[/email] || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$session s2

where s1.sid=l1.sid and s2.sid=l2.sid

and l1.BLOCK=1 and l2.request > 0

and l1.id1 = l2.id1

and l2.id2 = l2.id2 ;

注：

> ：重定向输出，将文件的标准输出重新定向输出到文件，或将数据文件作为另一程序的标准输入内容。

|：UNIX管道：将一文件的输出作为另一文件的输入.

在执行SQL语句试：alter system kill session '391,48398'(sid为391); 应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill.

------------------------------------------------------csdn 网友的SQL-------------------------------

SELECTsn.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.id2FROMv$session sn, v$lock mWHERE(sn.SID=m.SIDANDm.request!=0)--存在锁请求，即被阻塞OR(    sn.SID=m.SID--不存在锁请求，但是锁定的对象被其他会话请求锁定ANDm.request=0ANDlmode!=4AND(id1, id2)IN(SELECTs.id1, s.id2FROMv$lock sWHERErequest!=0ANDs.id1=m.id1ANDs.id2=m.id2)

)ORDERBYid1, id2, m.request;