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!