学习oracle系统包－DBMS_ROWID

   包DBMS_ROWID用于再PL/SQL程序和SQL语句中取得行标识符的信息并建立rowid。

1.取得特定rowid所对应的数据库。

SQL> select dbms_rowid.rowid_block_number(rowid) from t;
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                               19943
                               19945
                               19947
                               19949
                               19951
                               19953
 
6 rows selected.
 
SQL> select count(*) from t;
 
  COUNT(*)
----------
         6

本例中的表t包含6行数据，每行占用了1个block。将表重组后，再观察每行的block号。

SQL> alter table t move;
 
Table altered.
 
SQL> select dbms_rowid.rowid_block_number(rowid) from t;
 
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                              112242
                              112244
                              112246
                              112248
                              112250
                              112252
 
6 rows selected.

可见表T所占用block已经发生了变化。

仔细查看该表每行所占用的block号，发现每行占用了2个block。

SQL> exec dbms_stats.gather_table_stats(user,'T');
 
PL/SQL procedure successfully completed.
 
SQL> select  NUM_ROWS,BLOCKS from user_tables where table_name='T';
 
  NUM_ROWS     BLOCKS
----------            ----------
         6                 12

可见，每行占用了2个block