Getting Actual ROWID

[malru]

Hi,

I want to find the row for which a session is waiting,here is the query to find the rowid,

SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create (1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)
4 from v$session s, dba_objects do
5 where sid=10
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
-------------------- ------------- -------------- --------------- ------------- ------------------
EMP 34768 1 53714 0 AAAIfQAABAAANHSAAA

When i queried for that ROWID,it gave the following error,

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

I checked the rowid by using the below query and found that ROWID returned by procedure DBMS_ROWID differs by only one character thats why it's giving the error.

ROWID given by DBMS_ROWID = AAAIfQAABAAANHSAAA
Actual ROWID = AAAIfYAABAAANHSAAA

My oracle version is 9.2.0.8.

SQL> select rowid,id from scott.emp;

ROWID ID
------------------ ----------
AAAIfYAABAAANHSAAA 1
AAAIfYAABAAANHSAAB 2

How to solve this problem?What i've to do to get the actual rowid?

Thanks....

[Bore]

Try using dbms_rowid

[malru]

I used DBMS_ROWID only,it's giving wrong ROWID thats why i posted this thread.

[davey23uk]

maybe two people have an emp table

[Bore]

Maybe U use big file tablespace

[dbaxchangedba]

The portion of the rowid (the first 6 characters) that's causing the mismatch is the data_object_id portion of the rowid. The data_object_id is what's found within obj$.dataobj# or dba_objects.data_object_id which identifies the physical data segment currently holding data for the object_id. The obj$.dataobj# or dba_objects.data_object_id portion of a table will change whenever a truncate or alter table move tablespace occurs.

Looking at your condition :

Rowid returned by V$session : AAAIfQAABAAANHSAAA transalates to data_object_id: 34768

SQL> select dbms_rowid.ROWID_OBJECT('AAAIfQAABAAANHSAAA') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAIFQAABAAANHSAAA')
---------------------------------------------
34768

While the current rows within your table has a different data_object_id: 34776

SQL> select dbms_rowid.rowid_object('AAAIfYAABAAANHSAAA') from dual;

DBMS_ROWID.ROWID_OBJECT('AAAIFYAABAAANHSAAA')
---------------------------------------------
34776

Which tells me that the table might have been truncated (..or some type of reorg) multiple times between the time when the wait occured Vs. when you actually queried it.

Good luck......


http://www.dbaxchange.com