Re: when table is dropped, index is not dropping also ????

hello,

    tried the search on the data dictionary which yield the fact that the index was made on another (same name) object in another schema, even though i did not indicate it to use the wrong object in the other schema.

    so, when i dropped the table in my schema, the index did not drop but was left on the other object.

    odd?

    makes sense..... the kind of sense i'd rather not learn to use.

    moral of the story... don't leave anything to chance....

    explicitly name the schema.table when working on any object

    thanks for the help again.

--
John
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:970163079.14203.0.nnrp-12.9e984b29_at_news.demon.co.uk...


>

> Try this:

>

> select table_owner, table_name, owner

> from all_indexes

> where index_name = 'whatever the guilty index is';

> ;

>

> then

>

> select owner, object_type

> from all_objects

> where object_name = 'whatever the guilty index is'

> ;

>

> --

>

> Jonathan Lewis

> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>

> John A. Pearson wrote in message

> <_jLA5.1373$RX2.64814_at_newsread1.prod.itd.earthlink.net>...

> >no luck,

> >

> >same error....

> >

> >i actually dropped the table by hand and the index stayed..

> >

> >i'm now thinking that i might have something not so good here......

> >

> >anyone seen this before and what is a possible solution?

> >

> >i 'm going to start dropping the indexes, in the mean time,

 explicitly......


> >

> >thank you

> >

> >--

> >John

> >"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

> >news:970161274.26929.0.nnrp-10.9e984b29_at_news.demon.co.uk...

> >>

> >> Is it possible that an index of the name you

> >> want has been created on another table ?

> >>

> >> It shouldn't be possible for an index to

> >> exist without its table.

> >>

> >> --

> >>

> >> Jonathan Lewis

> >> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

> >>

> >> John A. Pearson wrote in message ...

> >> >hello,

> >> >

> >> >    version 8.0.5. on sun solaris 2.8

> >> >

> >> >    again, though no table, but index still exists.

> >> >

> >> >    however, thank you

> >> >

> >> >--

> >> >John

> >> >"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

> >> >news:970155095.27271.1.nnrp-03.9e984b29_at_news.demon.co.uk...

> >> >>

> >> >> You don't say which version of Oracle you are using.

> >> >>

> >> >> dbms_sql execute DDL at the parse call. I would be

> >> >> inclined to suspect that you are getting this error

> >> >> because the create statement is being executed

> >> >> twice, possibly on the parse and on the execute

> >> >> (which would be a bug in dbms_sql).

> >> >>

> >> >> Try putting an exception clause around the parse

> >> >> and around the execute and seeing where the trap

> >> >> occurs.

> >> >>

> >> >> --

> >> >>

> >> >> Jonathan Lewis

> >> >> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

> >> >>

> >> >> John A. Pearson wrote in message ...

> >> >> >hello,

> >> >> >

> >> >> >    during a refresh process, a table is dropped, then

> >> >> >recreated via a view using dbms_sql to build statements.

> >> >> >

> >> >> >    ora-00955 object already exists error occur, throwing

> >> >> >refresh.

> >> >> >

> >> >> >    i can catch the error in an error exception handler, but

> >> >> >

> >> >> >---- is it not TRUE that when a table is dropped, ALL

> >> >> >indexes drop also ----

> >> >> >

> >> >> >    error is occuring due to pre-existing index that should

> >> >> >have been dropped with the table

> >> >> >

> >> >> >    odd?

> >> >> >

> >> >> >    any information will be much appreciated.

> >> >> >

> >> >> >--

> >> >> >John

> >> >> >

> >> >> >

> >> >>

> >> >>

> >> >>

> >> >

> >> >

> >>

> >>

> >

> >

>

>