Oracle中数据块中row number缺失该怎么办

Oracle中数据块中row number缺失该怎么办，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

环境1：

SCOTT@proc> drop table tt purge;

Table dropped.

SCOTT@proc>

SCOTT@proc>

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

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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;

ID SU ROWID                   FILE#     BLOCK#       ROW#

---------- -- ------------------ ---------- ---------- ----------

1 a   AAAV8LAAEAAAAImAAA           4          550          0

         2 b   AAAV8LAAEAAAAImAAB           4          550          1

3 c   AAAV8LAAEAAAAImAAC           4          550          2

4 d   AAAV8LAAEAAAAImAAD           4          550          3

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

1 row deleted.

SCOTT@proc> commit;

Commit complete.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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;

ID SU ROWID                   FILE#     BLOCK#       ROW#

---------- -- ------------------ ---------- ---------- ----------

         1 a  AAAV8LAAEAAAAImAAA           4          550          0

5 e   AAAV8LAAEAAAAImAAB           4        550          1

         3 c   AAAV8LAAEAAAAImAAC           4          550          2

4 d   AAAV8LAAEAAAAImAAD          4          550          3

环境2：

SCOTT@proc> drop table tt purge;

Table dropped.

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

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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;

ID SU ROWID                   FILE#     BLOCK#       ROW#

---------- -- ------------------ ---------- ---------- ----------

         1 a   AAAV8VAAEAAAAIkAAA           4          548          0

2 b   AAAV8VAAEAAAAIkAAB          4          548          1

         3 c  AAAV8VAAEAAAAIkAAC          4          548          2

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

1 row deleted.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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;

        ID SU ROWID                   FILE#     BLOCK#       ROW#

---------- -- ------------------ ---------- ---------- ----------

1 a   AAAV8VAAEAAAAIkAAA          4          548          0

         3 c  AAAV8VAAEAAAAIkAAC           4         548          2

4 d  AAAV8VAAEAAAAIkAAD           4          548          3

         5 e  AAAV8VAAEAAAAIkAAE          4         548          4

SCOTT@proc>

环境3：

SCOTT@proc> drop table tt purge;

Table dropped.

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

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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;

ID SU ROWID                   FILE#     BLOCK#       ROW#

---------- -- ------------------ ---------- ---------- ----------

1 a  AAAV8MAAEAAAAImAAA          4          550          0

         2 b  AAAV8MAAEAAAAImAAB          4         550          1

3 c   AAAV8MAAEAAAAImAAC          4          550          2

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

1 row deleted.

SCOTT@proc> commit;

Commit complete.

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

1 row created.

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

1 row created.

SCOTT@proc> commit;

Commit complete.

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;

ID SU              ROWID      FILE#     BLOCK#       ROW#

---------- -- ------------------ ---------- ---------- ----------

1 a   AAAV8MAAEAAAAImAAA           4          550          0

5 e   AAAV8MAAEAAAAImAAB           4          550          1

         3 c   AAAV8MAAEAAAAImAAC           4          550          2

4 d   AAAV8MAAEAAAAImAAD           4          550          3

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。