Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    8

    Unanswered: Deadlock in Oracle

    Hi all,

    I encountered a deadlock in oracle. When i checked the oracle error dump, I could see the following.

    <code>
    ----- Information for the OTHER waiting sessions -----
    Session 122:
    sid: 122 ser: 57632 audsid: 1887231 user: 89/ALFSEARSPROD flags: 0x45
    pid: 30 O/S info: user: oracle, term: UNKNOWN, ospid: 20629
    image: oracle@db1.localdomain
    client details:
    O/S info: user: root, term: unknown, ospid: 1234
    machine: app.localdomain program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
    Current SQL Statement:
    delete from alf_node where id=:1 and version=:2

    ----- End of information for the OTHER waiting sessions -----

    </code>

    I believe the deadlock is happening while executing the sql "delete from alf_node where id=:1 and version=:2". But I could not find a table by name "alf_node" in my schema. Can anyone tell me what could be the problem here ?

    Thanks in advance
    Vinod T R

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    did you try

    Code:
    select * from all_tables where table_name = 'ALF_NODE';
    or if you have the permissions

    Code:
    select * from dba_tables where table_name = 'ALF_NODE';

  3. #3
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    the table is may stay in other schema of your Database. so find how is the owner of that table. please query in DBA_TABLES .
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You should find somewhere else in the dump file an area that shows the object id that the deadlock is occurring on. It may be a hex number ...
    You can then query the dba_objects table to find the object where the lock is occurring ... if the object id is hex, you can run the script below to convert the hex to decimal. Take the decimal number (object id) and find the true name of the object with the lock.

    drop function HEXTODEC ;
    create function HEXTODEC (Hexnum in CHAR)
    RETURN NUMBER IS
    X NUMBER;
    Digits NUMBER;
    Result NUMBER := 0;
    Current_Digit CHAR(1);
    Current_Digit_Dec NUMBER;
    begin
    Digits := LENGTH(Hexnum);
    for X in 1..Digits loop
    Current_Digit := UPPER(SUBSTR(Hexnum, X, 1));
    if Current_Digit in ('A','B','C','D','E','F') then
    Current_Digit_Dec := ASCII(Current_Digit) - ASCII('A') + 10;
    else
    Current_Digit_Dec := TO_NUMBER(Current_Digit);
    end if;
    Result := (Result * 16) + Current_Digit_Dec;
    end loop;
    return Result;
    end;
    /

    --- Now run the above function using the hex number you found in the dump

    define x = '9558AA2C'

    column hex_key new_value hex_key_value noprint
    select hextodec('&x') hex_key from dual;

    select object_name from all_objects where object_id = '&hex_key_value';

    undefine x;


    Gregg

  5. #5
    Join Date
    Jun 2013
    Posts
    2

    How to find the object using object id in hex

    How to find an object using object id which is in Hex (0x19fa554a8, 0x18ee31738, 0x19fb09c00 and 0x19fb09ab0) as specified the WFG..

    Global Wait-For-Graph(WFG) at ddTS[0.49] :
    BLOCKED 0x19fa554a8 3 wq 2 cvtops x1 [0xd0005][0x166cd8],[TX] [4A000-0002-00002FCF] 1 <<<<<<<<<<<<<
    BLOCKER 0x18ee31738 3 wq 1 cvtops x28 [0xd0005][0x166cd8],[TX] [2E000-0002-000050B2] 1 <<<<<<<<<<<<<
    BLOCKED 0x19fb09c00 3 wq 2 cvtops x1 [0x13001b][0x17a652],[TX] [2E000-0002-000050B2] 1 <<<<<<<<<<<<<
    BLOCKER 0x19fb09ab0 3 wq 1 cvtops x28 [0x13001b][0x17a652],[TX] [4A000-0002-00002FCF] 1 <<<<<<<<<<<<<

    Thanks
    Sridhar

  6. #6
    Join Date
    Jun 2013
    Posts
    2
    Thanks Greg for your quick reply.

    Will check and update you accordingly.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do you realize that you have resurrected a FOUR year old thread?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •