Re: Object refuses to drop

Maximus,

Before you go that path, make sure the object was not created with an accidental newline character or control character.

First do a
SELECT OWNER, OBJECT_NAME, DUMP(OBJECT_NAME), OBJECT_TYPE FROM ALL_OBJECTS WHERE OWNER='PROJECT'; and see if there are any suspicious characters in there.

If there are then create a drop statement like this:

select 'drop type "PROJECT"."' || attribute_type || '";'  FROM ALL_OBJECTS WHERE OWNER='PROJECT'; .. and if this does not work .. then open an iTar (I guess you already know that).

I highly suspect a newline character or something at the end...

HTH
Anurag

"Maximus" <qweqwe_at_qwqwewq.com> wrote in message news:Gdy_a.747551$Vi5.17032421_at_news1.calgary.shaw.ca...
>
> "Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message
> news:3F3A9F90.7E46F1D5_at_remove_spam.peasland.com...
> > Two things to try...first prepend the object_name with its
> > owner...second, issue the DROP command as someone who has the DROP ANY
> > TYPE system privilege.
>
> I tried the fully qualified identifer, both quoted and unquoted:
>
> DROP TYPE "PROJECT"."ATTRIBUTE_TYP"
> ...and...
> DROP TYPE PROJECT.ATTRIBUTE_TYP
>
> The error message (ORA-04043) indicates the object doesn't exist, no errors
> about priveledges though. I've also tried dropping it as user SYS AS
> SYSDBA... same error.
>
> Well, I guess it's time to explore the world of corruption repair!
>
> >
> > HTH,
> > Brian
> >
> > 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?
> >
> > --
> > ===================================================================
> >
> > Brian Peasland
> > dba_at_remove_spam.peasland.com
> >
> > Remove the "remove_spam." from the email address to email me.
> >
> >
> > "I can give it to you cheap, quick, and good. Now pick two out of
> > the three"
>
>
Received on Wed Aug 13 2003 - 17:12:22 CDT