Oracle的锁表与解锁、Oracle锁用户

偏执的太偏执、 2021-09-16 10:32 547阅读 0赞

Oracle的锁表与解锁

SELECT /\*+ rule \*/ s.username,  
decode(l.type,'TM','TABLE LOCK',  
'TX','ROW LOCK',  
NULL) LOCK\_LEVEL,  
o.owner,o.object\_name,o.object\_type,  
s.sid,s.serial\#,s.terminal,s.machine,s.program,s.osuser  
FROM v$session s,v$lock l,dba\_objects o  
WHERE l.sid = s.sid  
AND l.id1 = o.object\_id(+)  
AND s.username is NOT Null

**--kill session语句**  
alter system kill session'50,492';  
**--以下几个为相关表**  
SELECT \* FROM v$lock;  
SELECT \* FROM v$sqlarea;  
SELECT \* FROM v$session;  
SELECT \* FROM v$process ;  
SELECT \* FROM v$locked\_object;  
SELECT \* FROM all\_objects;  
SELECT \* FROM v$session\_wait;  
**--1.查出锁定object的session的信息以及被锁定的object名**  
SELECT l.session\_id sid, s.serial\#, l.locked\_mode,l.oracle\_username,  
l.os\_user\_name,s.machine, s.terminal, o.object\_name, s.logon\_time  
FROM v$locked\_object l, all\_objects o, v$session s  
WHERE l.object\_id = o.object\_id  
AND l.session\_id = s.sid  
ORDER BY sid, s.serial\# ;  
**--2.查出锁定表的session的sid, serial\#,os\_user\_name, machine name, terminal和执行的语句**  
***--比上面那段多出sql\_text和action***  
SELECT l.session\_id sid, s.serial\#, l.locked\_mode, l.oracle\_username, s.user\#,  
l.os\_user\_name,s.machine, s.terminal,a.sql\_text, a.action  
FROM v$sqlarea a,v$session s, v$locked\_object l  
WHERE l.session\_id = s.sid  
AND s.prev\_sql\_addr = a.address  
ORDER BY sid, s.serial\#;  
**--3.查出锁定表的sid, serial\#,os\_user\_name, machine\_name, terminal，锁的type,mode**  
SELECT s.sid, s.serial\#, s.username, s.schemaname, s.osuser, s.process, s.machine,  
s.terminal, s.logon\_time, l.type  
FROM v$session s, v$lock l  
WHERE s.sid = l.sid  
AND s.username IS NOT NULL  
ORDER BY sid;

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，  
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。  
**杀锁命令**  
alter system kill session 'sid,serial\#'  
SELECT /\*+ rule \*/ s.username,  
decode(l.type,'TM','TABLE LOCK',  
'TX','ROW LOCK',  
NULL) LOCK\_LEVEL,  
o.owner,o.object\_name,o.object\_type,  
s.sid,s.serial\#,s.terminal,s.machine,s.program,s.osuser  
FROM v$session s,v$lock l,dba\_objects o  
WHERE l.sid = s.sid  
AND l.id1 = o.object\_id(+)  
AND s.username is NOT NULL  
如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待  
以下的语句可以查询到谁锁了表，而谁在等待。  
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。  
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN  
col user\_name format a10  
col owner format a10  
col object\_name format a10  
col object\_type format a10  
SELECT /\*+ rule \*/ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle\_username User\_name,  
o.owner,o.object\_name,o.object\_type,s.sid,s.serial\#  
FROM v$locked\_object l,dba\_objects o,v$session s  
WHERE l.object\_id=o.object\_id  
AND l.session\_id=s.sid  
ORDER BY o.object\_id,xidusn DESC

==================================================================================

select \* from dba\_profiles s where s.profile='DEFAULT' and resource\_name='FAILED\_LOGIN\_ATTEMPTS';

如果显示结果如下，则表明“FAILED\_LOGIN\_ATTEMPTS”配置为了10次。

PROFILE          RESOURCE\_NAME           RESOURCE           LIMIT ----------------------------------------------------------------- DEFAULT          FAILED\_LOGIN\_ATTEMPTS   PASSWORD           10

修改登录失败次数限制为无限制：

alter profile default limit failed\_login\_attempts unlimited;

验证：

select \* from dba\_profiles s where s.profile='DEFAULT' and resource\_name='FAILED\_LOGIN\_ATTEMPTS';

如果显示结果如下，则表明“FAILED\_LOGIN\_ATTEMPTS”配置为了无限制。

PROFILE          RESOURCE\_NAME           RESOURCE           LIMIT ----------------------------------------------------------------- DEFAULT          FAILED\_LOGIN\_ATTEMPTS   PASSWORD           UNLIMITED

如果有用户已经被锁定，则以系统用户登录 [数据库][Link 1]，执行如下命令，对锁定的帐户解除锁定。

SQL> alter user msgbox identified by password account unlock;

在上述命令中，msgbox表示锁定的用户名，password为锁定用户的密码。

[Link 1]: http://www.2cto.com/database/