Use dbms_rowid.rowid_block_number : dbms_rowid « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. dbms_rowid

SQL>
SQL> set echo on
SQL> set serveroutput on
SQL>
SQL> create table t
  2  ( a int,
  3    b varchar2(4000) default rpad('*',4000,'*'),
  4    c varchar2(3000) default rpad('*',3000,'*' )
  5  )
  6  /

Table created.

SQL>
SQL> insert into t(a) select rownum from all_users;

15 rows created.

SQL> insert into t(a) select rownum+1000 from all_users;

15 rows created.

SQL>
SQL> select dbms_rowid.rowid_block_number(rowid), a from t;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          A
------------------------------------ ----------
                               43410          1
                               43411          2
                               43412          3
                               43413          4
                               43414          5
                               43415          6
                               43416          7
                               43417          8
                               43418          9
                               43419         10
                               43420         11

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          A
------------------------------------ ----------
                               43421         12
                               43422         13
                               43423         14
                               43424         15
                               43425       1001
                               43426       1002
                               43427       1003
                               43428       1004
                               43429       1005
                               43430       1006
                               43431       1007

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          A
------------------------------------ ----------
                               43432       1008
                               43433       1009
                               43434       1010
                               43435       1011
                               43436       1012
                               43437       1013
                               43438       1014
                               43439       1015

30 rows selected.

SQL>
SQL> drop table t;

Table dropped.

SQL>