flashback table肯定会造成rowid跟着修改-白红宇

flashback table肯定会造成rowid跟着修改

阅读量：6440 次

发布时间：2019-06-23

本文共 2523 字，大约阅读时间需要 8 分钟。

flashback table肯定会造成rowid跟着修改，为什么要开启行移动，就是这个原因

下面我马上做个实验来验证一下：

SQL> drop tablespace tp2 including contents and datafiles;

Tablespace dropped.

SQL> create tablespace tp2 datafile '/u01/app/oracle/oradata/tp2.dbf' size 512K;

Tablespace created.

SQL> create table t1 (id int,name char(10)) tablespace tp2;

Table created.

SQL> begin

2 for i in 1 .. 1000 loop

3 insert into t1 values(i,'gyj'||i);

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

查rowid

SQL> select rowid,id from t1 where id>=1 and id<=5;

ROWID ID

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

AAASvnAAIAAAAAOAAA 1

AAASvnAAIAAAAAOAAB 2

AAASvnAAIAAAAAOAAC 3

AAASvnAAIAAAAAOAAD 4

AAASvnAAIAAAAAOAAE 5

SQL> alter table t1 enable row movement;

Table altered.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

6177172

查文件号，块号，行号

SQL> select id,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from t1 where id>=1 and id<=5;

ID FILE# BLOCK# ROW#

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

1 8 14 0

2 8 14 1

3 8 14 2

4 8 14 3

5 8 14 4

SQL> delete from t1;

1000 rows deleted.

SQL> commit;

Commit complete.

插入大量记录，让空间用完为止

SQL> begin

2 for i in 1001 .. 100000 loop

3 insert into t1 values(i,'gyj'||i);

4 commit;

5 end loop;

6 end;

7 /

begin

ERROR at line 1:

ORA-01653: unable to extend table GYJ.T1 by 8 in tablespace TP2

ORA-06512: at line 3

SQL> flashback table t1 to scn 6177172;

Flashback complete.

查原来1000行记录的前5行的rowid，与原来的rowid不一样了

SQL> select rowid,id from t1 where id>=1 and id<=5;

ROWID ID

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

AAASvnAAIAAAAAcAFr 1

AAASvnAAIAAAAAcAFs 2

AAASvnAAIAAAAAcAFt 3

AAASvnAAIAAAAAcAFu 4

AAASvnAAIAAAAAcAFv 5

查原来1000行记录前5行所在的文件号，块号，行号，与原来的块号行号不一样了

SQL> select id,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from t1 where id>=1 and id<=5;

ID FILE# BLOCK# ROW#

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

1 8 28 363

2 8 28 364

3 8 28 365

4 8 28 366

5 8 28 367

我做这个实验是把表空间搞小一点这样更方便观察，在t1表先添加1000条记录，然后delete，最后再向里面插一些记录直到期把空间占完，这样最后新插入的记录会占用原来1000条记录的空间。。。

完毕！

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********

Name: guoyJoe

QQ: 252803295