[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