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

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

环境1：

1. SCOTT@proc> drop table tt purge;
   

2. 
   

3. Table dropped.
   

4. 
   

5. SCOTT@proc>
   

6. SCOTT@proc>
   

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

8. 
   

9. Table created.
   

10. 
    

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

12. 
    

13. 1 row created.
    

14. 
    

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

16. 
    

17. 1 row created.
    

18. 
    

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

20. 
    

21. 1 row created.
    

22. 
    

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

24. 
    

25. 1 row created.
    

26. 
    

27. SCOTT@proc> commit;
    

28. 
    

29. Commit complete.
    

30. 
    

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;
    

32. 
    

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
    

39. 
    

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

41. 
    

42. 1 row deleted.
    

43. 
    

44. SCOTT@proc> commit;
    

45. 
    

46. Commit complete.
    

47. 
    

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

49. 
    

50. 1 row created.
    

51. 
    

52. SCOTT@proc> commit;
    

53. 
    

54. Commit complete.
    

55. 
    

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;
    

57. 
    

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;
   

2. 
   

3. Table dropped.
   

4. 
   

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

6. 
   

7. Table created.
   

8. 
   

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

10. 
    

11. 1 row created.
    

12. 
    

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

14. 
    

15. 1 row created.
    

16. 
    

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

18. 
    

19. 1 row created.
    

20. 
    

21. SCOTT@proc> commit;
    

22. 
    

23. Commit complete.
    

24. 
    

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;
    

26. 
    

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
    

32. 
    

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

34. 
    

35. 1 row deleted.
    

36. 
    

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

38. 
    

39. 1 row created.
    

40. 
    

41. SCOTT@proc> commit;
    

42. 
    

43. Commit complete.
    

44. 
    

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

46. 
    

47. 1 row created.
    

48. 
    

49. SCOTT@proc> commit;
    

50. 
    

51. Commit complete.
    

52. 
    

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;
    

54. 
    

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
    

61. 
    

62. SCOTT@proc>

环境3：

1. SCOTT@proc> drop table tt purge;
   

2. 
   

3. Table dropped.
   

4. 
   

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

6. 
   

7. Table created.
   

8. 
   

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

10. 
    

11. 1 row created.
    

12. 
    

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

14. 
    

15. 1 row created.
    

16. 
    

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

18. 
    

19. 1 row created.
    

20. 
    

21. SCOTT@proc> commit;
    

22. 
    

23. Commit complete.
    

24. 
    

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;
    

26. 
    

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
    

32. 
    

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

34. 
    

35. 1 row deleted.
    

36. 
    

37. SCOTT@proc> commit;
    

38. 
    

39. Commit complete.
    

40. 
    

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

42. 
    

43. 1 row created.
    

44. 
    

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

46. 
    

47. 1 row created.
    

48. 
    

49. SCOTT@proc> commit;
    

50. 
    

51. Commit complete.
    

52. 
    

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;
    

54. 
    

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

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