Thursday, January 5, 2012

Understanding ORA-1410 Error

In this post I will try to make you understand what is ROWID, the nature of the ORA-1410 error, the nature of a rowid, how you can get the error, and possible ways to investigate the error.

In the simplest case, ORA-1410 means simply that a rowid is invalid.

Whats is a Rowid

A rowid is a structure that allows direct access to a row. The rowid contains information on the object number, the datafile it is located in, the block number, and the slot number within the block.

Oracle 8 and greater version of Oracle has a rowid in this format:
O=Data Object Number (length=6)
F=Relative File Number (length=3)
B=Block Number (length=6)
S=Slot Number (length=3)

The dbms_rowid package (rowid_info procedure) can be used to decode the rowid into its components.
The following anonymous PL/SQL block will decode any rowid you provide
In this case we are providing rowid as AAAYQKAAEAAAAPzAAA
SQL> select rowid from t1 where c1=1;


SQL> set serveroutput on
SQL> declare
  2          my_rowid rowid := 'AAAYQKAAEAAAAPzAAA';   -- or any rowid
  3          rowid_type number;
  4          object_number number;
  5          relative_fno number;
  6          block_number number;
  7          row_number number;
  8          begin
        dbms_rowid.rowid_info(my_rowid, rowid_type, object_number, relative_fno, block_number, row_number);
  9   10          dbms_output.put_line('ROWID:   ' || my_rowid);
 11          dbms_output.put_line('Object#:      ' || object_number);
 12          dbms_output.put_line('RelFile#:     ' || relative_fno);
 13          dbms_output.put_line('Block#:       ' || block_number);
 14          dbms_output.put_line('Row#:         ' || row_number);
 15          end;
 16          /
Object#:      99338
RelFile#:     4
Block#:       1011
Row#:         0

PL/SQL procedure successfully completed.

In order to find the object name of this ID 99338 issue following statement:
SQL> col owner for a10
col object_name for a30
select owner,object_name,object_type,data_object_id
     from dba_objects
     where data_object_id = 99338;SQL> SQL>   2    3

OWNER      OBJECT_NAME                    OBJECT_TYPE         DATA_OBJECT_ID
---------- ------------------------------ ------------------- --------------
ARJU       T1                             TABLE PARTITION              99338

In order to find data file name (RelFile#: 4) issue:
SQL> select file_name
     from dba_data_files
     where file_id = 4;  2    3


Reason of ORA-1410
When Oracle parses the rowid (to get the file, block, and slot), if there is no row there, then Oracle throws an error ORA-1410.

- If the file and block are both valid, and the only problem is that the row slot does not exist, it returns the 'no rows selected' message.

- If any other part of the rowid has a problem, then the ORA-1410 is returned and may be cause for concern.

ORA-1410 is often linked with block corruption, because it can be one source of the error. However, there are other sources of the error.

This is a list of possible sources of ORA-1410 errors.
1. The rowid was manually entered incorrectly into a SQL statement. Or a customized PL/SQL procedure has faulty logic and generates an incorrect rowid.

2. The rowid was generated internally, but was corrupted while in memory.

3. The rowid was retrieved from an index, which was corrupt. If this is the case, you would expect to see other corruption errors accompanying the ORA-1410.

4. DDL on objects during long running queries which access those objects.. For example, rebuilding an index can cause the ORA-1410 if a SQL statement is accessing the index.

5. The rowid was valid, but the data block or datafile was corrupted (overwritten) so the block address may be wrong. If this is the case, you would expect to see other corruption errors accompanying the ORA-1410.

6. The rowid was valid but points to a block that was moved recently. This can occur due to timing, where a table was truncated while a SQL was in progress. Therefore, the SQL has a cached rowid, but the block was removed during the truncation. The same thing can happen if a table partition was exchanged while a SQL statement was running. In this case the file number changed and the SQL statement could report ORA-1410.

7. An Oracle Bug, OS bug, or other application bug.

Example of how ORA-1410 can occur
With a simple example, I will demonstrate of how ORA-1410 might occur.

- First create a simple table with one or two columns.
- Then insert a couple of rows and commit.
- Then display the rowids
-- Create a sample table. 
SQL> create table rowid_demo (col1 varchar(10), col2 varchar2(10));

Table created.

-- Insert rows into the sample tables. 
SQL> insert into rowid_demo values('value1','col2');

1 row created.

SQL> insert into rowid_demo values('value2','col2');

1 row created.

SQL> commit;

Commit complete.

-- Display the rowids 
SQL> select rowid from rowid_demo;


From the above example we see there is two rows in the rowid_demo table with the following rowids:


Let's now play with these ROW IDs
From these two rowids we see last 3 digits are (which is slot number of 3 length) AAA and AAB. Let's increment the last rowid slot by 1 (so . . . . AAB becomes . . . . . .AAC), and select from the table using this non-existent rowid. Since we changed only the last value only slot number was changed so no rows were selected.

SQL> select * from rowid_demo where rowid='AAAYQOAAEAAACmmAAC';

no rows selected

Even changing last 3 digits to FFF returns "no rows selected".

SQL> select * from rowid_demo where rowid='AAAYQOAAEAAACmmFFF';

no rows selected

Now instead of slot number let's change the block number (length 6) and see what happens. Existing block number is AAACmm and lets change to FFFFmm.

SQL> select * from rowid_demo where rowid='AAAYQOAAEFFFFmmAAB';
select * from rowid_demo where rowid='AAAYQOAAEFFFFmmAAB'
ERROR at line 1:
ORA-01410: invalid ROWID

Now we see ORA-01410: invalid ROWID.

So we clearly see how ORA-01410 is generated. We see what happens in a corruption of the rowid. In the above example part of the rowid was overwritten by FFFF and it was the block address that was overwritten.

The overwrite can be caused by a few events:

1) A piece of C code wrote to memory it thought it owned. If the C code is Oracle’s, it is an Oracle bug. If the code is from the OS, it is a vendor (OS) bug. If the code is from an application, then we need to get in touch with the supplier of the application code. For Oracle Bugs, you will need to engage Oracle Support.

2) A flaw in memory, where hardware is failing, and a memory address is bad. Hopefully, we will see messages in the OS error logs to verify this.

3) A rowid was generated incorrectly, either by Oracle code, or by customized application code.

A corruption can occur not only on the rowid, but also on the object that holds a copy of the rowid (for example, an index), or on the table where the rowid points. If an index was corrupted, then the rowids in the index can have incorrect components, and will cause an ORA-1410 if it is used to access the data rows. Similarly, if the data component (table) was corrupted, then the block address may be overwritten, and a valid rowid from the index may not be able to find the block. The ORA-1410 will again occur.

The next step in the example is to demonstrate how changing the object can cause ORA-1410 even with a valid rowid.

In this example, we will truncate the table and see the effect on the rowid.
-- Show the two rowids in the table
SQL>  select rowid from rowid_demo;


-- Select all columns from the table using one of the  rowids.

SQL> select * from rowid_demo where rowid='AAAYQOAAEAAACmmAAA';

COL1       COL2
---------- ----------
value1     col2
-- Now truncate the table

SQL> truncate table rowid_demo;

Table truncated.

-- Rerun the previous select statement using the known valid rowid.

SQL> select * from rowid_demo where rowid='AAAYQOAAEAAACmmAAA';
select * from rowid_demo where rowid='AAAYQOAAEAAACmmAAA'
ERROR at line 1:
ORA-01410: invalid ROWID

In the above example, we see a common cause of the ORA-1410. A SQL statement has some rowid’s cached in memory, for selecting from a table. But the table is truncated in the interim, and all blocks in the table cease to exist. So on executing the select with the cached rowid, we see the ORA-1410.

The same thing happens if a table partition is exchanged, becoming a stand-alone table. In this case the file number has changed, and any SQL that was running and had rowid’s cached in memory, can get the ORA-1410.

No comments: