Oracle PL/SQL Tutorial/PL SQL Programming/DBMS ROWID
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
<source lang="sql">
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></source>