delete未提交rollback是否会改变行号

最新推荐文章于 2023-02-23 18:38:35 发布

丹心明月

最新推荐文章于 2023-02-23 18:38:35 发布

阅读量7.9k

点赞数

分类专栏： Oracle实验 Oracle数据库入门及进阶文章标签： delete未提交rollback是否会

本文链接：https://blog.csdn.net/ghostliming/article/details/51736392

版权

Oracle数据库入门及进阶同时被 2 个专栏收录

129 篇文章 47 订阅

订阅专栏

Oracle实验

82 篇文章 0 订阅

订阅专栏

今天做个试验，delete未提交rollback是否会改变行号？

创建测试表：
create table dxmy (id number,name varchar2(100));

插入数据
insert into dxmy values(1,'dxmy');
insert into dxmy values(2,'dxmy');
insert into dxmy values(3,'dxmy');
insert into dxmy values(4,'dxmy');
commit;

查看行号
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
  2         dbms_rowid.rowid_block_number(rowid) block_id,
  3         dbms_rowid.rowid_row_number(rowid) row_id,
  4         id
  5    from dxmy;
 
       FNO   BLOCK_ID     ROW_ID         ID
---------- ---------- ---------- ----------
         4        462          0          1
         4        462          1          2
         4        462          2          3
         4        462          3          4 


delete，但是不提交：
SQL> delete dxmy where id = 1;
 
1 row deleted
 
SQL> 
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
  2         dbms_rowid.rowid_block_number(rowid) block_id,
  3         dbms_rowid.rowid_row_number(rowid) row_id,
  4         id
  5    from dxmy;
 
       FNO   BLOCK_ID     ROW_ID         ID
---------- ---------- ---------- ----------
         4        462          1          2
         4        462          2          3
         4        462          3          4

rollback：
SQL> rollback;
 
Rollback complete

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
  2         dbms_rowid.rowid_block_number(rowid) block_id,
  3         dbms_rowid.rowid_row_number(rowid) row_id,
  4         id
  5    from dxmy;
 
       FNO   BLOCK_ID     ROW_ID         ID
---------- ---------- ---------- ----------
         4        462          0          1
         4        462          1          2
         4        462          2          3
         4        462          3          4

行号未改变。

那如果delete之后，我提交了，然后重新插入呢？
SQL> delete dxmy where id = 1;
 
1 row deleted
 
SQL> commit;
 
Commit complete
SQL> insert into dxmy values(1,'dxmy');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
  2         dbms_rowid.rowid_block_number(rowid) block_id,
  3         dbms_rowid.rowid_row_number(rowid) row_id,
  4         id
  5    from dxmy;
 
       FNO   BLOCK_ID     ROW_ID         ID
---------- ---------- ---------- ----------
         4        462          1          2
         4        462          2          3
         4        462          3          4
         4        463          0          1

<pre name="code" class="html">SQL> delete dxmy where id = 1;
 
1 row deleted
 
SQL> commit;
 
Commit complete

SQL> insert into dxmy values(1,'dxmy2');

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,
2         dbms_rowid.rowid_block_number(rowid) block_id,
3         dbms_rowid.rowid_row_number(rowid) row_id,
4         id
5    from dxmy;

       FNO   BLOCK_ID     ROW_ID         ID
---------- ---------- ---------- ----------
         4        462          1          2
         4        462          2          3
         4        462          3          4
         4        463          1          1

然后就发现：
1.delete未提交即rollback不会改变行号
2.delete提交后插入数据，可能会改变行号，但是也有可能不改变，但行的位置肯定会改变。