Oracle Trace File Full

Geert_Gruwez,
few points here with deadlocks there is nothing you can do to fix as oracle will do it for;
What is a TX lock ?
~~~~~~~~~~~~~~~~~~~
  A TX lock is acquired when a transaction initiates its first change and is
  held until the transaction does a COMMIT or ROLLBACK. It is used mainly as
  a queuing mechanism so that other sessions can wait for the transaction to
  complete. The lock name (ID1 and ID2) of the TX lock reflect the transaction
  ID of the active transaction.


all what you need to do is to avoid them ob the app level with good code;
as the app which issues the update statements has no strict ordering of the rows it updates.
apps can avoid row-level lock deadlocks by enforcing some ordering
 of row updates. This is purely an application design issue.


==================
NO 2
as from the trace file d:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc.
name it is a job q process trace file ext j002_148.trc. says that;
what happened that while the job was running the dead lock happened and the trace file was created because of the J process ;in a cse like that there could be timestamp diffrerenece between the alert log and the trace file.

this is different from a dbwr or lgwr cases where you have trace files with the name of the process as the error message was not critical to the life of the instance and a mandatory process was not engaged.

just check why this dead lock happened from the app code side.

If trace file is not in its original location then check for default location for windows installation. for Unix the default log folder is $ORACLE_HOME/rdbms/log

i used the sqlplus and
oradebug tracefile_name
to find the file of the trace

i am using this trace file and the information in it to find eaxctly what statements are producing the deadlocks

and now i don't have any information any more ...
as the *** trace file full ***

i know what a deadlock is and why it happens
i am trying to show the people developing what code caused the deadlock ...
but i can't, as i don't have the info

i need the info to help them from stopping the deadlocks occuring
i need to find a way to :
restart the info in the trace file

i get this on a other server where i mark the problematic code:

<alert text>
*** 2010-01-11 15:13:39.052
*** SESSION ID:(45.41541) 2010-01-11 15:13:39.052
DEADLOCK DETECTED
Current SQL statement for this session:DELETE FROM TOHOSTSEGMENTSTAT WHERE SEGMENTID <:B2 AND INSTANCEID = :B1 AND STATE = 'D'
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
A43535EC         9  anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090010-0026cd77       110      45     X            122     115           X
TX-00040005-002645bf       122     115     X            110      45           X
session 45: DID 0001-006E-00001A36    session 115: DID 0001-007A-000003F6
session 115: DID 0001-007A-000003F6    session 45: DID 0001-006E-00001A36
Rows waited on:
Session 115: obj - rowid = 00001B3B - AAABs7AAEAAANRSAAE
  (dictionary objn - 6971, file - 4, block - 54354, slot - 4)
Session 45: obj - rowid = 00001C0D - AAABwNAAFAAANaxAC5
  (dictionary objn - 7181, file - 5, block - 54961, slot - 185)
Information on the OTHER waiting sessions:
Session 115:
  pid=122 serial=5466 audsid=33176306 user: 34/LOCUS
  O/S info: user: apse13, term: SRVAPSE13, ospid: 5088:5744, machine: EMEA\SRVAPSE13
            program: tohora_QSSE_PRD.exe
  application name: tohora_QSSE_PRD.exe, hash value=0
  Current SQL Statement:
 
SELECT NVL (SI.MAXVALUE, SG.MAXVALUE) MAXVALUE, NVL (SI.MINVALUE, SG.MINVALUE) MINVALUE, SI.SEQUENCEITEM, SI.VALUE FROM SEQUENCEGROUP SG, SEQUENCEITEM SI WHERE SI.SEQUENCEGROUP =:B2 AND SI.SEQUENCEITEM =:B1 AND SG.SEQUENCEGROUP =SI.SEQUENCEGROUP FOR UPDATE OF SEQUENCEITEM
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
     process: 9FCBC7CC, call: A76055EC, xact: A11CD680, curses: 9FDB5254, usrses: 9FDB5254
  ----------------------------------------
  SO: 9FCBC7CC, type: 2, owner: 00000000, flag: INIT/-/-/0x00
  (process) Oracle pid=110, calls cur/top: A76055EC/A25EB4B0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 198 0 4
              last post received-location: kslpsr
              last process to post me: 9fca29bc 1 6
              last post sent: 0 0 16
              last post sent-location: ksasnd
              last process posted by me: 9fca29bc 1 6
    (latch info) wait_event=0 bits=10
      holding     21999a8 Parent+children enqueue hash chains level=4
        Location from where latch is held: ksqcmi: kslgpl:
        Context saved from call: 0
        state=busy
        recovery area:
Dump of memory from 0x9FCA195C to 0x9FCA1964
9FCA1950                            00000000              [....]
9FCA1960 00000000                             [....]            
    Process Group: DEFAULT, pseudo proc: 9FD95C70
    O/S info: user: SYSTEM, term: SRVORAE02, ospid: 2888
    OSD pid info: Windows thread id: 2888, image: ORACLE.EXE
    ----------------------------------------
    SO: 9FDB5254, type: 4, owner: 9FCBC7CC, flag: INIT/-/-/0x00
    (session) trans: A11CD680, creator: 9FCBC7CC, flag: (48100041) USR/- BSY/-/-/-/-/-
              DID: 0001-006E-00001A36, short-term DID: 0000-0000-00000000
              txn branch: 00000000
              oct: 7, prv: 0, sql: A434A5B0, psql: A434A5B0, user: 649/HOSTCOMM
    O/S info: user: apse13, term: SRVAPSE13, ospid: 5032:6012, machine: EMEA\SRVAPSE13
              program: tohora_QSSE_PRD.exe


And this info is missing ...

Geert_Gruwez:
have you seen my comment?

yes, i have seen your comment

i renamed the old file
i then moved this file to a other directory

i then searched for the file
and it says :
---------------------------
d:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc
---------------------------
Windows cannot find 'd:\oracle\admin\w06eprd\bdump\w06eprd_j002_148.trc'. Make sure you typed the name correctly, and then try again. To search for a file, click the Start button, and then click Search.
---------------------------
OK  
---------------------------

so the timestamp difference isn't the problem
there just isn't a new file

meanwhile, i have set the following

 alter system set max_dump_file_size = 100240;

i'm hoping this will resolve it

you have moved the trace file;and the even already happened.
which means the oracle engine is not dumping data into trace ;
why do you expect to find the trace again or that oracle will ever recreate it?

I recreated the same file in the same location also
and emptied it, i only left in the first line.

well i expected oracle to recreate it ...

just do not worry about the whole thing oracle will rectify dead locks for you.
the code needs to be changed to a good model;a TX lock is a dml as I have mentioned!

i know it is caused by the dml
that's not the issue

the problem is there is trillions of dml happening on the database
i wanted the information of which 2 statements out of those trillions of statements ...

select s.username, s.status,  s.sid,     s.serial#,
             p.spid,     s.machine, s.process, s.lockwait
      from   v$session s, v$process p
      where  s.process  = 'the process from the trace file'
      and    s.paddr    = p.addr
      /

This article shows the steps necessary to identify the offending application code when a deadlock is detected;as it looks like this will happen with you again

http://www.oracle-base.com/articles/misc/Deadlocks.php

But whatever info you are trying to find is already there in your trace file.
Current SQL statement for this session:
DELETE FROM TOHOSTSEGMENTSTAT WHERE SEGMENTID <:B2 AND INSTANCEID = :B1 AND STATE = 'D'
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
A43535EC         9  anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                      ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090010-0026cd77       110      45     X            122     115           X
TX-00040005-002645bf       122     115     X            110      45           X
session 45: DID 0001-006E-00001A36    session 115: DID 0001-007A-000003F6
session 115: DID 0001-007A-000003F6    session 45: DID 0001-006E-00001A36
Rows waited on:
Session 115: obj - rowid = 00001B3B - AAABs7AAEAAANRSAAE
 (dictionary objn - 6971, file - 4, block - 54354, slot - 4)
Session 45: obj - rowid = 00001C0D - AAABwNAAFAAANaxAC5
 (dictionary objn - 7181, file - 5, block - 54961, slot - 185)
Information on the OTHER waiting sessions:
Session 115:
 pid=122 serial=5466 audsid=33176306 user: 34/LOCUS
 O/S info: user: apse13, term: SRVAPSE13, ospid: 5088:5744, machine: EMEA\SRVAPSE13
           program: tohora_QSSE_PRD.exe
 application name: tohora_QSSE_PRD.exe, hash value=0
 Current SQL Statement:
 
SELECT NVL (SI.MAXVALUE, SG.MAXVALUE) MAXVALUE, NVL (SI.MINVALUE, SG.MINVALUE) MINVALUE, SI.SEQUENCEITEM, SI.VALUE FROM SEQUENCEGROUP SG, SEQUENCEITEM SI WHERE SI.SEQUENCEGROUP =:B2 AND SI.SEQUENCEITEM =:B1 AND SG.SEQUENCEGROUP =SI.SEQUENCEGROUP FOR UPDATE OF SEQUENCEITEM
End of information on OTHER waiting sessions.

 
This is enough for you to send to application guys for investigation

Fayyaz:
I think the asker@ ID: 26284498 said
i get this on a other server where i mark the problematic code:
and he meant he saw a dead lock trace file before on another server that looked like that!

may be i am wrong but this is what I understood!

it-rex,
i actually sent them the same link about the deadlocks

Fayyaz,
that was for a other server as i stated

okay, forget the deadlocks, that's not the problem

the problem is a trace file which is full
if i delete the trace file, oracle doesn't recreate it
i don't know yet, if after recreating the file empty,
if oracle now writes new info into it

i'm still waiting for a new deadlock to occur ...

the deadlocks themselves are not the problem,
it's the info produced by oracle which doesn't get written to the trace file.

am i so unclear ?