Vladimir,
Thanks for your reply.
I have tested the cursor. It does not include any bind variables.
There are no "broken rowids", as all objects passed analyze ... validate structure cascade.
I also tested the scenario you describe in your code below. The code does break with that error, however there are no inline views in my code.
Lisa
-----Original Message-----
From: Vladimir Begun [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 31, 2002 3:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1410 Silliness
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
