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