Oracle-L: Re: ORA-1410 Silliness

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Tue, 31 Dec 2002 12:54:14 -0800
Message-ID: <F001.00524F3C.20021231125414@fatcity.com>

Lisa

. Enable SQL tracing
. Launch your code
. Identify (exactly) the cursor which fails

with ORA-01410 and what bind vars are.

. Pull out the statement from your code
. Run it in 'standalone' mode
. If it fails identify rowids which look broken.

Check the phys. entities those rowids point out. Are those phys. entities Ok?
. Any access BY ROWID in your statement is a potential

problem.
. You might want to dump error stack too but I suggest

to contact oracle support first.

Try to make a test case as simple as possible, it definitely would help.

There is a simple scenario, may be it can give you some ideas (index_s is a simplified index simulator):

DROP TABLE index_s;
DROP TABLE tbl;
CREATE TABLE index_s (

   rid                              ROWID

);
CREATE TABLE tbl (

   p                                NUMBER

);
INSERT INTO tbl VALUES(1);
INSERT INTO index_s SELECT ROWID FROM tbl; SELECT *
FROM tbl
WHERE rowid = (

          SELECT rid
            FROM index_s
        );

DROP TABLE tbl;
CREATE TABLE tbl (

   p                                NUMBER

);
INSERT INTO tbl VALUES(1);
COMMIT;
SELECT *
FROM tbl
WHERE rowid = (

          SELECT rid
            FROM index_s
        );

However this works well: SELECT * FROM tbl , index_s WHERE tbl.rowid = index_s.rid / -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation.

Koivu, Lisa wrote:
> Hello all,
>
> 8.1.7, Windows 2000 SP2
>
> Here's the error:
> *
> ERROR at line 1:
> ORA-01001: invalid cursor
> ORA-06512: at "ELVIS.CLEANUP_VEGAS_PK", line 1970
> ORA-01410: invalid ROWID
> ORA-06512: at line 1
>
> Has anyone seen this error before? I run into this error periodically
> during data loads. I have done the following to search for the root of
> the problem:
>
> 1. No code references ROWID. Deletes are never applied to this table.
> 2. No inline views in any of the code.
> 3. Ran dbv on all datafiles while database was down. No problems
> identified.
> 4. Ran analyze table <table name> validate structure cascade on all
> recent partitions. No rows found in INVALID_ROWS table.
>
> 5. Was able to export the entire table without any problem.
>
> I can't easily drop the indexes and recreate them. This is a very large
> table - ~25GB, 38 million rows. I also can't easily
> export/drop/recreate/import.
>
> Usually when this happens I can re-fire the load and it will complete,
> no problem. It's a big annoyance and it seems like every time I take a
> day off it happens.
>
> Any ideas, suggestions, or thoughts are appreciated. Thanks everyone.
>
> Lisa Koivu
> Oracle Dorkbase Administrator
> Fairfield Resorts, Inc.
> 5259 Coconut Creek Parkway
> Ft. Lauderdale, FL, USA 33063

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.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).

Received on Tue Dec 31 2002 - 14:54:14 CST