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

If you have any local partitioned indexes declared with one or more columns DESC you cannot run index rebuilds - the indexes will contain rows from the whole table, not only the single partition. In stead, exchange with a table, run the index rebuild and exchange back including indexes. This is bug 1840026/1987514. You may also try note 165510.1, which is a good starter note for partition relevant bugs.

/Bjørn .

Chindarkar, Chetan (CAP, CARD Contractor) wrote:

> 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

-- 
Bjørn Engsig, Miracle A/S
Member of Oak Table Network <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Bj=F8rn_Engsig?=
  INET: bjorn_at_miracleas.dk

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