DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) : DBMS_ROWID « PL SQL Programming « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Programming
3. DBMS_ROWID

SQL> set echo on
SQL>
SQL>
SQL>
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) values ( 1);

1 row created.

SQL> insert into t (a) values ( 2);

1 row created.

SQL> insert into t (a) values ( 3);

1 row created.

SQL> delete from t where a = 2 ;

1 row deleted.

SQL> insert into t (a) values ( 4);

1 row created.

SQL> select a from t;


         A
----------
         1
         4
         3

3 rows selected.

SQL>
SQL> -- example showing the above sort of effect without a delete
SQL>
SQL>
SQL> insert into t(a) select rownum from all_users;

17 rows created.

SQL> commit;

Commit complete.

SQL> update t set b = null, c = null;

20 rows updated.

SQL> set serveroutput on
SQL> commit;

Commit complete.

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

17 rows created.

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


DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          A
------------------------------------ ----------
                               42610          1
                               42611          4
                               42612          3
                               42613          1
                               42613       1017
                               42614          2
                               42614       1016
                               42615          3
                               42615       1015
                               42616          4
                               42616       1014
                               43785          5
                               43785       1013
                               43786          6
                               43786       1012
                               43787          7
                               43787       1011
                               43788          8
                               43788       1010
                               43789          9
                               43789       1009
                               43790         10
                               43790       1008
                               43791         11
                               43791       1007
                               43792         12
                               43792       1006
                               43793         13
                               43793       1005
                               43794         14
                               43794       1004
                               43795         15
                               43795       1003
                               43796         16
                               43796       1002
                               43797         17
                               43797       1001

37 rows selected.

SQL> drop table t;

Table dropped.

SQL>