OraFAQ Forum: SQL & PL/SQL » Error : Invalid ROWID

Home » SQL & PL/SQL » SQL & PL/SQL » Error : Invalid ROWID (9.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error : Invalid ROWID [message #336824]

Tue, 29 July 2008 02:39

user71408
Messages: 585
Registered: November 2007
Location: NE

Senior Member

Hi When I am executing the Statement I am getting the folowing error. Please let me know why am getting this error and please give me idea to resolve this.

Prog_1~20080729061847~process~~-1410~ORA-01410: invalid ROWID~INTERNAL STORED FUNCTION ERROR: function=COST_EXTRACT_SQL.UPDATE_COSTS, error=UPDATE in package COST_EXTRACT_SQL.PROCESS_COST_CHANGE     , cost_chg=12345

Prog_1~20080729062650~process~~-1410~ORA-01410: invalid ROWID~INTERNAL STORED FUNCTION ERROR: function=COST_EXTRACT_SQL.UPDATE_COSTS, error=UPDATE in package COST_EXTRACT_SQL.PROCESS_COST_CHANGE     , cost_chg=12345
prog_1~20080729080247~process~~-1410~ORA-01410: invalid ROWID~INTERNAL STORED FUNCTION ERROR: function=COST_EXTRACT_SQL.UPDATE_COSTS, error=UPDATE in package COST_EXTRACT_SQL.PROCESS_COST_CHANGE     , cost_chg=12345

Thank you

Report message to a moderator

Re: Error : Invalid ROWID [message #336827 is a reply to message #336824]

Tue, 29 July 2008 03:11

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You are using a rowid that is not valid or points to a row that no more exists.

Regards
Michel

[Updated on: Tue, 29 July 2008 03:11]

Report message to a moderator

Re: Error : Invalid ROWID [message #337077 is a reply to message #336824]

Tue, 29 July 2008 20:55

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

existence of the rowid does not matter. Using the rowid of a no longer existing row does not cause this error. Only using an invalid rowid will cause this error. Invalid means, the rowid value you are using does not conform to the structure of a rowid for your verson of the database. Consider the code snippets below:

First we try to delete the same row twice using its rowid. No error is generated.

Next we try to delete using a null rowid. Again no error.

Last we try to delete using an obviosly malformed rowid. This gives the error.

SQL> drop table a;

Table dropped.

SQL> 
SQL> create table a (a number);

Table created.

SQL> 
SQL> insert into a values (1);

1 row created.

SQL> 
SQL> declare
  2     vrowid rowid;
  3  begin
  4     select rowid into vrowid from a where a = 1;
  5     delete from a where a = 1;
  6     delete from a where rowid = vrowid;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> delete from a where rowid = null;

0 rows deleted.

SQL> delete from a where rowid = 'a';
delete from a where rowid = 'a'
                            *
ERROR at line 1:
ORA-01410: invalid ROWID

How you got a bad rowid is anybody's guess.

Quote:

Do you save rowidids in a table somewhere?
Did you do a recent upgrade on your database to a version with a new rowid format?
Are you trying to construct rowid values on the fly yourself?
Do you have some third party tool that tries to do same?
Or maybe you had a memory corruption either by bad code, or hardware fault.

next time don't post such a long line, it makes the posts hard to read and harder to respond to.

Also, if you are going to post an error, use the error number along with the text.

Good luck, Kevin

Report message to a moderator

Re: Error : Invalid ROWID [message #337106 is a reply to message #337077]

Tue, 29 July 2008 22:40

Frank
Messages: 7901
Registered: March 2000

Senior Member

If I remember correctly, this error is also generated if you select the rowid from table A and try to use it in the where-clause for table B.

Edit: Tested.

SQL> declare
  2    cursor c
  3    is
  4      select rowid as the_rowid
  5      from   emp
  6      ;
  7  begin
  8    for r in c
  9    loop
 10      update dept
 11      set    deptno = 10
 12      where  rowid = r.the_rowid;
 13    end loop;
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 10

Another possibility (although related):

SQL> declare
  2    cursor c
  3    is
  4      select *
  5      from   emp
  6      for update;
  7  begin
  8    for r in c
  9    loop
 10      update dept
 11      set    deptno = 10
 12      where  current of c;
 13    end loop;
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 10

These could explain why op gets it while updating.

[Updated on: Tue, 29 July 2008 22:52]

Report message to a moderator

Re: Error : Invalid ROWID [message #337135 is a reply to message #337077]

Wed, 30 July 2008 01:00

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Kevin Meade

existence of the rowid does not matter. Using the rowid of a no longer existing row does not cause this error.

Are you sure?

SQL> create table a (a number);

Table created.

SQL> insert into a values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> declare
  2     vrowid rowid;
  3     va     number;
  4  begin
  5     select rowid into vrowid from a where a = 1;
  6     execute immediate 'truncate table a';
  7     select a into va from a where rowid = vrowid;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 7

Regards
Michel

[Updated on: Wed, 30 July 2008 01:02]

Report message to a moderator

Re: Error : Invalid ROWID [message #337296 is a reply to message #336824]

Wed, 30 July 2008 08:20

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Good one Frank thank, forgot about that.

Also Michel, thanks for showing me my mistake. Interesting piece of code you got there. Maybe this is a commit issue. I'll revise my understanding.

Kevin

Report message to a moderator

Re: Error : Invalid ROWID [message #337298 is a reply to message #337296]

Wed, 30 July 2008 08:36

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Maybe this is a commit issue.

No the reason is that "truncate" changes data object id which is part of rowid and previous rowid no more matches current data object id.

SQL> create table a (a number);

Table created.

SQL> insert into a values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid from a;
ROWID
------------------
AAAMtPAAEAAAB63AAA

1 row selected.

SQL> truncate table a;

Table truncated.

SQL> insert into a values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid from a;
ROWID
------------------
AAAMtQAAEAAAB63AAA

1 row selected.

AAAMtPAAEAAAB63AAA
AAAMtQAAEAAAB63AAA
Same file, block and row number but data object id increased by 1 due to truncate.

Regards
Michel

Report message to a moderator

Re: Error : Invalid ROWID [message #337304 is a reply to message #336824]

Wed, 30 July 2008 08:54

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I don't know if I buy that explanation Michel.

I think it has something to do with space actually allocated to the table. A delete followed by a commit frees up space for reuse by the table. A truncate actually removes space allocated for use by the table and returns it to the tablespace right?

Maybe the issue we see in our test cases is not does the rowid point to a row which actually exists in the table, but rather, does the rowid map to the space owned by the table.

This would explain to me why the code below fails at line 9 rather than line 7.

It is the same delete statement in both cases (line 7 and line 9). The row has been removed from the table in both cases. The most obvious difference however is that a delete followed by a commit leaves attached to the table, the space that was used by the row so that the deleted row's rowid still points to a location owned by the table. But the truncate pushes the high water mark down to "zero" for the table and thus the rowid for the now removed row, points to a location that is in a sense not valid for the table because the extent the rowid lives in is not actually in use by the table.

What do you think?

Kevin

SQL> drop table a;

Table dropped.

SQL> 
SQL> 
SQL> create table a (a number);

Table created.

SQL> 
SQL> 
SQL> insert into a values (1);

1 row created.

SQL> 
SQL> 
SQL> 
SQL> declare
  2     vrowid rowid;
  3  begin
  4     select rowid into vrowid from a where a = 1;
  5     delete from a where rowid = 1;
  6  commit;
  7     delete from a where rowid = vrowid;
  8     execute immediate 'truncate table a';
  9     delete from a where rowid = vrowid;
 10  end;
 11  /
   delete from a where rowid = 1;
                             *
ERROR at line 5:
ORA-06550: line 5, column 30:
PL/SQL: ORA-00932: inconsistent datatypes: expected ROWID got NUMBER
ORA-06550: line 5, column 4:
PL/SQL: SQL Statement ignored


SQL>

Report message to a moderator

Re: Error : Invalid ROWID [message #337332 is a reply to message #337304]

Wed, 30 July 2008 10:17

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

A truncate actually removes space allocated for use by the table and returns it to the tablespace right?

It depends on option "keep storage" or "reuse storage" but in both case at least min_extents is kept. In my case all the stuff is in the first extent so truncate does not free anything.
More the example I posted shows I reuse the same place (same fileid, blockif and rownb).

You can reexecute (I currently don't have a database) the same example I posted in my previous post including an "insert" between line 6 and 7 and you'll get the same result. So with or without a row, you get the same message.

I don't understand your example, I think you posted the wrong one as it does not fail at line 7 or 9 but at line 5. I think you wanted to use "a = 1" instead of "rowid = 1" in line 5.

Regards
Michel

Report message to a moderator

Re: Error : Invalid ROWID [message #337337 is a reply to message #336824]

Wed, 30 July 2008 10:26

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I am a stooge. I posted the wrong piece of code. Here is the write posted stuff. As you can see, error is as line 9 not line 7.

SQL> drop table a;

Table dropped.

SQL> 
SQL> 
SQL> create table a (a number);

Table created.

SQL> 
SQL> 
SQL> insert into a values (1);

1 row created.

SQL> 
SQL> 
SQL> declare
  2     vrowid rowid;
  3  begin
  4     select rowid into vrowid from a where a = 1;
  5     delete from a where a = 1;
  6  commit;
  7     delete from a where rowid = vrowid;
  8     execute immediate 'truncate table a';
  9     delete from a where rowid = vrowid;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 9


SQL>

Report message to a moderator

Re: Error : Invalid ROWID [message #337348 is a reply to message #337337]

Wed, 30 July 2008 11:23

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes it is the expected behaviour, in the delete line 7 it is just like a=2, there is no rowid with the value you give but the rowid is valid, it matches with the actual object.
In line 9, data object id changed and value in vrowid no more matches with the one of the segment owning the block.

Regards
Michel

Report message to a moderator

Re: Error : Invalid ROWID [message #337351 is a reply to message #336824]

Wed, 30 July 2008 11:28

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

OK, but I guess my confusion is in your use of the term data object id.

What do you means by this?

Kevin

Report message to a moderator

Re: Error : Invalid ROWID [message #337355 is a reply to message #337351]

Wed, 30 July 2008 11:39

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This was introduced in 8.0 along with new rowid format to prevent from the following to happen:
select a rowid from a table
truncate the table freeing the storage
create a new table that will reuse the just freed space
insert rows into the new table
select the first table using the rowid, you get the data from the second table (if it has the same structure).

This could happen in version 7 and before.
So Oracle introduced data object id and included it in rowid. This is set to the same value than object id at creation time and then when something happens that may release blocks, it changes to the next sequence number.

Have a look at dba_objects view, you will see 2 columns object_id and data_object_id. See object_id as the number of the object, data_object_id as the number of the segment that implements the object. Object is fixed, segment can move or be reinstantiated.

Regards
Michel

Report message to a moderator

Re: Error : Invalid ROWID [message #337356 is a reply to message #336824]

Wed, 30 July 2008 11:43

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Way cool! I get it. Thanks Michel.

Report message to a moderator

Previous Topic:	Date conversion
Next Topic:	describe tablename using sort

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 04 17:51:55 CDT 2026