Oracle中資料塊中row number缺失(記錄，未解決)

PiscesCanon發表於2017-07-10
Oracle
環境1：
  1. SCOTT@proc> drop table tt purge;

  3. Table dropped.

  5. SCOTT@proc>
  6. SCOTT@proc>
  7. SCOTT@proc> create table tt (id int,name varchar2(2000)) tablespace users;

  9. Table created.

  11. SCOTT@proc> insert into tt values(1,rpad('a',1800,'+'));

  13. 1 row created.

  15. SCOTT@proc> insert into tt values(2,rpad('b',1800,'+'));

  17. 1 row created.

  19. SCOTT@proc> insert into tt values(3,rpad('c',1800,'+'));

  21. 1 row created.

  23. SCOTT@proc> insert into tt values(4,rpad('d',1800,'+'));

  25. 1 row created.

  27. SCOTT@proc> commit;

  29. Commit complete.

  31. SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;

  33.         ID SU ROWID                   FILE#     BLOCK#       ROW#
  34. ---------- -- ------------------ ---------- ---------- ----------
  35.          1 a  AAAV8LAAEAAAAImAAA          4        550          0
  36.          2 b  AAAV8LAAEAAAAImAAB          4        550          1
  37.          3 c  AAAV8LAAEAAAAImAAC          4        550          2
  38.          4 d  AAAV8LAAEAAAAImAAD          4        550          3

  40. SCOTT@proc> delete from tt where id=2;

  42. 1 row deleted.

  44. SCOTT@proc> commit;

  46. Commit complete.

  48. SCOTT@proc> insert into tt values(5,rpad('e',1800,'+'));

  50. 1 row created.

  52. SCOTT@proc> commit;

  54. Commit complete.

  56. SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;

  58.         ID SU ROWID                   FILE#     BLOCK#       ROW#
  59. ---------- -- ------------------ ---------- ---------- ----------
  60.          1 a  AAAV8LAAEAAAAImAAA          4        550          0
  61.          5 e  AAAV8LAAEAAAAImAAB          4        550          1
  62.          3 c  AAAV8LAAEAAAAImAAC          4        550          2
  63.          4 d  AAAV8LAAEAAAAImAAD          4        550          3

環境2：
  1. SCOTT@proc> drop table tt purge;

  3. Table dropped.

  5. SCOTT@proc> create table tt (id int,name varchar2(2000)) tablespace users;

  7. Table created.

  9. SCOTT@proc> insert into tt values(1,rpad('a',1800,'+'));

  11. 1 row created.

  13. SCOTT@proc> insert into tt values(2,rpad('b',1800,'+'));

  15. 1 row created.

  17. SCOTT@proc> insert into tt values(3,rpad('c',1800,'+'));

  19. 1 row created.

  21. SCOTT@proc> commit;

  23. Commit complete.

  25. SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;

  27.         ID SU ROWID                   FILE#     BLOCK#       ROW#
  28. ---------- -- ------------------ ---------- ---------- ----------
  29.          1 a  AAAV8VAAEAAAAIkAAA          4        548          0
  30.          2 b  AAAV8VAAEAAAAIkAAB          4        548          1
  31.          3 c  AAAV8VAAEAAAAIkAAC          4        548          2

  33. SCOTT@proc> delete from tt where id=2;

  35. 1 row deleted.

  37. SCOTT@proc> insert into tt values(4,rpad('d',1800,'+'));

  39. 1 row created.

  41. SCOTT@proc> commit;

  43. Commit complete.

  45. SCOTT@proc> insert into tt values(5,rpad('e',1800,'+'));

  47. 1 row created.

  49. SCOTT@proc> commit;

  51. Commit complete.

  53. SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;

  55.         ID SU ROWID                   FILE#     BLOCK#       ROW#
  56. ---------- -- ------------------ ---------- ---------- ----------
  57.          1 a  AAAV8VAAEAAAAIkAAA          4        548          0
  58.          3 c  AAAV8VAAEAAAAIkAAC          4        548          2
  59.          4 d  AAAV8VAAEAAAAIkAAD          4        548          3
  60.          5 e  AAAV8VAAEAAAAIkAAE          4        548          4

  62. SCOTT@proc>

環境3：
  1. SCOTT@proc> drop table tt purge;

  3. Table dropped.

  5. SCOTT@proc> create table tt (id int,name varchar2(2000)) tablespace users;

  7. Table created.

  9. SCOTT@proc> insert into tt values(1,rpad('a',1800,'+'));

  11. 1 row created.

  13. SCOTT@proc> insert into tt values(2,rpad('b',1800,'+'));

  15. 1 row created.

  17. SCOTT@proc> insert into tt values(3,rpad('c',1800,'+'));

  19. 1 row created.

  21. SCOTT@proc> commit;

  23. Commit complete.

  25. SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;

  27.         ID SU ROWID                   FILE#     BLOCK#       ROW#
  28. ---------- -- ------------------ ---------- ---------- ----------
  29.          1 a  AAAV8MAAEAAAAImAAA          4        550          0
  30.          2 b  AAAV8MAAEAAAAImAAB          4        550          1
  31.          3 c  AAAV8MAAEAAAAImAAC          4        550          2

  33. SCOTT@proc> delete from tt where id=2;

  35. 1 row deleted.

  37. SCOTT@proc> commit;

  39. Commit complete.

  41. SCOTT@proc> insert into tt values(4,rpad('d',1800,'+'));

  43. 1 row created.

  45. SCOTT@proc> insert into tt values(5,rpad('e',1800,'+'));

  47. 1 row created.

  49. SCOTT@proc> commit;

  51. Commit complete.

  53. SCOTT@proc> select id,substr(name,1,1),rowid,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from tt;

  55.         ID SU              ROWID      FILE#     BLOCK#       ROW#
  56. ---------- -- ------------------ ---------- ---------- ----------
  57.          1 a  AAAV8MAAEAAAAImAAA          4        550          0
  58.          5 e  AAAV8MAAEAAAAImAAB          4        550          1
  59.          3 c  AAAV8MAAEAAAAImAAC          4        550          2
  60.          4 d  AAAV8MAAEAAAAImAAD          4        550          3


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2141875/，如需轉載，請註明出處，否則將追究法律責任。

相關文章