ORA-1410: invalid ROWID when updating table

 

1. ORA-01410: invalid ROWID [ID 734513.1]  

  Goal
      Using Informatica PowerCenter Version 7.1.2 some times when runninh the ETL full the process SIL_CGEO_OrderFact.log failed sending the next error

      ORA-01410: invalid ROWID

      If we restarted this process works fine.

      Are there any known issues in regards to this error message?

  Solution
       1) The following error can be found in the Informatica log file: -

          "ORA-01410: invalid ROWID"

  Cause:
   A ROWID was entered incorrectly. ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F.
   A typical ROWID format is '000001F8.0001.0006'.

  Action:
   Check the format, then enter the ROWID using the correct format. ROWID format: block ID, row in block, file ID."

   2). Also on Metalink (note, not Metalink3):

       From Metalink:

       Subject: ORA-1410: invalid ROWID when updating table
       Doc ID: Note:429692.1

   Cause
   The issue can occur when a index block delete is not completed.

   ROWID's are found in the index block leaf.

   Solution

    ~. Run the explain plan on the table with the update statement to fnd the offending index.
    ~. Run the following to find the index statement:
    set long 100000
    select dbms_metadata.get_ddl('INDEX','','') from dual;
    ~. Drop the index

    ~. Recreate the index.

    The update should run without error

    NOTE: The index must be dropped and recreated. An online rebuild will not fix the bad ROWID

    3). In essence, It appears that an Index re-build could well resolve this issue.

    4). If not then, please take this issue up with Oracle Database Technical Support as this does not seem to be an issue with Informatica or with Oracle Business Intelligence.

    This issue appears to be specific to the Database's internal execution of its Index creation.

 

 

2.ORA-1410: invalid ROWID when updating table [ID 429692.1]  

--------------------------------------------------------------------------------
 
  修改时间 28-JUL-2010     类型 PROBLEM     状态 PUBLISHED  

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References
--------------------------------------------------------------------------------
 
Symptoms
The following error can occur when trying to update a table.

Example:
update acd_person set CONTACT_MAIL_FLAG='Y' where PRS_CD_ID='292177803'

ERROR at line 1:
ORA-01410: invalid ROWID

A select will work fine on the table.

 

The instance does not crash
The error is reproducible

 

Changes
No changes to the environment

 

Cause
The issue can occur when a index block delete is not completed.

ROWID's are found in the index block leaf.

 

Solution
1.  Run the explain plan on the table with the update statement to fnd the offending index.

(Note 199081.1)

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Id  | Operation            |  Name          | Rows  | Bytes |  Cost  |
-----------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |                |     1 |     9 |     3 |
|   1 |  UPDATE              | ACD_PERSON     |       |       |       |
|*  2 |   INDEX  UNIQUE SCAN  | PK_ACD_PERSON  |     1 |     9 |     2 |
-----------------------------------------------------------------------
2.  Run the following to find the index statement.

set long 100000
select dbms_metadata.get_ddl('INDEX','','') from dual;

3.  Drop the index

4.  Recreate the index.

The update should run without error

NOTE:  The index must be dropped and recreated.  An online rebuild will not fix the bad ROWID.

  

参考文献：

1. ORA-1410: invalid ROWID when updating table [ID 429692.1]

2. ORA-01410: invalid ROWID [ID 734513.1]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9252210/viewspace-676185/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9252210/viewspace-676185/