其中一些批处理报告将尝试访问可能仍被锁定的数据库表。如何确定Oracle表是否被锁定？是否有任何SQL语句显示要分析的历史记录详细信息？
#1 楼
以下查询给出了所有锁的详细信息。SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID
#2 楼
可以使用以下脚本来快速识别Oracle系统中的所有锁定对象。select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
参考：-Burleson Consulting的Oracle技巧http：//www.dba -oracle.com/t_find_oracle_locked_objects.htm
#3 楼
您可以从V $ LOCKED_OBJECT中查询当前锁定的对象。虽然没有锁定的历史记录，但记录所有锁定将导致巨大的性能开销并存储大量数据。
#4 楼
使用以下查询，您可以找到桌子上的锁。column oracle_username format a15;
column os_user_name format a15;
column object_name format a37;
column object_type format a37;
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
锁定锁
#5 楼
如果要释放锁定对象的锁，请终止相应的会话。-- Query to Get List of all locked objects
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID ;
-- and A.OS_USER_NAME = 'mahendar'
-- Query to Get List of locked sessions
select SID,SERIAL#,INST_ID from gv$session a where schemaname = 'SYSTEM';
-- and osuser = 'mahendar';
-- o/p: 314 26513 1
-- Statement to Kill the session [pass values in the same order and append @ for inst_id]
alter system kill session '314,26513,@1';
#6 楼
您可以从v$lock和dba_objects视图检查表锁定。下面的查询将为您提供锁的详细信息。select a.sid||'|'|| a.serial#||'|'|| a.process
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME=upper('&TABLE_NAME');
查询2：
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2;
您可以在下面使用查询，它将为您提供更多详细信息。
桌锁
#7 楼
选择会话ID并杀死保持对模式用户的锁定的进程。select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status,
b.osuser, b.machine from v$locked_object a, v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;
，然后
SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';
INST_ID SID SERIAL#
---------- ---------- ----------
1 130 620
SQL> alter system kill session '130,620,1';
alter system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
Now, it works:
SQL> alter system kill session '130,620,@1';
System altered.
评论
为什么在问题发布7年后仍需要此答案？有没有在现有答案中尚未发布的其他信息？
– Miracle173
1月3日13:36
评论
我的意思是我想在特定时间间隔内显示内容。例如：我想列出02:00:00 PM-05:00:00 PM之间的所有锁定表，以供进一步分析。
通常我想在oracle中找到锁定的表？
@Selahattin您在谈论应用程序级别锁定吗？由于其序列化级别和设计，Oracle通常不执行表锁定