Re: ORA-01410: invalid ROWID

wiggam14 <member22780_at_dbforums.com> wrote in message news:<2422020.1043239859_at_dbforums.com>...
> Thanks for responding. We (the DBAs) have checked, all object are
> valid. Alert Logs have produced nothing. We've run DBVERIFY, it showed
> us no errors. No DB Links. No privileges issues. I'm storing the
> ROWID as a ROWID type. We're running oracle 8.1.7.4 on 2000. Like I
> said, it's truly weird. This trigger scheme has been working well for
> six months and then just started throwing these ORA-01410 errors.
>
> Here is a sample...
>
>
> The package to hold the ROWID....
>
>
> CREATE OR REPLACE PACKAGE PAK_MUTATING_STATE
> AS
>
> TYPE ridArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
>
> newRIDs ridArray;
> emptyRID ridArray;
>
> END PAK_MUTATING_STATE;
> /
>
> First, a before statement trigger clears out the package var....
>
> CREATE OR REPLACE TRIGGER TR_BEFORE_STMT
> BEFORE INSERT OR UPDATE
> ON TABLE
> BEGIN
> PAK_MUTATING_STATE.newRIDs := PAK_MUTATING_STATE.emptyRID;
> END;
>
> Each Row level trigger grabs the ROWID of the inserted / update row...
>
> CREATE OR REPLACE TRIGGER TR_AUDIT_ROW_LEVEL
> AFTER INSERT OR UPDATE
> ON TABLE
> FOR EACH ROW
> BEGIN
> PAK_MUTATING_STATE.newRIDs(PAK_MUTATING_STATE.newRIDs.count + 1)
> := :NEW.rowid;
> END;
>
> Lastly, an After statement trigger populates populates the audit table
> with every change ....
>
> CREATE OR REPLACE TRIGGER TR_AFTER_STMT
> AFTER INSERT OR UPDATE
> ON TABLE
> BEGIN
> for inx in PAK_MUTATING_STATE.newRIDs.FIRST ..
> PAK_MUTATING_STATE.newRIDs.LAST
> LOOP
> BEGIN
> INSERT INTO TABLE_AUDIT
> select * from TABLE
> where ROWID = PAK_MUTATING_STATE.newRIDs(inx);
> END;
> END LOOP;
> END;
>
>
> Also, I should note that these tables are not IOT table nor are they
> partitioned. (A suggestion from someone else).

How is your PL compatability set? Has it changed?

Perhaps this is related to bug 1640859, or the mysterious non-public bug it refers to. Try playing with the syntax of the ridarray typing.

How high is the count (particularly when the problem started)?

How about other stuff, like fet$ and so forth (speculation here being an increase in amount of data has slowed down extent management so the rowid sorta doesn't exit yet when the after trigger fires, which could be a concurrency bug).

Review Tom Kyte's book about mutant situations and ask him.

jg

--
_at_home is bogus.
There's a hole in the software, dear Henry, dear Henry.