oracle rowid分析

Question

通过查看rowid可以得知同一数据块中有存储了哪些记录
但如何通过rowid来区分同一条记录存储在多个数据块中呢，要用什么方法识别呢？
请高手指点！

Answer 1

SELECT ROWID,
       dbms_rowid.rowid_object(rowid) object_no,
       dbms_rowid.rowid_relative_fno(rowid) file_no, 
       dbms_rowid.rowid_block_number(rowid) block_no, 
       dbms_rowid.rowid_row_number(rowid) row_no
  FROM yourTable ;

Answer 2

rowid是代表的一条记录，
以上SQL也法得出一条记录如何分散到好几个数据块中呀？

Answer 3

oracledbalgtu：可以MSN请教吗？

Answer 4

首先,如果一行数据真的跨一个以上的块了, 这个在ORACLE中也就称为Row Chaining (行链接)SQL> create table aa
  2  ( a char(2000),
  3    b char(2000),
  4    c char(2000),
  5    d char(2000),
  6    e char(2000),
  7    f char(2000),
  8    g char(2000),
  9    h char(2000),
 10    i char(2000),
 11   j  char(2000),
 12   k char(2000),
 13    l char(2000),
 14   m char(2000),
 15  n char(2000),
 16  o char(2000),
 17  p char(2000),
 18  q char(2000),
 19  r char(2000),
 20  s char(2000),
 21  t char(2000),
 22  u char(2000),
 23  v char(2000),
 24  w char(2000),
 25  x char(2000),
 26  y char(2000),
 27  z char(2000));Table created.SQL> insert into aa
  2  values(lpad('llll',2000,'bbb'),
  3  lpad('llll',2000,'bbb'),
  4  lpad('llll',2000,'bbb'),
  5  lpad('llll',2000,'bbb'),
  6  lpad('llll',2000,'bbb'),
  7  lpad('llll',2000,'bbb'),
  8  lpad('llll',2000,'bbb'),
  9  lpad('llll',2000,'bbb'),
 10  lpad('llll',2000,'bbb'),
 11  lpad('llll',2000,'bbb'),
 12  lpad('llll',2000,'bbb'),
 13  lpad('llll',2000,'bbb'),
 14  lpad('llll',2000,'bbb'),
 15  lpad('llll',2000,'bbb'),
 16  lpad('llll',2000,'bbb'),
 17  lpad('llll',2000,'bbb'),
 18  lpad('llll',2000,'bbb'),
 19  lpad('llll',2000,'bbb'),
 20  lpad('llll',2000,'bbb'),
 21  lpad('llll',2000,'bbb'),
 22  lpad('llll',2000,'bbb'),
 23  lpad('llll',2000,'bbb'),lpad('llll',2000,'bbb'),
 24  lpad('llll',2000,'bbb'),
 25  lpad('llll',2000,'bbb'),
 26  lpad('llll',2000,'bbb'),lpad('llll',2000,'bbb'));
insert into aa
            *
ERROR at line 1:
ORA-00913: too many values
SQL> insert into aa
  2      values(lpad('llll',2000,'bbb'),
  3      lpad('llll',2000,'bbb'),
  4      lpad('llll',2000,'bbb'),
  5      lpad('llll',2000,'bbb'),
  6      lpad('llll',2000,'bbb'),
  7      lpad('llll',2000,'bbb'),
  8      lpad('llll',2000,'bbb'),
  9      lpad('llll',2000,'bbb'),
 10     lpad('llll',2000,'bbb'),
 11     lpad('llll',2000,'bbb'),
 12     lpad('llll',2000,'bbb'),
 13     lpad('llll',2000,'bbb'),
 14     lpad('llll',2000,'bbb'),
 15     lpad('llll',2000,'bbb'),
 16     lpad('llll',2000,'bbb'),
 17     lpad('llll',2000,'bbb'),
 18     lpad('llll',2000,'bbb'),
 19     lpad('llll',2000,'bbb'),
 20     lpad('llll',2000,'bbb'),
 21     lpad('llll',2000,'bbb'),
 22     lpad('llll',2000,'bbb'),
 23     lpad('llll',2000,'bbb'),lpad('llll',2000,'bbb'),
 24     lpad('llll',2000,'bbb'),
 25    lpad('llll',2000,'bbb'),
 26    lpad('llll',2000,'bbb'));1 row created.SQL> commit;Commit complete.SQL> select * from user_extents;SEGMENT_NAME                                                                      PARTITION_NAME                 SEGMENT_TYPE         TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
--------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ----------
AA                                                                                                               TABLE                LY                                      0      65536         32
AA                                                                                                               TABLE                LY                                      1      65536         32SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/utlchain
create table CHAINED_ROWS (
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> analyze table ly.aa list chained rows;Table analyzed.SQL> select * from chained_rows;OWNER_NAME                     TABLE_NAME                     CLUSTER_NAME                   PARTITION_NAME    SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_T
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ---------
LY                             DD    N/A                            AAACmOAAEAAAAAtAAA 05-SEP-08
LY                             AA    N/A                            AAACmQAAEAAAAB9AAA 05-SEP-08SQL>