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

2025-03-10 技术教程

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

环境1：

SCOTT@proc> drop table tt purge;

Table dropped.

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 SUROWID 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

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