Oracle数据库锁表原因及如何解锁

ALTER SYSTEM KILL SESSION '4033,33518'; --解除被锁定的会话信息

公司专注于为企业提供成都网站建设、成都网站设计、微信公众号开发、电子商务商城网站建设，微信小程序定制开发，软件按需求定制网站等一站式互联网企业服务。凭借多年丰富的经验，我们会仔细了解各客户的需求而做出多方面的分析、设计、整合，为客户设计出具风格及创意性的商业解决方案，成都创新互联公司更提供一系列网站制作和网站推广的服务。

select session_id from v$locked_object; --查看哪个会话被锁了

SELECT sid, serial#, username, osuser FROM v$session where sid = 4033; --查看具体被锁会话信息

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

以下的语句可以查询到谁在等待:

复制代码

代码如下:

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

解锁命令:

复制代码

代码如下:

alter

system

kill

session

'sid,serial#'

1).

复制代码

代码如下:

select

LOCK_INFO.OWNER

||

'.'

||

LOCK_INFO.OBJ_NAME

as

"已锁物件名称",

--物件名称(已经被锁住)

LOCK_INFO.SUBOBJ_NAME

as

"已锁子物件名称",

--

子物件名称(已经被锁住)

SESS_INFO.MACHINE

as

"机器名称",

--

机器名称

LOCK_INFO.SESSION_ID

as

"会话ID",

--

会话SESSION_ID

SESS_INFO.SERIAL#

as

"会话SERIAL#",

--

会话SERIAL#

SESS_INFO.SPID

as

"OS系统的SPID",

--

OS系统的SPID

(SELECT

INSTANCE_NAME

FROM

V$INSTANCE)

"实例名SID",

--实例名SID

LOCK_INFO.ORA_USERNAME

as

"ORACLE用户",

--

ORACLE系统用户名称

LOCK_INFO.OS_USERNAME

as

"OS用户",

--

作业系统用户名称

LOCK_INFO.PROCESS

as

"进程编号",

--

进程编号

LOCK_INFO.OBJ_ID

as

"对象ID",

--

对象ID

LOCK_INFO.OBJ_TYPE

as

"对象类型",

--

对象类型

SESS_INFO.LOGON_TIME

as

"登录时间",

--

登录时间

SESS_INFO.PROGRAM

as

"程式名称",

--

程式名称

SESS_INFO.STATUS

as

"会话状态",

--

会话状态

SESS_INFO.LOCKWAIT

as

"等待锁",

--

等待锁

SESS_INFO.ACTION

as

"动作",

--

动作

SESS_INFO.CLIENT_INFO

as

"客户资讯"

--

客户资讯

from

(select

obj.OWNER

as

OWNER,

obj.OBJECT_NAME

as

OBJ_NAME,

obj.SUBOBJECT_NAME

as

SUBOBJ_NAME,

obj.OBJECT_ID

as

OBJ_ID,

obj.OBJECT_TYPE

as

OBJ_TYPE,

lock_obj.SESSION_ID

as

SESSION_ID,

lock_obj.ORACLE_USERNAME

as

ORA_USERNAME,

lock_obj.OS_USER_NAME

as

OS_USERNAME,

lock_obj.PROCESS

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

obj.object_id

=

lock_obj.object_id)

LOCK_INFO,

(select

SID,

SERIAL#,

LOCKWAIT,

STATUS,

(select

spid

from

v$process

where

addr

=

a.paddr)

spid,

PROGRAM,

ACTION,

CLIENT_INFO,

LOGON_TIME,

MACHINE

from

v$session

a)

SESS_INFO

where

LOCK_INFO.SESSION_ID

=

SESS_INFO.SID

order

by

LOCK_INFO.SESSION_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 怎么解锁scott账户

原因：默认Oracle10g的scott不能登陆。解决： （1）conn sys/sys as sysdba;//以DBA的身份登录 （2）alter user scott account unlock;// 然后解锁 （3）conn scott/tiger //弹出一个修改密码的对话框，修改一下密码就可以了具体操作步骤如下： C: sqlplus 请输入用户名：sys 输入口令：sys as sysdba //注意：在口令这里输入的密码后面必须要跟上 as sysdba 才可以。 SQL alter user scott account unlock;用户已更改. SQL commit;提交完成. SQL conn scott/tiger 更改scott口令 新口令：tiger 重新键入新口令：tiger 口令已更改 已连接。另一种方法：你打开命令提示符，不要登录直接输入下面： sqlplus sys/tiger as sysdba 以dba方式进入sys帐户； alter user scott account unlock; 给scott用户解锁；

oracle锁表如何解锁

自己本地的库的话，重启数据库就好了。自动回滚，重要数据库不可以重启的话，查到用户会话的sid和serial#然后kill

分享名称：oracle怎么把表解锁,oracle表锁住了
标题来源：http://scgulin.cn/article/hshjij.html