7 Replies Latest reply: Dec 14, 2012 8:19 AM by Richard Harrison . RSS

    Cannot create any objects at all

      I have an Oracle database running our applications just fine.
      I tried modifying some tables and creating some views but I receive following error :

      ORA-00604: error occurred at recursive SQL level 2
      ORA-00060: deadlock detected while waiting for resource

      I stopped and started the database to be sure there would be no locks around. But still the same.

      After some more testing it looks like I cannot create any object at all. Some googling came up with the fact my data dictionary might be corrupt.

      I guess I can just export the database and recreate it and import again. I don't want use my RMAN backup to rollback as data has already been created since I first had the problem.

      Anyone have had this issue before and what did you do ?
        • 1. Re: Cannot create any objects at all
          Marcus Rangel
          Every deadlock generates an entry on the alert.log file; each entry will point you to a trace file that contains every detail about the deadlock.
          • 2. Re: Cannot create any objects at all
            In addition to checking the deadlock trace, have you done anything "interesting" with things like DDL triggers? You can definitely get yourself tangled up in knots if you happen to have a misbehaving DDL trigger that you've forgotten about-- I've done that to myself on my local instance a few times.

            • 3. Re: Cannot create any objects at all
              The trace was the first think I looked up myself but it just gave me the info that the session locked itself.

              I haven't used any ddl triggers.
              • 4. Re: Cannot create any objects at all
                Are you saying you looked at the deadlock trace and the trace indicated that the session deadlocked with itself? A deadlock inherently requires at least two different processes holding locks and blocking each other. It shouldn't be possible for a session to deadlock itself (outside of, maybe, some autonomous transaction oddities). Can you post the deadlock trace that leads you to believe that the session deadlocked with itself?

                • 5. Re: Cannot create any objects at all
                  This is part of the trace file :

                  *** ACTION NAME:() 2012-12-13 15:28:25.874
                  *** MODULE NAME:(SQL*Plus) 2012-12-13 15:28:25.874
                  *** SERVICE NAME:(oramult) 2012-12-13 15:28:25.874
                  *** SESSION ID:(1062.879) 2012-12-13 15:28:25.874
                  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
                  TX-000a002d-0098a33e 22 1062 X 22 1062 S
                  session 1062: DID 0001-0016-0000000C     session 1062: DID 0001-0016-0000000C
                  Rows waited on:
                  Session 1062: no row
                  Information on the OTHER waiting sessions:
                  End of information on OTHER waiting sessions.
                  Current SQL statement for this session:
                  delete from obj$ where obj# = :1
                  • 6. Re: Cannot create any objects at all
                    Marcus Rangel
                    Well, this one is really weird. Do you have access to MOS/Metalink so you can open a SR ?
                    • 7. Re: Cannot create any objects at all
                      Richard Harrison .
                      Very odd - the other strange thing is that the trace file shows a delete from obj$ implying that an objects of some sort is being dropped when you try and create something? I can only think that this is a recyclebin issue maybe (try purge dba_recyclebin) or there is some ddl trigger in place doing something, or as you first implied a dictionary corruption of some sort.