如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表:
复制代码
代码如下:
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
1、打开命令提示符cmd。
2、连接oracle数据库。
3、连接数据库用户shop。
4、解锁数据库用户,以便修改密码。
5、修改数据库用户密码为123。
6、验证用原来数据库用户密码连接数据库失败。
7、用新密码连接数据库。
Oracle数据库出现死锁的时候可以按照以下处理步骤加以解决:
第一步:尝试在sqlplus中通过sql命令进行删除,如果能够删除成功,则万事大吉!但通常情况下,出现死锁时,想通过命令行或者通过Oracle的管理工具删除有死锁的session,oracle只会将该session标记为killed,但无法清除掉,往往需要通过第二步在 *** 作系统层级进行删除!
Connected to Oracle9i Enterprise Edition Release 92010Connected as quik
SQL> select xidusn, object_id, session_id, locked_mode from v$locked_object; --查死锁的对象,获取其SESSION_ID
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- -----------
10 30724 29 3
10 30649 29 3
SQL> select username,sid,serial# from v$session where sid=29; --根据上步获取到的sid查看其serial#号
USERNAME SID SERIAL#
------------------------------ ---------- ----------
QUIK 29 57107
SQL> alter system kill session '29,57107'; --删除进程,如已经删除过,则会报ora-00031的错误;否则oracle会将该session标记为killed状态,等待一段时间看能否会自动消失,如长时间消失不掉,则需要做后续步骤
alter system kill session '29,57107'
ORA-00031: session marked for kill
SQL> select prospid from v$session ses,v$process pro where sessid=29 and sespaddr=proaddr; --查看spid号,以便在 *** 作系统中根据该进程号删除进程
SPID
------------
2273286
第二步:进入 *** 作系统进行删除进程,本示例的 *** 作系统是IBM aix。
login: root --录入用户名root's Password: --录入密码
Welcome to AIX Version 53!
Please see the README file in /usr/lpp/bos for information pertinent to
this release of the AIX Operating System
Last unsuccessful login: Fri Apr 23 14:42:57 BEIDT 2010 on /dev/pts/1 from 1073
52254
Last login: Fri Apr 23 15:27:50 BEIDT 2010 on /dev/pts/2 from 107352254
# ps -ef|grep 2273286 --查看进程详情
root 2289864 2494636 0 17:07:15 pts/1 0:00 grep 2273286
oracle 2273286 1 0 14:38:24 - 0:21 oracleQUIK (LOCAL=NO)
# kill -9 2273286 --删除进程,小心 *** 作,别写错进程号,如果oracle的关键进程被删,数据库会崩溃的!
# ps -ef|grep 2273286 --再次查看
root 2289864 2494636 0 17:07:15 pts/1 0:00 grep 2273286
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill –9 spid
首先需要用sysdba用户连接数据库:sqlplus sys/password@1021220:1521/orcl as sysdba
登陆之后解锁system用户:alter user system account unlock
用户被锁了?
FAILED_LOGIN_ATTEMPTS参数默认是10,即:用户连续输入10次错误密码,用户会被锁住;
可以使用其他拥有DBA权限的用户进行解锁;
alter user username account unlock;
如果是数据库内部出现死锁或阻塞会话,可以先查出阻塞的会话,
select from dba_waiters;
在杀掉阻塞的会话
alter system kill session 'sid,serial#';
测试环境,可以直接重启数据库!
首先你要知道表锁住了是不是正常锁?因为任何DML语句都会对表加锁。
你要先查一下是那个会话那个sql锁住了表,有可能这是正常业务需求,不建议随便KILL
session,如果这个锁表是正常业务你把session
kill掉了会影响业务的。
建议先查原因再做决定。
(1)锁表查询的代码有以下的形式:
select
count()
from
v$locked_object;
select
from
v$locked_object;
(2)查看哪个表被锁
select
bowner,bobject_name,asession_id,alocked_mode
from
v$locked_object
a,dba_objects
b
where
bobject_id
=
aobject_id;
(3)查看是哪个session引起的
select
busername,bsid,bserial#,logon_time
from
v$locked_object
a,v$session
b
where
asession_id
=
bsid
order
by
blogon_time;
(4)查看是哪个sql引起的
select
busername,bsid,bserial#,c
from
v$locked_object
a,v$session
b,v$sql
c
where
asession_id
=
bsid
and
bSQL_ID
=
csql_id
and
csql_id
=
''
order
by
blogon_time;
(5)杀掉对应进程
执行命令:alter
system
kill
session'1025,41';
其中1025为sid,41为serial#
以上就是关于oracle查询锁表与解锁情况提供解决方案全部的内容,包括:oracle查询锁表与解锁情况提供解决方案、Oracle数据库system用户忘记了密码怎么办、oracle数据库死锁怎么解决等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)