Getting the ROWIDs present in a Block

Here is a demonstration of getting the ROWIDs present in a block.

Assume that you are interested in Block 172 in File 11.  Maybe this block is being reported in V$SESSION  (ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) and you know it is object 114154.  OR Assume that this block is being reported in a trace file and you want to know the contents of the block.

First I start with a new Table and identify its OBJECT_ID and Blocks potentially holding the rows.  (In your case you might have already identified the Block(s).

SQL> create table new_table_block_rowid (id_col number, data_col varchar2(15));

Table created.

SQL> insert into new_table_block_rowid values (1, 'Row One');

1 row created.

SQL> insert into new_table_block_rowid values (2, 'Row Two');

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> select file_id, block_id
  2  from dba_extents -- currently the table is a single extent
  3  where segment_name = 'NEW_TABLE_BLOCK_ROWID'
  4  and owner = 'HEMANT'
  5  /

   FILE_ID   BLOCK_ID
---------- ----------
        11        168

SQL> 
SQL> select object_id, data_object_id
  2  from user_objects
  3  where object_name = 'NEW_TABLE_BLOCK_ROWID'
  4  /

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    114154         114154

SQL> 

For subsequent verification, I get the ROWIDs.  (If your query is "which ROWIDs exist in Block 'N', you wouldn't know these ROWIDs in advance but I print them here for verification to confirm that I subsequently identfy the correct ROWIDs.)

SQL> select id_col, data_col, rowid
  2  from new_table_block_rowid
  3  order by 1;

    ID_COL DATA_COL        ROWID
---------- --------------- ------------------
         1 Row One         AAAb3qAALAAAACsAAA
         2 Row Two         AAAb3qAALAAAACsAAB

SQL> 

Later, I will confirm that I have the right ROWIDs.

I now use the DBMS_ROWID.CREATE_ROWID function to identify all potential ROWIDs.  Remember that the first row is not necessarily in Block 168 of File 11.

SQL> select dbms_rowid.rowid_create(1,114154,11,target_block,1), target_block
  2  from
  3  (select rownum+168 target_block from dual connect by level < 12)
  4  /

DBMS_ROWID.ROWID_C TARGET_BLOCK
------------------ ------------
AAAb3qAALAAAACpAAB          169
AAAb3qAALAAAACqAAB          170
AAAb3qAALAAAACrAAB          171
AAAb3qAALAAAACsAAB          172
AAAb3qAALAAAACtAAB          173
AAAb3qAALAAAACuAAB          174
AAAb3qAALAAAACvAAB          175
AAAb3qAALAAAACwAAB          176
AAAb3qAALAAAACxAAB          177
AAAb3qAALAAAACyAAB          178
AAAb3qAALAAAACzAAB          179

11 rows selected.

SQL> 

What I have identified is a set of (potential) ROWIDs for (potential) Rows in Blocks 169 to 179.

 Let's assume that I have identified my candidate block as Block 172 and need to identify the ROWIDs in Block 172.

Here's how I identify the ROWIDs in Block 172 :

SQL> select ret_rowid
  2  from
  3  (
  4  select dbms_rowid.rowid_create(1,114154,11,target_block,target_row) ret_rowid
  5  from
  6  (select 172 target_block from dual) a,
  7  (select rownum-1 target_row from dual connect by level < 8) b
  8  )
  9  /

RET_ROWID
------------------
AAAb3qAALAAAACsAAA
AAAb3qAALAAAACsAAB
AAAb3qAALAAAACsAAC
AAAb3qAALAAAACsAAD
AAAb3qAALAAAACsAAE
AAAb3qAALAAAACsAAF
AAAb3qAALAAAACsAAG

7 rows selected.

SQL> 

That gives me 7 (potential) ROWIDs for (potential) Rows in Bock 172 of File 11 owned by Object 114154.

Let's verify which of these ROWIDs contain the rows I want :

SQL> l
  1  select rowid, id_col, data_col from new_table_block_rowid
  2  where
  3  rowid in
  4  (
  5  select ret_rowid
  6  from
  7  (
  8  select dbms_rowid.rowid_create(1,114154,11,target_block,target_row) ret_rowid
  9  from
 10  (select 172 target_block from dual) a,
 11  (select rownum-1 target_row from dual connect by level < 8) b
 12  )
 13  )
 14* order by id_col
SQL> /

ROWID                  ID_COL DATA_COL
------------------ ---------- ---------------
AAAb3qAALAAAACsAAA          1 Row One
AAAb3qAALAAAACsAAB          2 Row Two

SQL> 

Voila !  I have identified the two rows (with their ROWIDs) that are prsent in File 11, Block 172, Object 114154 and have confirmed that only 2 and not 7 or more rows actually exist !


Note : Thanks to Jonathan Lewis for pointing out that the Row Directory actually starts with Zero (0).  Tha is why my query for "target_row" has rownum-1 from dual.

.
.
.