Re: Getting rid of ConText Indices with huge queues...

Gerhard Moeller wrote:
>
> I have several tables for which ConText Policies and Indices exist,
> however, they are not needed. So I would like to get rid of them with
> ctx_ddl.drop_index and ctx_ddl.drop_policy. However, the dml_queue for
> those policies are rather big (several million entries). Now what
> happens when issuing a drop_index statement is...it takes hours for
> complete. Unfortunatelly, I have a dozen policies for which this is
> true. Do I really have to wait for several days?

It's taking a long time because we need to delete from the DML queue. Now, if you can control other policies' DML, then you can do the following:

1. make sure there is no pending DML for policies you want to keep.
2. as ctxsys, truncate drq_pending. (You shouldn't have rows in drq_inprog, if you have a drq_inprog queue)
3. now you should be able to drop the text index in minimum time.

-- 
---------------------------------------------------------------------
Garrett Kaminaga                          Oracle ConText Server Group
---------------------------------------------------------------------
Note: remove underscores to get real email address 
Standard disclaimers apply -- all posts are personal opinion and do 
not nec. reflect those of Oracle Corporation, etc. etc.