ORA -01410 Invalid ROWID - PLEASE HELP !!!!!!!!

Gurus ,  

There has been a tiresome 4 days and nights and we does not seem to go anywhere.  

We have this query running against a 8.1.7.4.0 Oracle 32-bit data warehouse on a Sun Solaris box which gives invalid ROWID error everytime we runt he query. This is the only query which is giving us this error and all the other queries seem to run fine.  

We had been constantly in touch with Oracle Support and could not get much help. They asked us to do the following things :  

(1) Run and ANALYZE TABLE <> VALIDATE STRUCTURE CASCADE; on all the tables. We did it for all the tables except one which is approx 50 GB in size and more than 350 million records. Other tables are also pretty big ranging few GB's in size. We ran the analyze command on this table but it took us 28 hours and we had to kill it. We did the HANG ANALYZE and took a system dump but everything looked fine.
 

(2) We ran DBVerify on all the files but even that could not complete because of the size of the db. Its roughly around 1.25 TB.
 

(3) We tried setting the 10046 Trace event on the session level and then run the query, but that hanged the query and we had to kill it after running it for more than 24 hours. Again we ran the HANG ANALYZE, and everything looked ok.
 

(4) Oracle suggested us to then drop, recreate and reanalyze all the indexes on all the tables (irrespective of whether the index is getting used in the query or not). We did that and then re-ran the query yesterday afternoon again, this time w/o any trace as Oracle ppl were quite confident that it will resolve the Invalid ROWID issue. But the query again errored out with Invalid ROWID.
 

(5) Then they asked us to run the query with Event ORA-1410 enabled but the query ran for a much longer time. Then we decided to tune the query and re-ran it again so that the query will run faster.
 

Query :  

SELECT /*+ PARALLEL_INDEX(DAILY_ACCT_BAL DAB_DAY_KEY_BIDX,32,Default) PARALLEL(DAILY_ACCT_BAL, 32) */ CDW.PL_COLLECTOR.LAST_NM, CDW.PL_COLLECTOR.FIRST_NM, CDW.PL_COLLECTOR.GC_ID, CDW.PL_COLLECTOR.SITE_NM,

       CDW.DAY.DAY_DT, CDW.TIME.HOUR_24, CDW.ACCOUNT.ACCT_ID, CDW.COMMENTS.ACTION_CD, CDW.COMMENTS.RESULT_CD,
       CDW.COMMENTS.DUE_STAGE,
       CDW.DAILY_ACCT_BAL.CURR_QUEUE_ID, CDW.DAILY_ACCT_BAL.PREV_QUEUE_DT, 
       CDW.DAILY_ACCT_BAL.PREV_QUEUE_ID, CDW.DAILY_ACCT_BAL.ROUTE_CD, CDW.DAILY_ACCT_BAL.CURR_BAL, 
       CDW.BRANCH.COLL_OP_PLAN_GROUP_NM, CDW.PROC_SEGMENTATION.PROC_SEG_LVL1_NM, CDW.PROC_SEGMENTATION.PROC_SEG_LVL2_NM, 
       CDW.PROC_SEGMENTATION.PROC_SEG_LVL3_NM, CDW.PROC_SEGMENTATION.PROC_SEG_LVL4_NM
FROM  CDW.PL_COLLECTOR, -- Small table
      CDW.DAY, -- Small table
      CDW.TIME, -- Small table
      CDW.ACCOUNT, -- Hash partitioned table, roughly around 8.5 GB size
      CDW.COMMENTS, -- Range partitioned table, roughly around 12 GB size
      CDW.DAILY_ACCT_BAL, -- Really the biggest partitioned table with around 50 GB size
      CDW.BRANCH, -- Small table
      CDW.PROC_SEGMENTATION -- Small table
WHERE CDW.COMMENTS.ACCT_KEY = CDW.ACCOUNT.ACCT_KEY AND
      CDW.COMMENTS.BRANCH_KEY = CDW.BRANCH.BRANCH_KEY AND
      CDW.COMMENTS.TIME_KEY = CDW.TIME.TIME_KEY AND
      CDW.COMMENTS.COLL_KEY = CDW.PL_COLLECTOR.COLL_KEY AND
      CDW.COMMENTS.DAY_KEY = CDW.DAY.DAY_KEY AND
      CDW.DAILY_ACCT_BAL.ACCT_KEY = CDW.ACCOUNT.ACCT_KEY AND
      CDW.DAILY_ACCT_BAL.PROC_SEG_KEY = CDW.PROC_SEGMENTATION.PROC_SEG_KEY AND
      CDW.DAILY_ACCT_BAL.DAY_KEY = CDW.DAY.DAY_KEY AND
      CDW.COMMENTS.DAY_KEY BETWEEN 20030115 AND 20030131 AND
      CDW.PROC_SEGMENTATION.PROC_SEG_LVL1_NM = 'Skip' AND
      CDW.DAY.DAY_DT BETWEEN CDW.PL_COLLECTOR.EFF_START_DT AND CDW.PL_COLLECTOR.EFF_END_DT
 

Please advise as this has became a very critical issue and need to be resolved asap.  

Thanks - Chetan

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chindarkar, Chetan (CAP, CARD Contractor)
  INET: Chetan.Chindarkar_at_gecapital.com

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).