ORA-01410 – Invalid rowid

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s