Search My Oracle Blog

Custom Search

20 June, 2013

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

---------- ----------
        11        168

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

---------- --------------
    114154         114154

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;

---------- --------------- ------------------
         1 Row One         AAAb3qAALAAAACsAAA
         2 Row Two         AAAb3qAALAAAACsAAB

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  /

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

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  /


7 rows selected.

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

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.



Pavan Kumar said...

Hi Hemant,

From your demo, we can understand that when you query the "dba_extents" initially it pointed to block 168 (Segment Header) using the "dbms_rowid.rowid_create" it allows (or) oracle pre-calculates the rowid based on (file,object,extended_format,block) which was result and verified with tables actual rows.

- Thanks
Pavan Kumar N

Anonymous said...

Very interesting article, i do have one question. Why did you pick 8? It will always return a max of 7 rows, you can have more rows i guess.

Hemant K Chitale said...

I just chose 8. I could have chosen 11 or 42. The script was generating expected ROWIDs. There were actually only 2 rows present in the block.


Aggregated by

Aggregated by
This blog is being aggregated by

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016