One of the reason why invalid rowid can occur is if there is a truncate that occurs in between when an existing SQL is running.
Sample:
1) Sample table x with one column of datatype NUMBER.
SQL> select * FROM ajaffer.test;
X
———-
1000
1000
1000
1000
1000
1000
1000
7 rows selected.
2) Long running query which has a sleep for 60 seconds before it fetches the next row to demo SQL running for long period of time. In parallel Step (3) is running in another window.
declare
cursor l_cur IS SELECT * FROM ajaffer.test;
begin
for l_rec in l_cur
loop
dbms_output.put_line(l_rec.x);
sys.dbms_lock.sleep(60);
end loop;
end;
/
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 4
(3) In another window at the time the above PL/SQL is running, the table amin.x was truncated the table and added new rows.
SQL> truncate table ajaffer.test;
Table truncated.
SQL> insert into ajaffer.test values ( 10000 );
1 row created.
SQL> insert into ajaffer.test values ( 10000 );
1 row created.
SQL> commit;
Commit complete.