oracle锁表结构,关于ORACLE的锁表与解锁总结

总结1：的锁表与解锁

select

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 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

总结2：一个有用查找脚本：

column sid format 999;

column b format 9;

column object_name format a30;

column locktype format a20;

select v$lock.sid,

decode(v$lock.type,

'MR', 'Media Recovery',

'RT','Redo Thread',

'UN','User Name',

'TX', 'Transaction',

'TM', 'DML',

'UL', 'PL/SQL User Lock',

'DX', 'Distributed Xaction',

'CF', 'Control File',

'IS', 'Instance State',

'FS', 'File Set',

'IR', 'Instance Recovery',

'ST', 'Disk Space Transaction',

'TS', 'Temp Segment',

'IV', 'Library Cache Invalida-tion',

'LS', 'Log Start or Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table',

'Unknown') LockType,

rtrim(owner) || '.' || object_name object_name,

decode(lmode,   0, 'None',

1, 'Null',

2, 'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

6, 'Exclusive','Unknown') LockMode,

decode(request, 0, 'None',

1, 'Null',

2, 'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

6, 'Exclusive', 'Unknown') RequestMode,

ctime, block b

from v$lock, all_objects

where sid > 6

and v$lock.id1 = all_objects.object_id;

找到某表的锁 所属的sid.

alter system kill session 'sid,serial#';

即可

select object_id,session_id,serial#,oracle_username,os_user_name,s.process

from v$locked_object a,v$session s

where a.session_id=s.sid;

查出被lock 的对象

然后 alter system kill session 'sid,serial#';

知识点3：

LOCK   TABLE

语法：

LOCK   TABLE   table_1   [,table_2,   ...,   table_n]   IN   lock_mode   MODE

NOWAIT

变量：

table_1,...,table_n:   一系列你想通过使用LOCK   TABLE语句锁住的数据库表。

lock_mode:   对于某一数据库表你要设定的锁定模式。你可以从如下的锁定模式中任选一个。

EXCLUSIVE

SHARE   ROW   EXCLUSIVE

SHARE

SHARE   UPDATE

ROW   SHARE

ROW   EXCLUSIVE

NOWAIT:   Oracle   will   not   wait   to   lock   the   given   Table(s),   if   the   Table(s)   is(are)   not

available

例子：

SQL

LOCK   TABLE   loan   IN   SHARE   MODE   ;

LOCK   TABLE   region   IN   EXCLUSIVE   MODE   NOWAIT;

LOCK   TABLE   acct   IN   SHARE   UPDATE   MODE;

LOCK   TABLE   bank   IN   ROW   EXCLUSIVE   MODE   NOWAIT;

LOCK   TABLE   user   IN   SHARE   ROW   EXCLUSIVE   MODE;

LOCK   TABLE   branch   IN   ROW   SHARE   MODE   NOWAIT;

commit

/

转自：http://space.itpub.net/18953778/viewspace-563108