Re: Problem dropping an index

"Fergus" <sinhask_at_swbell.net> wrote in message news:IwH38.746$Yn2.332607646_at_newssvr30.news.prodigy.com...
>
> "Nasir Mohammed" <nasir_mohd_at_hotmail.com> wrote in message
> news:1011816812.763149_at_sj-nntpcache-3...
> > Hi,
> >
> > As part of our daily extract exercise, we drop index on a table (say
table
> > A) , insert data into the table and then recreate the index back. The
> > process stopped today at the step where it was trying to drop the index.
>
> Did you try to figure out what the session was upto?

Yes, the session was going to insert data into the table A. Dropping the index on table A was first step in that process.
>
> It
> > gave no error or anything. I killed the session before which it ran for
> > several hours.
>
> How? kill -9? alter system kill?

I killed both OS and Oracle sessions. Kill -9 and Alter System kill .....

>
> During the time that session was running, even a describe on
> > that table A won't work. After killing that session, I tried dropping
> that
> > index again, which didn't work.
>
> what error do you get?

It doesn't give any errors. The session just hangs. During this time, even describing the table from other session doesn't work.

>
> > I checked up in v$session and v$locked_objects and found that no session
> is
> > holding any locks on the table. I also queried the v$sqltext view to
find
> > out any sql statement which could be using that index, thinking that if
> any
> > object is using that index, oracle won't allow the index to be dropped.
> But
> > I couldn't find any query which could be using that index.
> >
> > Any pointers/help would be very much appreciated ... ORACLE version is
> > Oracle8i Enterprise Edition Release 8.1.7.2.0 .
> >
> > Thx,
> > Nasir
> >
> >
>
>
Received on Wed Jan 23 2002 - 17:36:37 CST