oracle查询锁表与解锁情况提供解决方案

如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表:
复制代码
代码如下:
SELECT
/+
rule
/
susername,
decode(ltype,'TM','TABLE
LOCK',
'TX','ROW
LOCK',
NULL)
LOCK_LEVEL,
oowner,oobject_name,oobject_type,
ssid,sserial#,sterminal,smachine,sprogram,sosuser
FROM
v$session
s,v$lock
l,dba_objects
o
WHERE
lsid
=
ssid
AND
lid1
=
oobject_id(+)
AND
susername
is
NOT
NULL
以下的语句可以查询到谁在等待:
复制代码
代码如下:
SELECT
/+
rule
/
lpad('
',decode(lxidusn
,0,3,0))||loracle_username
User_name,
oowner,oobject_name,oobject_type,ssid,sserial#
FROM
v$locked_object
l,dba_objects
o,v$session
s
WHERE
lobject_id=oobject_id
AND
lsession_id=ssid
ORDER
BY
oobject_id,xidusn
DESC
解锁命令:
复制代码
代码如下:
alter
system
kill
session
'sid,serial#'
1)
复制代码
代码如下:
select
LOCK_INFOOWNER
||
''
||
LOCK_INFOOBJ_NAME
as
"已锁物件名称",
--物件名称(已经被锁住)
LOCK_INFOSUBOBJ_NAME
as
"已锁子物件名称",
--
子物件名称(已经被锁住)
SESS_INFOMACHINE
as
"机器名称",
--
机器名称
LOCK_INFOSESSION_ID
as
"会话ID",
--
会话SESSION_ID
SESS_INFOSERIAL#
as
"会话SERIAL#",
--
会话SERIAL#
SESS_INFOSPID
as
"OS系统的SPID",
--
OS系统的SPID
(SELECT
INSTANCE_NAME
FROM
V$INSTANCE)
"实例名SID",
--实例名SID
LOCK_INFOORA_USERNAME
as
"ORACLE用户",
--
ORACLE系统用户名称
LOCK_INFOOS_USERNAME
as
"OS用户",
--
作业系统用户名称
LOCK_INFOPROCESS
as
"进程编号",
--
进程编号
LOCK_INFOOBJ_ID
as
"对象ID",
--
对象ID
LOCK_INFOOBJ_TYPE
as
"对象类型",
--
对象类型
SESS_INFOLOGON_TIME
as
"登录时间",
--
登录时间
SESS_INFOPROGRAM
as
"程式名称",
--
程式名称
SESS_INFOSTATUS
as
"会话状态",
--
会话状态
SESS_INFOLOCKWAIT
as
"等待锁",
--
等待锁
SESS_INFOACTION
as
"动作",
--
动作
SESS_INFOCLIENT_INFO
as
"客户资讯"
--
客户资讯
from
(select
objOWNER
as
OWNER,
objOBJECT_NAME
as
OBJ_NAME,
objSUBOBJECT_NAME
as
SUBOBJ_NAME,
objOBJECT_ID
as
OBJ_ID,
objOBJECT_TYPE
as
OBJ_TYPE,
lock_objSESSION_ID
as
SESSION_ID,
lock_objORACLE_USERNAME
as
ORA_USERNAME,
lock_objOS_USER_NAME
as
OS_USERNAME,
lock_objPROCESS
as
PROCESS
from
(select

from
all_objects
where
object_id
in
(select
object_id
from
v$locked_object))
obj,
v$locked_object
lock_obj
where
objobject_id
=
lock_objobject_id)
LOCK_INFO,
(select
SID,
SERIAL#,
LOCKWAIT,
STATUS,
(select
spid
from
v$process
where
addr
=
apaddr)
spid,
PROGRAM,
ACTION,
CLIENT_INFO,
LOGON_TIME,
MACHINE
from
v$session
a)
SESS_INFO
where
LOCK_INFOSESSION_ID
=
SESS_INFOSID
order
by
LOCK_INFOSESSION_ID;
2)
复制代码
代码如下:
select
sql_text
from
v$sqltext
where
address
in
(select
sql_address
from
v$session
where
sid
=
&sid)
order
by
piece;
3)
复制代码
代码如下:
ALTER
SYSTEM
KILL
SESSION
'会话ID,会话SERIAL#';
4)
kill
-9
OS系统的SPID

今天访问应用的时候，发现账号被锁定，登录oracle后，发现很多用户都被锁定了。看下提示信息，原来是密码过期了导致账号被锁定。这里记录下这几个 *** 作的常用命令，以方便以后自己的使用。
1查看用户的proifle是哪个，一般是default
SELECT username,PROFILE FROM dba_users;
2查看对应的概要文件（如default）的密码有效期设置
SELECT FROM dba_profiles s WHERE sprofile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
3将概要文件(如default)的密码有效期由默认的180天修改成“无限制”（修改之后不需要重启动数据库，会立即生效）
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4对已经锁定和过期的账号进行解锁
（1）alter user 用户名 identified by <原来的密码> account unlock; ----不用换新密码，用户名和密码也不用加引号，这个命令会解锁账号，同时取消密码过期。
（2）alter user 用户名 account unlock; ----这个只会解锁账号，而不会取消密码过期，所以建议使用上面的口令

在对指定表做append *** 作，其他再做truncate时候，会产生锁表，如下验证步骤，

1、创建测试表，

create table test_lock(id number, value varchar2(200));

2、执行append语句；并且不做提交，insert /+append/ into test_lock values(1,1);

3、再次执行清表语句，truncate table test_lock;报锁表错误，

4、查看锁表语句，发现被锁表，

select bobject_name, t

from v$locked_object t, user_objects b

where tobject_id = bobject_id

1创建测试表，如下图。

createtabletest_lock(idnumber,valuevarchar2(200));

2执行append语句；并且不做提交，insert/+append/intotest_lockvalues(1,1)如下图。

3再次执行清表语句，truncatetabletest_lock;报锁表错误，如下图。

4查看锁表语句，发现被锁表

selectbobject_name,t

fromv$locked_objectt,user_objectsb

wheretobject_id=bobject_id

注意事项：

简化数据:可以将复杂的查询创建为其他人可以使用的视图，而不必了解复杂的业务或逻辑关系。这简化并掩盖了视图用户数据的复杂性。

表结构设计的补充:在设计的系统才刚刚开始,大部分的程序直接访问数据表结构,但是随着业务的变化,系统更新,等等,引起了一些表结构不适用,这次修改系统的表结构太大,开发成本较高的影响。

这个时候可以创建一个视图来补充表结构设计,降低开发成本。程序可以通过查询视图直接获得它想要的数据。

添加安全性:视图可以向用户显示表中的指定字段，而不是向用户显示表中的所有字段。在实际开发中，视图通常作为提供数据的一种方式提供，并将只读权限提供给第三方以供查询使用。

SQL>select session_id from v$locked_object;
49
SQL>SELECT sid, serial#, username  FROM v$session where sid = 49;
49  554  system
SQL>ALTER SYSTEM KILL SESSION '49, 554';

用的是客户端登录，直接关掉客户端就可以了，因为你的客户端也是个session
普通用户也可以，因为是你这个用户开启这个进程，那么你也可以关闭这个进程。

3尽量用dba权限的用户 *** 作，还有可以到主机上 kill -9 spid 会更快。

---