下面我马上做个实验来验证一下:
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条记录的空间。。。