Why would adding a datafile cause a deadlock in Oracle?

Solution 1:

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

Solution 2:

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.

Solution 3:

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;
MAX(FILE_ID)
------------
         200

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