ORA-00060: deadlock detected while waiting for resource

In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks. Can read from https://unirac.in/blog/what-is-deadlock-in-oracle-database/

A trace file is generated for the offending session in diag destination.

The blocking condition being encountered is likely to be on either the payments table or on one of its two indexes: paymt_pk or ptmytidx2.

Solution

To resolve the deadlocking error, the  user must re-create the payments table indexes or table and increase the value for the number of initial transaction slots in the objects block's header.

For review Oracle Metalink document 62354.1 "TX Transaction locks - Example wait scenarios" under the section, "Waits due to Insufficient 'ITL' slots in a Block".

To increase the initrans value for the indexes, the user rebuilds the index and specifies the new value.

SQL> ALTER INDEX paymt_pk REBUILD INITRANS 8;

Index altered.

SQL> ALTER INDEX ptmytidx2 REBUILD INITRANS 8;

Index altered.

For increasing the size on the payments table, alter the table.

SQL> ALTER TABLE payments INITRANS 8;

Table altered.