精华内容
下载资源
问答
  • BLOCKER SID BLOCKEE SID ------------------------------ ---------- ------------------------------ ---------- SCOTT 1644 SCOTT 1632 注:sid = 1644 的session 阻塞了sid=1632的 session. V$LOCK This ...

    SQL> select sid, type, id1, id2, lmode, request, block from v$lock  where  id1=51148;

    SID TY        ID1        ID2      LMODE    REQUEST      BLOCK

    ---------- -- ---------- ---------- ---------- ---------- ----------

    1644 TM      51148          0          6          0          1

    1632 TM      51148          0          0          6          0

    SQL>

    SQL>

    SQL>

    SQL> select  ( select username from v$session where sid=a.sid) blocker , a.sid, (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a , v$lock b where a.id1=b.id1 and a.id2 = b.id2 and a.block=1 and b.request > 0;

    BLOCKER                               SID BLOCKEE                               SID

    ------------------------------ ---------- ------------------------------ ----------

    SCOTT                                1644 SCOTT                                1632

    注:sid = 1644 的session 阻塞了sid=1632的 session.

    V$LOCK

    This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

    ColumnDatatypeDescriptionADDRRAW(4 | 8)Address of lock state object

    KADDRRAW(4 | 8)Address of lock

    SIDNUMBERIdentifier for session holding or acquiring the lock

    TYPEVARCHAR2(2)Type of user or system lock

    The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

    TM - DML enqueue

    TX - Transaction enqueue

    UL - User supplied

    The locks on the system types are held for extremely short periods of time. The system type locks are listed in .

    ID1NUMBERLock identifier #1 (depends on type)

    ID2NUMBERLock identifier #2 (depends on type)

    LMODENUMBERLock mode in which the session holds the lock: 0 - none

    1 - null (NULL)

    2 - row-S (SS)

    3 - row-X (SX)

    4 - share (S)

    5 - S/Row-X (SSX)

    6 - exclusive (X)

    REQUESTNUMBERLock mode in which the process requests the lock: 0 - none

    1 - null (NULL)

    2 - row-S (SS)

    3 - row-X (SX)

    4 - share (S)

    5 - S/Row-X (SSX)

    6 - exclusive (X)

    CTIMENUMBERTime since current mode was granted

    BLOCKNUMBERA value of either 0 or 1, depending on whether or not the lock in question is the blocker.

    Table 6-1 Values for the TYPE Column: System TypesSystem TypeDescriptionSystem TypeDescriptionBLBuffer hash table instanceNA..NZLibrary cache pin instance (A..Z = namespace)

    CFControl file schema global enqueuePFPassword File

    CICross-instance function invocation instancePI, PSParallel operation

    CUCursor bindPRProcess startup

    DFdatafile instanceQA..QZRow cache instance (A..Z = cache)

    DLDirect loader parallel index createRTRedo thread global enqueue

    DMMount/startup db primary/secondary instanceSCSystem change number instance

    DRDistributed recovery processSMSMON

    DXDistributed transaction entrySNSequence number instance

    FSFile setSQSequence number enqueue

    HWSpace management operations on a specific segmentSSSort segment

    INInstance numberSTSpace transaction enqueue

    IRInstance recovery serialization global enqueueSVSequence number value

    ISInstance stateTAGeneric enqueue

    IVLibrary cache invalidation instanceTSTemporary segment enqueue (ID2=0)

    JQJob queueTSNew block allocation enqueue (ID2=1)

    KKThread kickTTTemporary table enqueue

    LA .. LPLibrary cache lock instance lock (A..P = namespace)UNUser name

    MMMount definition global enqueueUSUndo segment DDL

    MRMedia recoveryWLBeing-written redo log instance

    展开全文
  • oracle session阻塞查询

    2013-10-04 17:56:01
    注:sid = 1644 的session 阻塞了sid=1632的 session.   V$LOCK This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch. Column Datatype Description ...
    SQL> select sid, type, id1, id2, lmode, request, block from v$lock  where  id1=51148;
           SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
    ---------- -- ---------- ---------- ---------- ---------- ----------
          1644 TM      51148          0          6          0          1
          1632 TM      51148          0          0          6          0
    SQL>
    SQL>
    SQL>
    SQL> select  ( select username from v$session where sid=a.sid) blocker , a.sid, (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a , v$lock b where a.id1=b.id1 and a.id2 = b.id2 and a.block=1 and b.request > 0;
    BLOCKER                               SID BLOCKEE                               SID
    ------------------------------ ---------- ------------------------------ ----------
    SCOTT                                1644 SCOTT                                1632
    注:sid = 1644 的session 阻塞了sid=1632的 session.
     

    V$LOCK

    This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

    ColumnDatatypeDescription
    ADDRRAW(4 | 8)Address of lock state object
    KADDRRAW(4 | 8)Address of lock
    SIDNUMBERIdentifier for session holding or acquiring the lock
    TYPEVARCHAR2(2)Type of user or system lock

    The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

    TM - DML enqueue

    TX - Transaction enqueue

    UL - User supplied

    The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table 6-1.

    ID1NUMBERLock identifier #1 (depends on type)
    ID2NUMBERLock identifier #2 (depends on type)
    LMODENUMBERLock mode in which the session holds the lock:
    • 0 - none

    • 1 - null (NULL)

    • 2 - row-S (SS)

    • 3 - row-X (SX)

    • 4 - share (S)

    • 5 - S/Row-X (SSX)

    • 6 - exclusive (X)

    REQUESTNUMBERLock mode in which the process requests the lock:
    • 0 - none

    • 1 - null (NULL)

    • 2 - row-S (SS)

    • 3 - row-X (SX)

    • 4 - share (S)

    • 5 - S/Row-X (SSX)

    • 6 - exclusive (X)

    CTIMENUMBERTime since current mode was granted
    BLOCKNUMBERA value of either 0 or 1, depending on whether or not the lock in question is the blocker.

    Table 6-1 Values for the TYPE Column: System Types

    System TypeDescriptionSystem TypeDescription

    BL

    Buffer hash table instance

    NA..NZ

    Library cache pin instance (A..Z = namespace)

    CF

    Control file schema global enqueue

    PF

    Password File

    CI

    Cross-instance function invocation instance

    PI, PS

    Parallel operation

    CU

    Cursor bind

    PR

    Process startup

    DF

    datafile instance

    QA..QZ

    Row cache instance (A..Z = cache)

    DL

    Direct loader parallel index create

    RT

    Redo thread global enqueue

    DM

    Mount/startup db primary/secondary instance

    SC

    System change number instance

    DR

    Distributed recovery process

    SM

    SMON

    DX

    Distributed transaction entry

    SN

    Sequence number instance

    FS

    File set

    SQ

    Sequence number enqueue

    HW

    Space management operations on a specific segment

    SS

    Sort segment

    IN

    Instance number

    ST

    Space transaction enqueue

    IR

    Instance recovery serialization global enqueue

    SV

    Sequence number value

    IS

    Instance state

    TA

    Generic enqueue

    IV

    Library cache invalidation instance

    TS

    Temporary segment enqueue (ID2=0)

    JQ

    Job queue

    TS

    New block allocation enqueue (ID2=1)

    KK

    Thread kick

    TT

    Temporary table enqueue

    LA .. LP

    Library cache lock instance lock (A..P = namespace)

    UN

    User name

    MM

    Mount definition global enqueue

    US

    Undo segment DDL

    MR

    Media recovery

    WL

    Being-written redo log instance

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-773725/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/27042095/viewspace-773725/

    展开全文
  • 主要介绍了Oracle中查看引起Session阻塞的2个脚本分享,本文给出了2个脚本来查询导致Session阻塞的原因,并给出Kill引起阻塞的Session方法,需要的朋友可以参考下
  • oracle阻塞session

    2020-09-08 16:15:41
    1.查询阻塞 select a.* from (SELECT o.object_name, l.locked_mode, s.sid, s.serial#, s.final_blocking_session, s.sql_exec_start, (sysdate - nvl(s.sql_exec_start, sysdate)

    1.查询阻塞

    select a.*
      from (SELECT o.object_name,
                   l.locked_mode,
                   s.sid,
                   s.serial#,
                   s.final_blocking_session,
                   s.sql_exec_start,
                   (sysdate - nvl(s.sql_exec_start, sysdate)) * 24 * 3600 as duration_second
              FROM v$locked_object l, dba_objects o, v$session s
             WHERE l.object_id  = o.object_id
               AND l.session_id = s.sid) a
     where duration_second > 10 ; 
    

    2.查询:最终阻塞者相应信息 or 被阻塞者信息

    select sid,serial#,s.sql_id,a.SQL_FULLTEXT from v$session s ,v$sqlarea a 
    where nvl(s.SID,s.PREV_SQL_ID)=:final_blocking_session and a.SQL_ID=s.sql_id
    

    3.杀掉阻塞者

    alter system kill session 'sid,serial#';
    
    展开全文
  • 如何查看Oracle数据库的session阻塞

    千次阅读 2012-12-27 11:27:41
    我们都知道ORACLE自己会自动处理死锁的情况,不需要人为的干预,但是ORACLE不能自己处理session阻塞的的情况,阻塞导致资源的浪费和消耗系统性能,这个时候我们就需要快速的找出导致阻塞的原因,并尽快排除它,好让...

    我们都知道ORACLE自己会自动处理死锁的情况,不需要人为的干预,但是ORACLE不能自己处理session阻塞的的情况,阻塞导致资源的浪费和消耗系统性能,这个时候我们就需要快速的找出导致阻塞的原因,并尽快排除它,好让系统重新正常运行。下面我将做一个例子来解释如何迅速的处理这种阻塞:

    下面的例子是两个session 同时更新HR用户的同一条记录。

    HR用户窗口1:

    [oracle@even ~]$ sqlplus hr/hr

    SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 18 14:57:27 2012

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining options

    SQL> update hr.employees set last_name='b' where employee_id=100;

    1 row updated.

     

    HR用户窗口2:

    [oracle@even ~]$ sqlplus hr/hr

    SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 18 14:56:49 2012

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining options

    SQL> update hr.employees set last_name='a' where employee_id=100;

    此时进入等待……hang住不动了

     

    在打开一个窗口,用SYS用户登陆:

    [oracle@even ~]$ sqlplus / as sysdba

    SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 18 14:58:31 2012

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.


    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining options

    ---查找阻塞,和被阻塞session id

    SQL>select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid 
    ||','||d.serial# block_msg, a.block  from v$lock a,v$lock b,v$session c,v$session d 
      3  where a.id1=b.id1  and a.id2=b.id2  and a.block>0  and a.sid <>b.sid  and a.sid=c.sid  and b.sid=d.SID; 

    BLOCK_MSG
    --------------------------------------------------------------------------------
         BLOCK
    ----------
    pts/2 ('151,18') is blocking 133,614
             1


    SQL> select sid,serial#,username from v$session where username is not null; 

           SID    SERIAL# USERNAME
    ---------- ---------- ------------------------------
           133        614 HR
           134         71 SYS
           138        298 SYSMAN
           139          2 SYSMAN
           140          2 SYSMAN
           142         59 DBSNMP
           147        175 DBSNMP
           151         18 HR
           152       1639 SYS
           153         26 SYSMAN
           159         13 SYS

    11 rows selected.

    ---kill the blocker '151,8'

    SQL> alter system kill session'151,18';

    System altered.

     

    再次回到窗口1:

    SQL> select * from employees;
    select * from employees
    *
    ERROR at line 1:
    ORA-00028: your session has been killed

     

     

    再次回到HR用户窗口2查看,语句已经执行了(提示1 row updated):

    SQL> update hr.employees set last_name='a' where employee_id=100;

    1 row updated.

     


     

     

     

    展开全文
  • About Oracle SessionView

    2020-03-23 14:33:31
    一、在Oracle中,V$SESSION视图中有哪些比较实用的列? Oracle 数据库中V、GV、GV、GV、XKaTeX parse error: Expected group after '_' at position 3: 、V_̲、GV_之间的关系说明GV之间的关系说明 GV之间的关系...
  • Oracle Session&Process

    2018-05-16 16:00:00
    v$session 每一个连接到数据库实例中的session都拥有一条记录。包括用户session及后台进程如DBWR,LGWR,arcchiver等等。 SADDR: session address SID: session identifier,常用于连接其它列。 SERIAL#: sid 会...
  • oracle session常用语句

    2011-05-10 17:46:54
    --查阻塞的会话:--查询语句:Select osuser,machine,program,Module,sid,serial#,event,t2.logon_timefrom v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time --提取SQL语句:-...
  • 快速Kill 一个Oracle Session

    千次阅读 2009-12-01 00:07:00
    为什么kill一个Session,进程状态为Killed?可能两种原因: 1....较缓和的kill(有回滚)我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:ALTER SYSTEM KILL SESSION
  • 由于锁的机制,当某一条DML或者DDL SQL语句执行被阻塞的时候,需要找出是什么原因导致这条SQL被阻塞了,下面介绍一下通常的诊断方法:假设有这样一个表: table t(id int primary key,val int);数据为:id val1 12 21,在一...
  • 【[文章] 数据字典】Oracle session相关数据字典 甲骨人 管理Oracle Session是后台DBMS采用Oracle的信息管理系统的一个重要工作。如果管理不当,会对系统的性能和运行的稳定性产生非常大的影响。那么以下是...
  • (一)session相关视图 (1)视图 v$session v$active_session_history dba_hist_active_session_history 如果是多节点数据库,v$session和v$active_hist_session_history仅获得单节点的信息,可以使用gv$...
  • Oracle中诊断阻塞session

    千次阅读 2012-06-01 15:12:05
    由于锁的机制,当某一条DML或者DDL SQL语句执行被阻塞的时候,需要找出是什么原因导致这条SQL被阻塞了,下面介绍一下通常的诊断方法: 假设有这样一个表: table t(id int primary key,val int);数据为: id ...
  • V$session参数SADDR: session addressSID: session identifier,常用于连接其它列。SERIAL#: sid 会重用,但是同一个sid被重用时,serial#会增加,不会重复。AUDSID: audit session id。可以通过audsid查询当前...
  • 该楼层疑似违规已被系统折叠隐藏此楼查看此楼session1;create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;alter table T_ALL_OBJS add constraint pk_t_all_objs primary...
  • Oracle Kill Session 方法详细说明

    千次阅读 2019-05-16 13:45:59
    Oracle 数据库中经常会出现session阻塞等导致CPU使用率异常的情况,而一个快速解决这个故障的方法就是kill session,可以直接kill 所有session,也可以使用hanganalyze 工具找到具体的session 在kill。 关于...
  • Oracle中诊断阻塞session的方法 blocking error 由于锁的机制,当某一条DML或者DDL SQL语句执行被阻塞的时候,需要找出是什么原因导致这条SQL被阻塞了,下面介绍一下通常的诊断方法: 假设有这样一个表: table t...
  • -- showlock.sql - show all user locks-- originally from tsawmiller on the ORACLE-L list-- and morphed somewhat-- speeded up greatly by changing order of where clause,-- and using all_objects instead o...
  • 1,树形结构分级别显示会话之间的阻塞关系set lines 200 pages 100col tree for a30col event for a40select *from (select a.inst_id, a.sid, a.serial#,a.sql_id,a.event,a.status,connect_by_...
  • oracle blocking session

    2014-08-22 15:14:58
    以上测试不难看出blocking_session字段表示了128这个session被blocking_session字段的值即129session阻塞了。 但有活动事务对对象加锁的时候,会在v$locked_object视图中有记录如object_id,session_id等,通常被...
  • Oracle 查询阻塞

    千次阅读 2015-08-27 10:14:47
    如果你的 SQL 或系统突然 hang 了,很有可能是因为一个 session 阻塞了另一个 session,如何查询是否发生阻塞了呢?看看下面的 SQL吧。 select blocksession.sid as block_session_sid, blocksession.serial# ...
  • 1. 查看活动会话信息[sql] view plain copySELECT * FROM V$SESSION WHERE USERNAME IS NOT NULL AND STATUS = 'ACTIVE' ORDER BY LOGON_TIME, SID; 注:status用来判断会话状态 Active:正执行sql语句(waiting for/...
  • 查杀oracle 死锁的一些帖子摘录[@more@]查找死锁进程column sid format 999;column b format 9;column object_name format a30;select v$loc...
  • Oracle中V$sessionsession相关信息

    万次阅读 2012-09-12 15:50:04
    V$session参数 SADDR: session address SID: session identifier,常用于连接其它列。 SERIAL#: sid 会重用,但是同一个sid被重用时,serial#会增加,不会重复。 AUDSID: audit session id。可以通过audsid查询...
  • 关于OracleSession的干掉问题。

    千次阅读 2004-09-17 11:25:00
    我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:alter system kill session sid,serial# ; 被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该...
  • Oracle会话阻塞

    千次阅读 2018-01-07 21:11:29
    单实例的会话阻塞 模拟232会话被1224会话阻塞的情况 ... select * from V$SESSION_BLOCKERS; SID SESS_SERIAL# WAIT_ID WAIT_EVENT WAIT_EVENT_TEXT BLOCKER_INSTANCE_ID BLOCKER_SID BLOCKER_SESS_SERIAL# --

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 18,016
精华内容 7,206
关键字:

oraclesession阻塞