Oracle 锁表情况查询sql

select LOCK_INFO.OWNER || '.' || LOCK_INFO.OBJ_NAME as "已锁物件名称", --物件名称(已经被锁住)

我们提供的服务有：成都网站设计、成都网站建设、微信公众号开发、网站优化、网站认证、博爱ssl等。为上1000+企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的博爱网站制作公司

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;

oracle查询资源占用，锁表解锁

1. 先通过top命令查看产用资源较多的spid号

2.查询当前耗时的会话ID，用户名，sqlID等：

select sid,serial#,machine,username,program,sql_hash_value,sql_id,

  to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session

where paddr in (select addr from v$process where spid in ('5648612','256523'));

3. 如果上一步sql_id或者 hash_value不为空，则可用v$sqlarea查出当前正在使用的sql

select sql_text

from v$sqltext_with_newlines

where hash_value = hash_value

order by piece;

也可直接使用:

select a.*,b.SQL_TEXT from (

select sid,serial#,machine,username,program,sql_hash_value,sql_id,

  to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session

where paddr in (select addr from v$process where spid in ('23226'))

) a,v$sql b

where a.sql_id  = b.SQL_ID(+)

4.kill占用大资源的session

Alter system kill session 'SID,SERIAL#'

解锁：

1.查询哪些对象被锁：

select object_name,machine,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;

2.下面的语句用来杀死一个进程：

alter system kill session '524,1095'; (其中24,111分别是上面查询出的sid,serial#)

3.再一次查询目前锁定的对象，若发现以上方法不能解除锁定的表，则用以下方法：

3.1 执行下面的语句获得进程（线程）号：

select spid, osuser, s.program

from v$session s,v$process p

where s.paddr=p.addr and s.sid=524 （524是上面的sid）

查询Oracle锁表语句以及解锁语句

查询锁表：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#;

解锁：ALTER system KILL session 'sid,serial#';

查询锁住原因：SELECT b.sid oracleID,b.username 登录Oracle用户名,b.serial#,spid 操作系统ID,paddr, 

sql_text 正在执行的SQL,b.machine 计算机名 FROM v$process a, v$session b, v$sqlarea c 

WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value  and b.USERNAME='FKPHIS24';

oracle数据库表被锁了怎么解锁

1、在做Oracle监听程序测试时，发现帐户已经被锁定。

2、在数据库安装电脑上，点击开始打开运行窗口。

3、在运行窗口输入CMD，调出命令提示符界面。

3、在命令提示符下面，用管理员身份登入到数据库sqlplus / as sysdba。

4、输入解锁命令alter user Scott account unlock后回车。

5、看见用户已更改的字样，表示命令已成功执行。

6、再切换到监听程序验证，原来的ora-28000帐户被锁定的提示已经不存在了。用户解锁成功。

如何查看oracle数据库用户是否被锁

这个要dba权限的用户才能查看，具体的查看方法是 select * from dba_users 。用户状态一般是open（正常） locked（锁定）expire（过期失效）几种。

分享题目：如何查询oracle锁,如何查看oracle锁表
文章出自：http://ybzwz.com/article/hegsge.html