</p> select dbms_rowid.rowid_object(rowid) "object", dbms_rowid.rowid_relative_fno(rowid) "file", dbms_rowid.rowid_block_number(rowid) "block", dbms_rowid.rowid_row_number(rowid) "row" from temp_jp/object file block row--------- ---------- ---------- ----------220848 12 12172 0220848 12 12172 1220848 12 12172 24 y# C2 Y4 m, V7 a" E: v
执行完move呼吁后,Oracle为该表指定了一个新的object_id。属于users2表空间的文件号为12。当前的块ID为12172。而行号还连结和原本一样。Move呼吁改变了表的位置,但没有改变表熟行的枚举挨次。接下来,我们还可以用新的object_id来查找该对象的具体信息。& n% g z: H4 Y" { E
select object_id,owner, object_name, object_type,created from dba_objects where object_id = 220848;
) G4 K7 e! m N$ H! E8 J no rows selected7 p2 i" q g/ v( E% r0 M4 K3 U
年夜执行move呼吁后的新RowID获得了object_id没有检索到该对象的具体信息。相反,年夜dba_objects视图看该表的对象ID默示为执行move呼吁前的旧object_id。. I6 `: t' n8 W# `1 {
select object_id,owner, object_name, object_type,created from dba_objectswhere object_name = ‘TEMP_JP‘;OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE CREATED--------- ------ --------------- ------------------- ---------220845 SCOTT TEMP_JP TABLE 30-MAR-08
. x3 ^2 q" ~& Z/ U* ] 这可能是Oracle数据库的一个裂痕。我们来查找一下在曩昔一小时内建树的对象:
5 v2 ~8 ~& ?! |9 j, b) X$ [ select object_id,owner,object_name,object_type,created from dba_objects where createdtrunc(sysdate) -1/24;OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE CREATED--------- ------ --------------- ------------------- ---------220846 SCOTT TEMP_JP_IDX INDEX 30-MAR-08220845 SCOTT TEMP_JP TABLE 30-MAR-08220847 SCOTT TEMP_ROWIDS_JP TABLE 30-MAR-08select object_id, owner, object_name, object_type, created from dba_objects where object_id in (select max(object_id) from dba_objects);OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE CREATED--------- ------- ------------- ------------------- ---------220847 SCOTT TEMP_ROWIDS_JP TABLE 30-MAR-08: g% w" o C- e; Q
用新的RowID来发芽表temp_jp:4 i" c4 \- }$ U+ P- A
select * from temp_jp where rowid = ‘AAA16wAAMAAAC+MAAB‘; COL1 COL2---------- -------------------- 3 SITA
1 k3 `2 [$ ]& J9 h/ j. _5 \. ]7 L7 N 我们用新RowID能够年夜该表发芽到数据。可是,年夜新RowID获得的objectid并不能检索到对象的具体信息。年夜这点看来,Oracle并没有使用年夜今朝的RowID获得的objectid来检索该表的数据,而是使用fileid、blockid和行号来访谒表数据。
& i, C3 [& I. \6 |2 m 那么让我们来看看执行完move呼吁后表temp_jp的索引状况。
7 U4 H3 B8 W7 c" p select index_name, status from dba_indexes where table_name=‘TEMP_JP‘;INDEX_NAME STATUS------------------------------ --------TEMP_JP_IDX UNUSABLEset autot on expselect * from temp_jp where col1=3; COL1 COL2---------- -------------------- 3 SITAExecution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=25) 1 0 TABLE ACCESS (FULL) OF ‘TEMP_JP‘ (TABLE) (Cost=2 Card=1 Bytes=25)
: Z& s- _" e3 u) c- U3 K' V 因为表temp_jp已有的索引无法使用,我们先对该表进行了一次全表扫描。正如你所想到的,表temp_jp的索引仍然存储着无效的RowID。此刻,让我们来看看我们用旧的RowID能不能检索到数据。
1 S2 I- H1 K$ ]+ m* ]8 o SQL select * from temp_jp where rowid = ‘AAA16tAAEAAAADoAAB‘;select * from temp_jp where rowid = ‘AAA16tAAEAAAADoAAB‘ *ERROR at line 1:ORA-01410: invalid ROWID( P( L" C* d/ l0 {" m9 e; M
旧的RowID不再指向该表各行的正确物理位置。而索引temp_jp_idx仍保留着执行move呼吁前旧的无效RowID,所以,Oracle把这些索引标识表记标帜为不成用。不外,我们仍是可以用新的RowID来访谒该表的数据。
/ v1 F9 W6 B) Q) M' z3 S) M select * from temp_jp where rowid = ‘AAA16wAAMAAAC+MAAB‘; COL1 COL2---------- -------------------- 3 SITA3 b, f. j+ `& F- y: o d" p
让我们来斗劲一下执行move呼吁前后表temp_jp的RowID有什么分歧。& u+ U; L! L4 X$ R- c) z% K
select a.col1, a.before_move_rowid,b.rowid after_move_rowidfrom temp_rowids_jp a, temp_jp b where a.col1 = b.col1; COL1 BEFORE_MOVE_ROWID AFTER_MOVE_ROWID--------- ------------------ ------------------ 1 AAA16tAAEAAAADoAAA AAA16wAAMAAAC+MAAA 3 AAA16tAAEAAAADoAAB AAA16wAAMAAAC+MAAB 2 AAA16tAAEAAAADoAAC AAA16wAAMAAAC+MAAC( D7 a5 R- v( E7 f3 [; Q3 }
正如之前所说的,在执行了move呼吁之后,该表转移到了此吐矣闽表空间,而objectid、fileid和blockid都已经发生了转变。因为表内的数据在表移动的过程中并没有发生改变,所以行号仍保留原样。
; Z5 e/ B% N; e ?5 z: Y; ]) o7 y9 F 为了修复不成用索引,用新的正确的RowID来年夜头生成temp_jp_idx索引即可$ q1 K; D `% r
alter index temp_jp_idx rebuild online;select index_name, status from dba_indexes where table_name=‘TEMP_JP‘;INDEX_NAME STATUS------------------------------ --------TEMP_JP_IDX VALIDAfter rebuilding the unusable index, the index is validated.set autot on expselect * from temp_jp where col1=3; COL1 COL2 ---------- -------------------- 3 SITA Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=25) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘TEMP_JP‘ (TABLE) (Cost=2 Card=1 Bytes=25) 2 1 INDEX (RANGE SCAN) OF ‘TEMP_JP_IDX‘ (INDEX) (Cost=1 Card =1)1 t5 Z( X" o8 H- g! I
年夜头生成索引后,就可以使用该索引来对表temp_jp进行select发芽了。 l" O( [+ e" N& b
记住,当你要移动一个表时,该表的索引会被标识表记标帜为不成用,但经由过程年夜头生成指令来将索引转移到新的物理位置时,索引就可以恢复可用性了。 |