Oracle dump索引组织表

朱雀 2022-06-14 22:28 203阅读 0赞

dump了索引组织表，发现跟索引的块结构是一样的。

create table iot\_t

(  
  id number,  
  name varchar2(10),  
  primary key(id)  
)  
organization index;  
  
  
insert into iot\_t values(1,'a');  
insert into iot\_t values(5,'e');  
commit;  
  
**看到传统的找数据块的方法并不奏效**  
SQL> select rowid,  
           dbms\_rowid.rowid\_object(rowid) object\_id,  
           dbms\_rowid.rowid\_relative\_fno(rowid) file\_id,  
           dbms\_rowid.rowid\_block\_number(rowid) block\_id,  
           dbms\_rowid.rowid\_row\_number(rowid) num  
      from iot\_t;  
       dbms\_rowid.rowid\_row\_number(rowid) num  
       \*  
第 5 行出现错误:  
ORA-06553: PLS-306: 调用 'ROWID\_ROW\_NUMBER' 时参数个数或类型错误  
  
**看到它根本不是表**  
SQL> select  table\_name, tablespace\_name, NUM\_ROWS, BLOCKS, EMPTY\_BLOCKS  
      from user\_tables  
     where lower(table\_name) in ('iot\_t');  
TABLE\_NAME TABLESPACE\_NAME    NUM\_ROWS  BLOCKS EMPTY\_BLOCKS  
\---------- ---------------- ---------- ------- ------------  
IOT\_T  
  
**索引组织表就是索引**  
SQL> select INDEX\_NAME,  
           INDEX\_TYPE,  
           TABLE\_NAME,  
           PCT\_THRESHOLD,  
           INCLUDE\_COLUMN  
      from dba\_indexes  
     where lower(table\_name) in ('iot\_t');  
INDEX\_NAME          INDEX\_TYPE    TABLE\_NAMEPCT\_ THRESHOLD INCLUDE\_COLUMN  
\------------------- ------------- -------------- --------- --------------  
SYS\_IOT\_TOP\_77544   IOT - TOP     IOT\_T              50         0  
  
**按照索引的方式dump**  
SQL>  select OBJECT\_NAME, OBJECT\_ID, DATA\_OBJECT\_ID, OBJECT\_TYPE  
       from dba\_objects  
      where object\_name in ('SYS\_IOT\_TOP\_77544', 'IOT\_T');  
OBJECT\_NAME        OBJECT\_ID DATA\_OBJECT\_ID OBJECT\_TYPE  
\------------------------- ---------- -------------- ---  
IOT\_T                  77544                TABLE  
SYS\_IOT\_TOP\_77544      77545          77545 INDEX  
  
alter session set events 'immediate trace name treedump level 77545';  
**dump索引出来的结果是：**  
\----- begin tree dump  
leaf: 0x1001533 16782643 (0: nrow: 2 rrow: 2)  
\----- end tree dump  
  
**定位出是哪个文件，哪个数据块**  
SQL>  select dbms\_utility.data\_block\_address\_file(16782643) "file",  
           dbms\_utility.data\_block\_address\_block(16782643) "block"  
      from dual;  
      file      block  
\---------- ----------  
         4       5427        
alter system  checkpoint;  
alter system dump datafile 4 block 5427;

**dump出来的数据块结果是：**

Start dump data blocks tsn: 4 file\#:4 minblk 5427 maxblk 5427  
................................................  
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc  
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000  
0x02   0x0007.017.000018c4  0x00c008ee.0193.04  --U-    1  fsc 0x0000.e2b70a42  
...........................................................  
row\#0\[8026\] flag: K-----, lock: 0, len=10  \--第一行数据 c102就是1，61就是a  
col 0; len 2; (2):  c1 02     
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1  
col  0: \[ 1\]  61  
row\#1\[8016\] flag: K-----, lock: 2, len=10  \--第一行数据 c106就是5，65就是e  
col 0; len 2; (2):  c1 06  
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1  
col  0: \[ 1\]  65  
\----- end of leaf block dump -----  
End dump data blocks tsn: 4 file\#: 4 minblk 5427 maxblk 5427  
  
**插入一条数据之后：**  
insert into iot\_t values(2,'b');  
commit;  
alter system  checkpoint;  
alter system dump datafile 4 block 5427;  
  
**dump出来的数据块结果是：**  
Start dump data blocks tsn: 4 file\#:4 minblk 5427 maxblk 5427  
Block header dump:  0x01001533  
 Object id on Block? Y  
 seg/obj: 0x12ee9  csc: 0xcb5.e2b70e52  itc: 2  flg: E  typ: 2 - INDEX  
     brn: 0  bdba: 0x1001530 ver: 0x01 opc: 0  
     inc: 0  exflg: 0  
   
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc  
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000  
0x02   0x0005.01c.000018d0  0x00c00411.0140.03  --U-    1  fsc 0x0000.e2b70e53  
........................................................  
row\#0\[8026\] flag: K-----, lock: 0, len=10  
col 0; len 2; (2):  c1 02  
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1  
col  0: \[ 1\]  61  
row\#1\[8006\] flag: K-----, lock: 2, len=10 --插入到1和5之间  
col 0; len 2; (2):  c1 03  
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1  
col  0: \[ 1\]  62  
row\#2\[8016\] flag: K-----, lock: 0, len=10  
col 0; len 2; (2):  c1 06  
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1  
col  0: \[ 1\]  65  
\----- end of leaf block dump -----  
End dump data blocks tsn: 4 file\#: 4 minblk 5427 maxblk 5427