Mega Code Archive
DBMS_ROWID ROWID_BLOCK_NUMBER(ROWID)
<![CDATA[
SQL> set echo 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) 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> 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>
]]>