[Update 2009-10
Found Bug 1646533 (V$SESSION SHOWING WRONG VALUES FOR ROW_WAIT_OBJ#, ROW_WAIT_FILE# ETC). 
But it was closed as not a bug. Last updated on 21-MAR-2001. Relevant for 8.1.6.3]


I'm reading a message thread on Google
http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/b9fe15a2f5c632c3
and decided to do some test. To my surprise, I find v$session.row_wait_obj#
gives completely irrelevant object_id and may change with time. I did this in
9i and 10g:

col sid new_value mysid
col objid new_value obj_id
drop table c;
drop table p;
select sid from v$mystat where rownum = 1;
create table p (a number primary key);
create table c (a number, foreign key (a) references p on delete cascade);
insert into p values (1);
insert into c values (1);
commit;
delete from p;
select * from v$lock where sid = &mysid;
select row_wait_obj# objid from v$session where sid = &mysid;
select * from dba_objects where object_id = &obj_id;

Run the query to get row_wait_obj# multiple times. I get values as low as 19,
an object owned by SYS such as RLS$. Sometimes it changes to a high number such
as 46374 (MDSYS.UNIQUE_GEORASTERS in my database)

The problem is reproduced in 9.2.0.7.0 and up to 10.2.0.2.0.

Yong

--------------------------------------------------------------------------------

Jonathan Lewis's message:

http://groups.google.com/group/comp.databases.oracle.server/msg/beff96edcc7c13ad

--------------------------------------------------------------------------------

One single session inserts into a table. Why is v$session.row_wait_obj# not -1?

In my 9.2.0.1.0 database, I "reproduced" the case where multiple simultaneous 
inserts cause v$session.row_wait_obj# to be non -1 and stay like that forever. 
Create a table in non-ASSM tablespace using all default storage parameters. 
Open 3 sqlplus sessions and in 2 windows run
begin
 for i in 1..100000 loop
  insert into tt values (i);
 end loop; end;
In the 3rd window, select sid, row_wait_obj# from v$session where row_wait_obj# 
!= -1. It shows two sessions with the obj# corresponding to my table. The two 
sessions finish successfully in about 15 seconds. But the two rows in the 
v$session query still show the object number. In fact, it stays there forever 
until the session disconnects. If you alter table storage (freelists 2), you 
need to run 3 simultaneous inserts to see non -1 row_wait_obj#.

Back to Lucy Wang's mystery, I believe it's due to a previous simultaneous 
insert. So my suggestion is ignore that row_wait_obj#. That column in v$session 
should be updated by Oracle kernel. Consider it as a bug.

Yong Huang

[Original message: http://www.itpub.net/284474,2.html]

--------------------------------------------------------------------------------

http://www.freelists.org/archives/oracle-l/04-2008/msg00106.html