Re: Object refuses to drop

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Thu, 14 Aug 2003 16:12:06 -0700
Message-ID: <3F3C1745.D1258D70@exxesolutions.com>

Maximus wrote:

> "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> news:3F3AC14E.E89D9B3_at_exxesolutions.com...
> > Maximus wrote:
> >
> > > "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> > > news:3F3A9821.CEF9BDF1_at_exxesolutions.com...
> > > > Maximus wrote:
> > > >
> > > > > database: Oracle9i Enterprise Edition Release 9.2.0.1.0
> > > > >
> > > > > I have been attempting to clean out a schema and there is an object
> that
> > > > > refuses to drop. If I issue "DROP TYPE ATTRIBUTE_TYP" the command
> fails
> > > > > with an ORA-04043, object does not exist. I've verified the object
> does
> > > > > indeed exist but any SQL to alter or drop it fails:
> > > > >
> > > > > SQL> CONNECT PROJECT/****@DB01.****.COM
> > > > > Connected.
> > > > > SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
> > > > > 2 FROM ALL_OBJECTS WHERE OWNER='PROJECT';
> > > > >
> > > > > OWNER OBJECT_NAME
> > > OBJECT_TYPE
> > > >
> > >
> > ------------------------------ ------------------------------ ------------
> > > --
> > > > > ----
> > > > > PROJECT ATTRIBUTE_TYP TYPE
> > > > >
> > > > > SQL> DROP TYPE ATTRIBUTE_TYP;
> > > > > DROP TYPE ATTRIBUTE_TYP
> > > > > *
> > > > > ERROR at line 1:
> > > > > ORA-04043: object ATTRIBUTE_TYP does not exist
> > > > >
> > > > > How the heck can I get rid of this object... go into sys.obj$ and
> delete
> > > it
> > > > > manually?
> > > >
> > > > Anyone that would even suggest going into sys.<anything> and making a
> > > change
> > > > should
> > > > have their keyboard taken away. I am staggered by the implications.
> > >
> > > Subtle way to tell me that's a no-no... lol
> > >
> > > > When you attempt to drop it are you logged on as project?
> > >
> > > Yes.
> > >
> > > The object also appears in Enterprise Manager under schema "PROJECT" and
> > > I've tried the GUI "remove" but same error... object does not exist.
> >
> > If you can drop and recreate the schema PROJECT do so.
> >
> > CONN / AS SYSDBA
> > DROP USER PROJECT CASCADE;

>

> Tried that already, same error "object does not exist."

>


> > Then recreate the objects and data you need from a backup or export.

> >
> > If you are convinced that this is a case of data dictionary corruption
> then run
> > catalog.sql, catproc.sql, and utl_rp.sql from the rdbms/admin directory.
> >
> > If that doesn't solve the problem then open an iTAR with Oracle. Under no
> > circumstances should you attempt to directly alter the data dictionary
> without
> > their input.

>

> I ran catalog.sql from rdbms/admin to recreate the data dictionary views ...
> I got about a gazillion errors and when the script was finished the database
> was completely inaccesible, inoperable and hoplessly corrupted. Never seen
> that happen before. I think the original error I ran into was just a
> symptom of a serious corruption problem. Resorted to my most recent backup
> and all seems to be okay, the project schema/user even dropped just fine ...
> good thing it's wasn't a live server.

>
> Arghhhh!

From what you say your conclusion is undoubtedly correct. If running catalog can make a mess ... you already had one.

Be very thankful you didn't go in an try editing catalog tables yourself.

Well that and the fact that you have valid backups.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Aug 14 2003 - 18:12:06 CDT