SQL & PL/SQL

Like this

You can not disable the Index. You can make it unusable.

Check the URL given above for more details.

Regards
RK

You can - if it is a function based index:

SQL>  create index emp_idx on emp (upper(ename))
/
Index created.

SQL>  alter index emp_idx disable
/
Index altered.

SQL>  alter index emp_idx enable
/
Index altered.

Thank you Michels for pointing this!!

Regards
RK

Another way is to mark the index unsable.

alter index your_index unusable;

To remark your index usable, you must rebuild the index.(This sucks)

alter index your_index rebuild [online];

Check following parameter. If your index is marked unusable and "skip_unusable_indexes" is false, you DML will fail.

show parameter skip_unusable_indexes;
alter session set skip_unusable_indexes = true;

But if its a unique index then even if the parameter SKIP_UNUSABLE_INDEXES=True, DmL Wont work

HI,

Somesh,
I think it is one of the thousands bugs in oracle.

1.) If i create unique constraint (oracle internaly creates unique or non unique index for it depending if constrainti is deferreble)
i can dissable the constraint and insert the data.

2.) If i create the unique index and set it unusable oracle wont let to insert data.

hi,

i am presently doning mutilple files checkin to oracle UCM,

i am presently checked in 100 files in UCM using java api (provided in loop condition)

after executed the checkin page, it was able to checked in to UCM. but many files being indexed.

so, after checkedin the documents inside UCM, i wanted to view the all the documents immediatly. all the documents should publish/relase immediatly after checkedin done.

here i need to know how to stop the indexing in ucm after checking the documents.

how to disable the indexer, if i disable the indexer when documents being indexed is there impact will be happend to content server ?

how to save the time for avoiding indexing the all the documents.

please help me out . i need to know very urgent

Hello,
I have a question related to disabing index and thought I would post it in thread.

I'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".
On searching more about this error, it seems like the function index must be dropped before altering the column.
The table I'm dealing with is huge.

Question 1:
In case of dropping and recreating the index, should the following steps be done:
- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index

Question 2:
Is there anything else that should be done when the index is dropped and re-created?

Question 3:
What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?

Question 4:
Would it work if I disable the index, alter the column and reenable the index?
Do I have to rebuild the index and gather Stats upon reenabling it?


Thanks!

Just so everyone knows this last question is posted over here now: 2343555

MichaelS said:
>
alter index emp_idx disable
>
That solution won't work for this use case.
The statement works but you still get the original error if you try to alter the col.

Same applies for Dion's suggestion:

alter index your_index unusable;

Still can't alter the column.

Why not to temporarily drop that and later on recreate it?

Regards,