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