Oracle中数据块中row number缺失(记录，未解决)
环境1：
环境2：
环境3：
-
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30174570/viewspace-2141875/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Oracle RAC环境添加新的network和listener(未完成)
- 【Oracle】Oracle wrong result一则（优化器问题）
- 【Oracle】arraysize的研究（存在疑问）
- 【Oracle】where条件执行顺序(上篇的问题延伸)
- 【Oracle】查询条件中出现to_number报错，用Hint之后改变运行结果
- 偶然的实验，查询两条记录的全表很久，10046真好用...
- Oracle不正规操作导致drop表空间drop用户报错ora38301(记录，未解决)
- Oracle脚本收集【不定时更新】
- Oracle的不一致性,未commit数据变成实际数据并被其他回话查询
- Oracle中flashback table功能为什么需要开启row movement功能
