Oracle dump索引组织表

发布时间：2020-12-12 14:55:04 所属栏目：百科来源：网络整理

导读：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; 看到传统的找数据块的方法并不

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-----,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-----,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-----,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!