how to troubleshoot oracle excessive trace file in DFWorks

locate a logical error in one of the .trc file
note the object id (ex. 12345)

FIND tablespace and table name for problematic index

stop following service:
Apache2.2, Apache Tomcat, DFWorks UDH App, DFWorks UDH Web

in a command promt
.      Sqlplus /nolog
1.      SQL>              Connect sys/superfly@smartdb as sysdba;
2.   SQL>              select object_type, object_name from dba_objects where data_object_id = 12345;   
(ex. I had DLX_IDX_RECIPIENTADDRESS1)
3.   SQL>              select tablespace_name from dba_indexes where index_name=<object_name from step #2, surrounded by single quotes>;   (Don’t type the ‘<’ or ‘>’).
(In my case:select tablespace_name from dba_indexes where index_name='DLX_IDX_RECIPIENTADDRESS1';
TABLESPACENAME is PARI)
4.   In a different Command Prompt window:
RMAN>        validate check logical tablespace <result from step #3>;                                                                                                                                      (Don’t type the ‘<’ or ‘>’).
5.   In the 1nd Command Prompt window:
SQL>              select table_name from dba_indexes where index_name=<object_name from step #2, surrounded by single quotes>;         (Don’t type the ‘<’ or ‘>’).
(In my case:select table_name from dba_indexes where index_name='DLX_IDX_RECIPIENTADDRESS1';
TABLE_NAME is MP_DETAIL)

now find all index still pending (with the value I had earlier)

Select index_name, table_name from all_indexes where index_name like ‘DLC_IDX_RECI%’ ;

result:
index name                         table name
DLX_IDX_RECIPIENTADDRESS1          MP_DETAIL
DLX_IDX_RECIPIENTADDRESS2          MP_DETAIL
DLX_IDX_RECIPIENTNAME              MP_DETAIL

now drop and recreate index (for each index)

Sqlplus /nolog
2. SQL>              Connect dfworks/dfworks2000@smartdb;
3. SQL>              DROP INDEX DFWORKS.DLC_IDX_RECIPIENTADDRESS1;
4. SQL>              CREATE INDEX DFWORKS.DLC_IDX_RECIPIENTADDRESS1 ON DFWORKS.MP_DETAILS (UPPER("RecipientAddresse1")) TABLESPACE PARI;

Sqlplus /nolog
2. SQL>              Connect dfworks/dfworks2000@smartdb;
3. SQL>              DROP INDEX DFWORKS.DLC_IDX_RECIPIENTADDRESS2;
4. SQL>              CREATE INDEX DFWORKS.DLC_IDX_RECIPIENTADDRESS2 ON DFWORKS.MP_DETAILS (UPPER("RecipientAddress2")) TABLESPACE PARI;

Sqlplus /nolog
2. SQL>              Connect dfworks/dfworks2000@smartdb;
3. SQL>              DROP INDEX DFWORKS.DLC_IDX_RECIPIENTNAME;
4. SQL>              CREATE INDEX DFWORKS.DLC_IDX_RECIPIENTNAME ON DFWORKS.MP_DETAILS (UPPER("RecipientName")) TABLESPACE PARI;

restart:
Apache2.2, Apache Tomcat, DFWorks UDH App, DFWorks UDH Web