Re: Object refuses to drop

Anurag Varma wrote:

> 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"
> >
> >

Good point. I'll be watcing to see Maximus' solution.

--
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)