AskTom

Hi,

there is 20 steps how to install Oracle 11R2 on SLES10.

Pictures are only preview when you click on it you will see full size.

Step 1.

Step 2.

Step 3.

Step 4.

Step 5.

Step 6.

Step 7.

Step 8.

Step 9.

Step 10.

Step 11.

Step 12.

Step 13.

Step 14.

Step 15.

Step 16.

Step 17.

Step 18.

Step 19.

Step 19.

Step 19.

Step 20.

Step 20.

Regards,
Tom

Hi,

today I faced same deadlocks on our production.
In alert.log were this lines “ORA-00060: Deadlock detected”

This is part from trace file:

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

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
TM-0002de1f-00000000       165     202    SX   SSX      163     365    SX   SSX
TM-0002de1f-00000000       163     365    SX   SSX      165     202    SX   SSX

session 202: DID 0001-00A5-000F6F92    session 365: DID 0001-00A3-000BBFBC
session 365: DID 0001-00A3-000BBFBC    session 202: DID 0001-00A5-000F6F92

Rows waited on:
Session 202: obj - rowid = 0002C0EC - AAAsDsAAGAAA5RCAAA
(dictionary objn - 180460, file - 6, block - 234562, slot - 0)
Session 365: obj - rowid = 0002C0EC - AAAsDsAAGAACQhvAAA
(dictionary objn - 180460, file - 6, block - 591983, slot - 0)

Solution:
=========
Query dba_objects for data_object_id = 180460 which should be the item_dtl table.
Given you’ve got an exclusive mode deadlock (X’s held and waited for everywhere) this probably has nothing to do with foreign key deadlocking. Foriegn key deadlocks usually show a mode 4 or 5 (S or SSX) in the mix. This is simply:
session A locks row X
sesssion B locks row Y
session A tries to lock row Y
session B tries to lock row X
Session A fails after roughly 3 seconds with a deadlock ORA-00060
62365.1 What to do with “ORA-60 Deadlock Detected” Errors

Known Issues and References
~~~~~~~~~~~~~~~~~~~~~~~~~~~
62354.1 TX lock waits and why they occur
33453.1 TM locks and Foreign Key Constraints
38373.1 Example TM locks During Referential Integrity Enforcement

Regards,

Tom

Hi,
this is part from Julian Dyke’s blog. I put this selects there because are very usefull.

Listing All Events

Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use

SET SERVEROUTPUT ON

DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/

On Unix systems event messages are in the formatted text file

$ORACLE_HOME/rdbms/mesg/oraus.msg

To print detailed event messages (Unix only) use the following script

event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done

Listing Enabled Events

To check which events are enabled in the current session

SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line (’Event ‘||TO_CHAR (l_event)||
‘ is set at level ‘||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/

Regards,

Tom