Getting Actual ROWID
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Getting Actual ROWID

  1. #1
    Join Date
    Feb 2006
    Posts
    162

    Getting Actual ROWID

    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....

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Try using dbms_rowid

  3. #3
    Join Date
    Feb 2006
    Posts
    162
    I used DBMS_ROWID only,it's giving wrong ROWID thats why i posted this thread.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    maybe two people have an emp table

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Maybe U use big file tablespace

  6. #6
    Join Date
    Nov 2005
    Posts
    32
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width