Internal Functions

This page discusses undocumented internal functions

SYS_OP_LBID

This function returns the BLOCKID for the index leaf block in which a specific leaf entry resides.

The function takes three parameters:

OBJECT_ID - the object ID of the index (not the data object ID)
Block type - always 'L' for leaf block
ROWID - the ROWID of the leaf row in the indexed table

For example, consider the following example:

CREATE INDEX country_pk ON country (country_key);

To obtain the OBJECT_ID for the COUNTRY_PK index:

SQL> SELECT object_id FROM dba_objects
  2  WHERE owner = 'GP'
  3  AND object_name = 'COUNTRY_PK'
  4  AND object_type = 'INDEX';

 OBJECT_ID
----------
     75826

To select the leaf block IDs use:

SQL> SELECT country_name,ROWID AS table_rowid,
  2  SYS_OP_LBID(75826,'L',ROWID) AS index_blockid
  3  FROM country;

COUNTRY_NAME         TABLE_ROWID        INDEX_BLOCKID
-------------------- ------------------ ------------------
Abu Dhabi            AAASgxAABAAAV/JAAj AAASgyAABAAAV/RAAA
Argentina            AAASgxAABAAAV/JAAC AAASgyAABAAAV/RAAA
Australia            AAASgxAABAAAV/JAAM AAASgyAABAAAV/RAAA
Bahrain              AAASgxAABAAAV/JAAc AAASgyAABAAAV/RAAA
Belgium              AAASgxAABAAAV/JAAD AAASgyAABAAAV/RAAA
Brazil               AAASgxAABAAAV/JAAB AAASgyAABAAAV/RAAA
Canada               AAASgxAABAAAV/JAAK AAASgyAABAAAV/RAAA
China                AAASgxAABAAAV/JAAd AAASgyAABAAAV/RAAA
Chile                AAASgxAABAAAV/JAAa AAASgyAABAAAV/RAAA
Columbia             AAASgxAABAAAV/JAAH AAASgyAABAAAV/RAAA
Czech Republic       AAASgxAABAAAV/JAAb AAASgyAABAAAV/RAAA
...

Use the DBMS_ROWID package to convert INDEX_BLOCKID to file number/block number