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 - 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:                                                                                                                                                    

----- 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.

  • Really interesting question, but wondering if you'll get more DBA love on the DBA site. We can flag to have it moved if you like. – khoxsey Aug 20 '12 at 19:29

It might be helpful to see the actual resource(s) in contention, but I'm guessing that both operations are trying to touch the tablespace metadata and so locking each other out. A good test of that would be to re-arrange your tasks so the alter tablespace command didn't have such a huge run-out (creating the datafile):

imp dumpfile control=blahblah.ctl
create datafile ....
alter tablespace add datafile...

However, on a machine running 11g and with the resources you describe, I would recommend taking a look at using ASM rather than managing files manually. ASM makes a DBA's life so much easier (Well, mine anyway).

  • That sounds like a sensible guess. I am curious though why it never happened before but only on this particular Oracle11 instance. Although that's not easy to pinpoint of course. By the way the adding of datafiles is to prevent the tablespace from filling up during import and I don't want to over commit tablespace which then is hard to retrieve back from the database. Yes I have thought about ASM and I might as well start using it at some point. Luckily tablespace size is rather stable after import, during normal usage. – aseq Aug 20 '12 at 19:32
  • Understood about manual control of disk space, and the unknowns of a random issue. One of the fun things about Oracle is the depth of arcane detail one can wallow around in. GL. – khoxsey Aug 20 '12 at 19:36
  • Yeah I see your point :-D I added some more detail from the trace file, I don't know how to actually find the resources the "Resource Name" is pointing to. A quick search didn't help much either: dbaforums.org/oracle/index.php?showtopic=2631 – aseq Aug 20 '12 at 19:57
  • The "no row" looks familiar and might be a clue. Any chance your dumpfile includes tables with unindexed foreign keys? Just a thought. – khoxsey Aug 20 '12 at 20:22
  • I found this asktom.oracle.com/pls/asktom/… which provides a script to query for unindexed foerign keys. Indeed there are tables with unindexed foreign keys. However older schemas on Oracle10 have also have this and those never showed any problems. – aseq Aug 28 '12 at 23:44

According to this website (that I found with a very simple web search) it's because your session conflicted with another:

ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.

Read more from that article for more information about deadlocks.

  • Much more concise answer than my plan to regurgitate CPSC2xx-3xx courses to explain what a deadlock was (dining philosphers, anyone?). – DerfK Aug 18 '12 at 3:23
  • Thanks, yes I read about deadlocks and did pretty much the same search you did. However it isn't clear to me why adding a datafile would cause a deadlock whilst importing a dumpfile. As far as I can see the datafile does NOT become available to the database until it's finished creating. This does not appear like a situation that would normally cause a deadlock. – aseq Aug 18 '12 at 4:29
  • If you could please elaborate on your answer, I must just overlook something totally obvious. – aseq Aug 18 '12 at 4:36
  • 1
    Look in the Oracle alert log. Right after the "ORA-00060: Deadlock detected" it should give a path to a trace file... As the link bahamat provided says: "Action: Look at the trace file to see the transactions and resources involved." – George3 Aug 18 '12 at 21:00
  • I understand what you're trying to say, but unfortunately the trace file does not provide the information I am after. It does show the exact statements causing the deadlock (create table and add datafile, which I already figured) and a whole lot of detailed information such as memory dumps and stack traces. However the exact cause is not clear, which makes sense, that's not oracle's business. I am hoping that someone is able to give an informed guess about what would cause this, or had this happening before and found out the underlying reasons. – aseq Aug 20 '12 at 18:32

Well, it's a very old post, but I get to that page while trying to solve same problem with my Oracle 12c server. Finally it was because I reached the default limit of 200 datafiles configured in control files:

SQL> select max(FILE_ID) from DBA_DATA_FILES;

I had to increase that value, and then I could create a new tablespace o add a datafile to a existing one:

alter system set db_files = 2000 scope = spfile;
dbshut / dbstart

A deadlock occurs when 2 sessions block each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back. There can be more than 2 sessions involved, but the main idea is the same.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.