Unusable partition index -- working funny

From: <sat0789_at_fastmail.fm>
Date: Tue, 20 Jan 2004 16:19:25 -0800
Message-ID: <F001.005DDAFB.20040120161925@fatcity.com>

Hello All,
I have a strange problem...
I have a table on which i am doing an update. Its a partition table and the local index on the column which is being updated is in an unusable state.
I have a database trigger at statement level (before update of col_a for ....) where i do an execute immediate ' alter session set skip_unusable_indexes = true';

i log into sqlplus as the owner of the table and do the following

SQL> connect nevadmin_at_nv_prod
Enter password: ******
Connected.
SQL> UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 11111 where
2 mortgage_loan_key = 11111 and period_key = '30-JUN-03'; UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1166444 where
*
ERROR at line 1:
ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of such
index is in unusable state

SQL> / 1 row updated.

My question is why does the trigger not fire for the first time... When i do the / i am able to update the table which means the trigger is firing the 2nd time.

Any help would be greatly appriciated..

thanks,

sathish.

--

http://www.fastmail.fm - Consolidate POP email and Hotmail in one place
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: sat0789_at_fastmail.fm

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 20 2004 - 18:19:25 CST