This discussion is archived
7 Replies Latest reply: Dec 14, 2012 6:19 AM by Richard Harrison . RSS

Cannot create any objects at all

JohanVyt Newbie
Currently Being Moderated
I have an Oracle 10.2.0.3 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 Journeyer
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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.

    Justin
  • 3. Re: Cannot create any objects at all
    JohanVyt Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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?

    Justin
  • 5. Re: Cannot create any objects at all
    JohanVyt Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    Hi,
    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.

    Regards,
    Harry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points