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

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 92010 

Connected 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数据库死锁怎么解决等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！