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

来源：互联网发布：淘宝qq号卖家不见了编辑：程序博客网时间：2024/06/06 03:08

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

Email: oracledba_cn@hotmail.com

Blog: http://blog.csdn.net/guoyJoe

ITPUB: http://www.itpub.net/space-uid-28460966.html

OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036