2022年7月

racle 查看锁表sql语句（导致锁表的sql）、解锁语句
--查看锁表进程SQL语句1：

--被锁对象表、数据库对象表、数据session表关联来获取被锁对象对应的是那个session；
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

--查看锁表进程SQL语句2：
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

--查看导致锁表的sql语句是那一条

select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;

--杀掉锁表进程：

--通过上面的查询获取SID和serial#，替换下面的x,y,就可以解除被锁的状态
alter system kill session 'x,y';

1、上午用户反馈dg数据和生产数据不一致
检查发现mpr进行已停止
select * from v$managed_standby；
2、检查alert.log
发现告警：ORA 00494 ENQUEUE[CF] held for too long (more than 900 seconds) by 'inst 1,osid 469226'
incident details in:/u01/app/....../sid_pr00_258815_i240250.trc

打开trace文件，发现loadAVG： 16.9,15.22,10.59
cat /proc/cpuinfo | grpe proc |wc -l 4 4个cpu，明显压力过大
经分析发现mrp0 进程停止的时间为备份软件启动时间。且主库的cpu、内存配置是备库的2倍，由此可以断点，是因为cpu过大，导致产生了长时间CF控制文件锁，导致恢复进程停止。
3、手动启动应用进程
alter database recover managed standby database using current logfile disconnect from session
提示相应的未找到归档日志
检查v$archived_log 发现name字段中存在备份一体机的备份的归档信息。
4、手动清理rman中，备份一体机的备份信息
delete archivelog like ‘%xxxxx.dbf%’
5、重新启动应用进程
alter database recover managed standby database using current logfile disconnect from session
6、检查发现
select * from v$managed_standby;
mrp0进行已经正常启动
7、检查应用执行情况

select thread#,sequance#,'last applied:' logs,to_char(next_time,'DD-MON-YYYY：HH24:MI:SS') TIME
from archived_log 
 where sequence#=(select max(sequence#) from v$archived_log where applied='YES') 
UNION 
SELECT THREAD#,sequence#,'las received:' logs,to_char(next_time,'DD-MON-YYYY:HH24:MI:SS') TIME 
FROM 
V$archived_log 
  where sequence#=(select max(sequence#) from v$archived_log);

原文转自：http://space.itpub.net/519536/viewspace-693689

在诊断有关锁等待这种数据库等常见故障的时候经常会用到V$LOCK视图，这个视图中的ID1和ID2的含义经常被提及。了解这两个字段的含义对理解V$LOCK视图很有意义。

本文简单对V$LOCK视图中的ID1和ID2含义进行描述和探索。

1.Oracle官方文档中的描述
参考链接：http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1147.htm#i1407629
其中只记录了如下两条模糊的信息：
ID1 NUMBER Lock identifier #1 (depends on type)
ID2 NUMBER Lock identifier #2 (depends on type)

这样的描述是不足够和充分的。我们要求甚解！

2.MOS中的描述
有关V$LOCK视图中的ID1和ID2的含义在MOS的29787.1中有以下描述，描述的比较清晰，供参考。
The meaning of ID1 and ID2 depend on the lock TYPE.

Most Common Lock Types:

TYPE Name ID1 ID2
~~ ~ ~~~
TX Transaction Decimal RBS & slot Decimal WRAP number

                 (0xRRRRSSSS  RRRR = RBS number, SSSS = slot)
A TX lock is requested in eXclusive mode if we are waiting on a ROW.
A SHARE mode request implies we are waiting some other resource held
by the TX (Eg: waiting for an ITL entry)

TM Table Locks Object id of table. Always 0.

TS Temp Segment TS# Relative DBA

ST Space Transaction Only ONE enqueue.
UL User Locks

3.实例说明TM表级锁时ID1和ID2的含义

以最常见的TM表级锁定为例，ID1表示被锁定的对象的OBJECT_ID，ID2此时为“0”。
1）模拟TM标记锁
（1）创建测试用表T
sec@ora10g> create table t (x varchar2(8));
Table created.

（2）向表T中插入一条数据
sec@ora10g> insert into t values('secooler');
1 row created.

2）获取锁信息
当前会话的sid为140，为了避免查询到其他信息，这里进行限定。
sec@ora10g> select sid, type, id1, id2 from v$lock where sid=140;

   SID TYPE                        ID1        ID2
   140 TM                        10981          0
   140 TX                       655404        260

3）根据ID1的值定位操作的数据库对象信息
sec@ora10g> select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_ID=10981;

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE

SEC T 10981 TABLE

OK，得以验证，这里10981对应数据库对象便是我们操作的表T。

4.实例说明TX事务锁时ID1和ID2的含义
当锁类型为TX事务锁时ID1和ID2的含义如下：
ID1对应视图V$TRANSACTION中的XIDUSN字段（Undo segment number：事务对应的撤销段序列号）和XIDSLOT字段（Slot number：事务对应的槽位号）。其中ID1的高16位为XIDUSN，低16位为XIDSLOT。

ID2对应视图V$TRANSACTION中的XIDSQN字段（Sequence number：事务对应的序列号）。

1）仍然以上面的锁信息中的TX锁对应的记录为例进行说明。
摘录信息如下：

   SID TYPE                        ID1        ID2
   140 TX                       655404        260

2）使用ID2的值“260”检索V$TRANSACTION视图
sec@ora10g> select XIDUSN,XIDSLOT,XIDSQN from V$TRANSACTION where XIDSQN=260;

XIDUSN    XIDSLOT     XIDSQN
    10         44        260

3）10和44对应到ID1的值“655404”的方法
10*2^16+44=655404

4）使用ID1的值计算得到XIDUSN和XIDSLOT方法
sec@ora10g> select trunc(655404/power(2,16)) XIDUSN from dual;

XIDUSN
    10

sec@ora10g> select bitand(655404,to_number('ffff','xxxx'))+0 XIDSLOT from dual;

XIDSLOT

    44

这便是他们之间既简单有复杂的关系。

5.小结
有关V$LOCK的应用请参考文章《【实验】【LOCK】“锁等待”模拟、诊断及处理方法》（http://space.itpub.net/519536/viewspace-605526）
作为最常用的动态性能视图V$LOCK，它的每个字段都应该深入了解和探索。以便做到胸有成竹，事半功倍。

更多讨论：http://www.itpub.net/thread-973766-1-1.html

-- The End --

众所周知Oracle临时表空间主要是用于数据库较大的临时排序用，在PGA分配的工作区空间不足以容纳排序数据时使用临时表空间。在用户执行的排序相关的语句执行完毕后临时段回收，相应的临时空间占用也会自动释放。但针对LOB所产生的临时段的释放不会遵从些机制。

使用率过高带来的影响

临时表空间使用率过高带来的影响：

由于一般业务中都会存在大量排序的sql(如distinct、order by、group by、union、union all、hash join等)，所以一旦临时表空间长时间使用率过高将导致核心sql因ORA-1652错误而终止，最终导致业务处理失败，如果是核心业务那么这个影响是巨大的。

遇到的问题

某核心OLTP生产系统最近发现临时表空间占用高达90%多，且持续监控发现一直不释放，而且还在缓慢增长

- 阅读剩余部分 -

  1. 1
  2. 2
  3. 3