Re: Object refuses to drop

"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! Received on Wed Aug 13 2003 - 19:22:39 CDT