I have noticed something in Oracle which never happened before to me. Though I frequently do this kind of thing for many years now. In this case it's an Oracle11 instance (Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit running on Linux to be precise).
I am importing a dumpfile and at the same time I am increasing the tablespace using statements such as this:
alter tablespace example add datafile '/path/to/oradata/instance/exampleXX.dbf' size 31000m;
I sometimes get the following error when running the above statement:
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
I can restart the alter tablespace statement and it mostly will go all right the second attempt.
I am asking why this happens because it is unusual to me. Does it indicate a bug or impeding filesystem failure? I understand running an import and adding datafiles to a tablespace at the same time may be a lot to ask of a server, however this is server has 4 CPUs, 64 GB and a few TB of SAS raid10 at its disposal.
This is not a production system by the way.
Relevant trace file output:
Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TT-00000006-00000010 36 11 SX 38 142 S TT-00000006-00000010 38 142 S 36 11 SX session 11: DID 0001-0024-0000005D session 142: DID 0001-0026-00000068 session 142: DID 0001-0026-00000068 session 11: DID 0001-0024-0000005D Rows waited on: Session 11: no row Session 142: no row ----- Information for the OTHER waiting sessions ----- Session 142: sid: 142 ser: 30639 audsid: 0 user: 5/SYSTEM flags: 0x8000051 pid: 38 O/S info: user: example_user, term: UNKNOWN, ospid: 14589 image: example@example (DW00) current SQL: CREATE TABLE EXAMPLE ... ----- Error Stack Dump ----- ORA-00060: deadlock detected while waiting for resource ----- Current SQL Statement for this session (sql_id=6bvzdgdn8vqqt8) ----- alter tablespace ...
The stacktrace and memory dump could provide more information however I am not a specialist in troubleshooting those. What I am after is an idea what it might be, someone who had this happen before who could make an informed guess. If it is a bug, a sign the filesystem may be having issues, or that the raid controller couldn't handle the load. Why were the two statements waiting for the resource and why was this resource unavailable.