/***创建t3表改为数值型/
create table t3 (id int,a1 char(10),a2 char(10));
import random
arr01=[]
for i in range(100):
while True:
a= random.randint(1,100)
if not a in arr01:
print a
arr01.append(a)
break;
print arr01
print len(arr01)
for x in arr01:
# print x
print "insert into t3 values(%s,'%sa','%sb')" % (x,x,x)
insert into t3 values(43,'43a','43b')
insert into t3 values(71,'71a','71b')
insert into t3 values(59,'59a','59b')
insert into t3 values(34,'34a','34b')
insert into t3 values(76,'76a','76b')
insert into t3 values(32,'32a','32b')
insert into t3 values(84,'84a','84b')
insert into t3 values(14,'14a','14b')
insert into t3 values(63,'63a','63b')
insert into t3 values(51,'51a','51b')
insert into t3 values(12,'12a','12b')
insert into t3 values(19,'19a','19b')
insert into t3 values(62,'62a','62b')
insert into t3 values(30,'30a','30b')
insert into t3 values(73,'73a','73b')
insert into t3 values(18,'18a','18b')
insert into t3 values(33,'33a','33b')
insert into t3 values(15,'15a','15b')
insert into t3 values(4,'4a','4b')
insert into t3 values(97,'97a','97b')
insert into t3 values(48,'48a','48b')
insert into t3 values(54,'54a','54b')
insert into t3 values(87,'87a','87b')
insert into t3 values(6,'6a','6b')
insert into t3 values(60,'60a','60b')
insert into t3 values(44,'44a','44b')
insert into t3 values(98,'98a','98b')
insert into t3 values(35,'35a','35b')
insert into t3 values(85,'85a','85b')
insert into t3 values(8,'8a','8b')
insert into t3 values(45,'45a','45b')
insert into t3 values(68,'68a','68b')
insert into t3 values(47,'47a','47b')
insert into t3 values(61,'61a','61b')
insert into t3 values(2,'2a','2b')
insert into t3 values(25,'25a','25b')
insert into t3 values(26,'26a','26b')
insert into t3 values(90,'90a','90b')
insert into t3 values(3,'3a','3b')
insert into t3 values(58,'58a','58b')
insert into t3 values(89,'89a','89b')
insert into t3 values(86,'86a','86b')
insert into t3 values(81,'81a','81b')
insert into t3 values(53,'53a','53b')
insert into t3 values(29,'29a','29b')
insert into t3 values(56,'56a','56b')
insert into t3 values(16,'16a','16b')
insert into t3 values(75,'75a','75b')
insert into t3 values(1,'1a','1b')
insert into t3 values(17,'17a','17b')
insert into t3 values(31,'31a','31b')
insert into t3 values(72,'72a','72b')
insert into t3 values(9,'9a','9b')
insert into t3 values(65,'65a','65b')
insert into t3 values(94,'94a','94b')
insert into t3 values(69,'69a','69b')
insert into t3 values(74,'74a','74b')
insert into t3 values(93,'93a','93b')
insert into t3 values(64,'64a','64b')
insert into t3 values(11,'11a','11b')
insert into t3 values(50,'50a','50b')
insert into t3 values(5,'5a','5b')
insert into t3 values(96,'96a','96b')
insert into t3 values(37,'37a','37b')
insert into t3 values(36,'36a','36b')
insert into t3 values(83,'83a','83b')
insert into t3 values(22,'22a','22b')
insert into t3 values(24,'24a','24b')
insert into t3 values(10,'10a','10b')
insert into t3 values(46,'46a','46b')
insert into t3 values(40,'40a','40b')
insert into t3 values(41,'41a','41b')
insert into t3 values(91,'91a','91b')
insert into t3 values(100,'100a','100b')
insert into t3 values(49,'49a','49b')
insert into t3 values(28,'28a','28b')
insert into t3 values(99,'99a','99b')
insert into t3 values(78,'78a','78b')
insert into t3 values(23,'23a','23b')
insert into t3 values(21,'21a','21b')
insert into t3 values(42,'42a','42b')
insert into t3 values(92,'92a','92b')
insert into t3 values(80,'80a','80b')
insert into t3 values(38,'38a','38b')
insert into t3 values(66,'66a','66b')
insert into t3 values(52,'52a','52b')
insert into t3 values(13,'13a','13b')
insert into t3 values(57,'57a','57b')
insert into t3 values(55,'55a','55b')
insert into t3 values(79,'79a','79b')
insert into t3 values(39,'39a','39b')
insert into t3 values(88,'88a','88b')
insert into t3 values(82,'82a','82b')
insert into t3 values(95,'95a','95b')
insert into t3 values(20,'20a','20b')
insert into t3 values(70,'70a','70b')
insert into t3 values(7,'7a','7b')
insert into t3 values(77,'77a','77b')
insert into t3 values(27,'27a','27b')
insert into t3 values(67,'67a','67b')
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCAN',
tabname => 'T3',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
insert into t3 values(101,null,'cccc');
SQL> select TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME, IOT_NAME from user_tables;
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------
T4 USERS
T3 USERS
SQL> select object_name,object_id from user_objects;
OBJECT_NAME OBJECT_ID
------------------------------ ----------
T3 88719
T4 88718
create index t3_idx1 on t3(id);
create index t3_idx2 on t3(a1);
SQL> select object_name,object_id from user_objects;
OBJECT_NAME OBJECT_ID
------------------------------ ----------
T3_IDX2 88721
T4 88718
T3 88719
T3_IDX1 88720
SQL> select index_name,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,table_type from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE
-------------------- -------------------- -------------------- ------------------------------ ------------------------------
T3_IDX2 NORMAL SCAN T3 TABLE
T3_IDX1 NORMAL SCAN T3 TABLE
2.将索引dump到trace文件中
2.将索引dump到trace文件中
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oracle/diag/rdbms/serv/serv/trace/serv_ora_2167.trc
SQL> alter session set events 'immediate trace name treedump level 88720';
会话已更改。
[oracle@node01 ~]$ cat /oracle/diag/rdbms/serv/serv/trace/serv_ora_2167.trc
Trace file /oracle/diag/rdbms/serv/serv/trace/serv_ora_2167.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name: Linux
Node name: node01
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: serv
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 2167, image: oracle@node01 (TNS V1-V3)
*** 2019-04-10 07:08:19.429
*** SESSION ID:(1705.13) 2019-04-10 07:08:19.429
*** CLIENT ID:() 2019-04-10 07:08:19.429
*** SERVICE NAME:(SYS$USERS) 2019-04-10 07:08:19.429
*** MODULE NAME:(SQL*Plus) 2019-04-10 07:08:19.429
*** ACTION NAME:() 2019-04-10 07:08:19.429
----- begin tree dump
leaf: 0x102cbcb 16960459 (0: nrow: 101 rrow: 101)
----- end tree dump
SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='T3_IDX1';
INDEX_NAME PREFIX_LENGTH BLEVEL LEAF_BLOCKS
-------------------- ------------- ---------- -----------
T3_IDX1 0 1
SQL> select dbms_utility.data_block_address_file('16960459') FILE_ID,
dbms_utility.data_block_address_block('16960459') BLOCK_ID
from dual; 2 3
FILE_ID BLOCK_ID
---------- ----------
4 183243
SQL> select header_file,header_block from dba_segments where segment_name='T3_IDX1';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 183242
SQL> select dbms_utility.data_block_address_file(16960459)fno,
dbms_utility.data_block_address_block(16960459) bkno from dual 2 ;
FNO BKNO
---------- ----------
4 183243
SQL> alter system dump datafile 4 block 183243;
系统已更改。
[root@node01 ~]# cat /oracle/diag/rdbms/serv/serv/trace/serv_ora_5035.trc
Trace file /oracle/diag/rdbms/serv/serv/trace/serv_ora_5035.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name: Linux
Node name: node01
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine: x86_64
VM name: VMWare Version: 6
Instance name: serv
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 5035, image: oracle@node01 (TNS V1-V3)
*** 2019-04-10 07:11:58.835
*** SESSION ID:(573.67) 2019-04-10 07:11:58.835
*** CLIENT ID:() 2019-04-10 07:11:58.835
*** SERVICE NAME:(SYS$USERS) 2019-04-10 07:11:58.835
*** MODULE NAME:(sqlplus@node01 (TNS V1-V3)) 2019-04-10 07:11:58.835
*** ACTION NAME:() 2019-04-10 07:11:58.835
Start dump data blocks tsn: 4 file#:4 minblk 183243 maxblk 183243
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16960459
BH (0x8d3e5ed0) file#: 4 rdba: 0x0102cbcb (4/183243) class: 1 ba: 0x8d1a4000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,19
dbwrid: 0 obj: 88720 objn: 88720 tsn: 4 afn: 4 hint: f
hash: [0x9861c950,0x9861c950] lru: [0x8d3e60f8,0x8d3e5e88]
ckptq: [NULL] fileq: [NULL] objq: [0x8d3e6120,0x6a9bb670] objaq: [0x8d3e6130,0x6a9bb660]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 2
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 4 rdba: 0x0102cbcb (4/183243)
scn: 0x0000.00168d6c seq: 0x01 flg: 0x04 tail: 0x8d6c0601
frmt: 0x02 chkval: 0x466c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F2CC3D4FA00 to 0x00007F2CC3D51A00
7F2CC3D4FA00 0000A206 0102CBCB 00168D6C 04010000 [........l.......]
7F2CC3D4FA10 0000466C 00000002 00015A90 00168D6A [lF.......Z..j...]
7F2CC3D4FA20 00000000 00320002 0102CBC8 00000000 [......2.........]
7F2CC3D4FA30 00000000 00000000 00000000 00000000 [................]
7F2CC3D4FA40 00000000 0000FFFF 00000000 00000000 [................]
7F2CC3D4FA50 00000000 00008000 00168D6A 00000000 [........j.......]
7F2CC3D4FA60 00000000 02800000 00000000 00EE0065 [............e...]
7F2CC3D4FA70 19B51AA3 00000000 00000000 00000000 [................]
7F2CC3D4FA80 00000000 00001F60 1F481F54 1F301F3C [....`...T.H.<.0.]
7F2CC3D4FA90 1F181F24 1F001F0C 1EE81EF4 1ED01EDC [$...............]
7F2CC3D4FAA0 1EB81EC4 1EA01EAC 1E881E94 1E701E7C [............|.p.]
7F2CC3D4FAB0 1E581E64 1E401E4C 1E281E34 1E101E1C [d.X.L.@.4.(.....]
7F2CC3D4FAC0 1DF81E04 1DE01DEC 1DC81DD4 1DB01DBC [................]
7F2CC3D4FAD0 1D981DA4 1D801D8C 1D681D74 1D501D5C [........t.h..P.]
7F2CC3D4FAE0 1D381D44 1D201D2C 1D081D14 1CF01CFC [D.8.,. .........]
7F2CC3D4FAF0 1CD81CE4 1CC01CCC 1CA81CB4 1C901C9C [................]
7F2CC3D4FB00 1C781C84 1C601C6C 1C481C54 1C301C3C [..x.l.`.T.H.<.0.]
7F2CC3D4FB10 1C181C24 1C001C0C 1BE81BF4 1BD01BDC [$...............]
7F2CC3D4FB20 1BB81BC4 1BA01BAC 1B881B94 1B701B7C [............|.p.]
7F2CC3D4FB30 1B581B64 1B401B4C 1B281B34 1B101B1C [d.X.L.@.4.(.....]
7F2CC3D4FB40 1AF81B04 1AE01AEC 1AC81AD4 1AB01ABC [................]
7F2CC3D4FB50 00001AA3 00000000 00000000 00000000 [................]
7F2CC3D4FB60 00000000 00000000 00000000 00000000 [................]
Repeat 409 times
7F2CC3D51500 00000000 00000000 02C20300 02010602 [................]
7F2CC3D51510 0000C3CB C2020000 02010602 2100C5CB [...............!]
7F2CC3D51520 C1020000 02010664 5000C5CB C1020000 [....d......P....]
7F2CC3D51530 02010663 4200C5CB C1020000 02010662 [c......B....b...]
7F2CC3D51540 2400C5CB C1020000 02010661 2000C5CB [...$....a...... ]
7F2CC3D51550 C1020000 02010660 2B00C5CB C1020000 [....`......+....]
7F2CC3D51560 0201065F 5B00C5CB C1020000 0201065E [_......[....^...]
7F2CC3D51570 5600C5CB C1020000 0201065D 0500C5CB [...V....].......]
7F2CC3D51580 C1020000 0201065C 2800C5CB C1020000 [..........(....]
7F2CC3D51590 0201065B 2500C5CB C1020000 0201065A [[......%....Z...]
7F2CC3D515A0 0300C5CB C1020000 02010659 4A00C5CB [........Y......J]
7F2CC3D515B0 C1020000 02010658 3700C5CB C1020000 [....X......7....]
7F2CC3D515C0 02010657 3300C5CB C1020000 02010656 [W......3....V...]
7F2CC3D515D0 1700C5CB C1020000 02010655 1300C5CB [........U.......]
7F2CC3D515E0 C1020000 02010654 3200C5CB C1020000 [....T......2....]
7F2CC3D515F0 02010653 2F00C5CB C1020000 02010652 [S....../....R...]
7F2CC3D51600 6100C5CB C1020000 02010651 4C00C5CB [...a....Q......L]
7F2CC3D51610 C1020000 02010650 3D00C5CB C1020000 [....P......=....]
7F2CC3D51620 0201064F 0800C5CB C1020000 0201064E [O...........N...]
7F2CC3D51630 4600C5CB C1020000 0201064D 1800C5CB [...F....M.......]
7F2CC3D51640 C1020000 0201064C 5D00C5CB C1020000 [....L......]....]
7F2CC3D51650 0201064B 0D00C5CB C1020000 0201064A [K...........J...]
7F2CC3D51660 4000C5CB C1020000 02010649 3A00C5CB [...@....I......:]
7F2CC3D51670 C1020000 02010648 3100C5CB C1020000 [....H......1....]
7F2CC3D51680 02010647 3000C5CB C1020000 02010646 [G......0....F...]
7F2CC3D51690 3C00C5CB C1020000 02010645 4D00C5CB [...<....E......M]
7F2CC3D516A0 C1020000 02010644 2700C5CB C1020000 [....D......'....]
7F2CC3D516B0 02010643 3B00C5CB C1020000 02010642 [C......;....B...]
7F2CC3D516C0 1000C5CB C1020000 02010641 3800C5CB [........A......8]
7F2CC3D516D0 C1020000 02010640 1E00C5CB C1020000 [....@...........]
7F2CC3D516E0 0201063F 1D00C5CB C1020000 0201063E [?...........>...]
7F2CC3D516F0 0E00C5CB C1020000 0201063D 5100C5CB [........=......Q]
7F2CC3D51700 C1020000 0201063C 1500C5CB C1020000 [....<...........]
7F2CC3D51710 0201063B 1A00C5CB C1020000 0201063A [;...........:...]
7F2CC3D51720 0900C5CB C1020000 02010639 0F00C5CB [........9.......]
7F2CC3D51730 C1020000 02010638 2600C5CB C1020000 [....8......&....]
7F2CC3D51740 02010637 4400C5CB C1020000 02010636 [7......D....6...]
7F2CC3D51750 0B00C5CB C1020000 02010635 4100C5CB [........5......A]
7F2CC3D51760 C1020000 02010634 2900C5CB C1020000 [....4......)....]
7F2CC3D51770 02010633 5C00C5CB C1020000 02010632 [3..........2...]
7F2CC3D51780 0200C5CB C1020000 02010631 0100C5CB [........1.......]
7F2CC3D51790 C1020000 02010630 6000C5CB C1020000 [....0......`....]
7F2CC3D517A0 0201062F 1400C5CB C1020000 0201062E [/...............]
7F2CC3D517B0 6300C5CB C1020000 0201062D 4500C5CB [...c....-......E]
7F2CC3D517C0 C1020000 0201062C 4B00C5CB C1020000 [....,......K....]
7F2CC3D517D0 0201062B 3400C5CB C1020000 0201062A [+......4....*...]
7F2CC3D517E0 0000C5CB C1020000 02010629 5500C5CB [........)......U]
7F2CC3D517F0 C1020000 02010628 3900C5CB C1020000 [....(......9....]
7F2CC3D51800 02010627 4700C5CB C1020000 02010626 ['......G....&...]
7F2CC3D51810 0700C5CB C1020000 02010625 1200C5CB [........%.......]
7F2CC3D51820 C1020000 02010624 5700C5CB C1020000 [....$......W....]
7F2CC3D51830 02010623 3E00C5CB C1020000 02010622 [#......>...."...]
7F2CC3D51840 5200C5CB C1020000 02010621 2200C5CB [...R....!......"]
7F2CC3D51850 C1020000 02010620 0600C5CB C1020000 [.... ...........]
7F2CC3D51860 0201061F 3F00C5CB C1020000 0201061E [.......?........]
7F2CC3D51870 5800C5CB C1020000 0201061D 4900C5CB [...X...........I]
7F2CC3D51880 C1020000 0201061C 1F00C5CB C1020000 [................]
7F2CC3D51890 0201061B 5900C5CB C1020000 0201061A [.......Y........]
7F2CC3D518A0 5300C5CB C1020000 02010619 2C00C5CB [...S...........,]
7F2CC3D518B0 C1020000 02010618 2E00C5CB C1020000 [................]
7F2CC3D518C0 02010617 4E00C5CB C1020000 02010616 [.......N........]
7F2CC3D518D0 5A00C5CB C1020000 02010615 1C00C5CB [...Z............]
7F2CC3D518E0 C1020000 02010614 0A00C5CB C1020000 [................]
7F2CC3D518F0 02010613 0400C5CB C1020000 02010612 [................]
7F2CC3D51900 1B00C5CB C1020000 02010611 6200C5CB [...............b]
7F2CC3D51910 C1020000 02010610 0C00C5CB C1020000 [................]
7F2CC3D51920 0201060F 5400C5CB C1020000 0201060E [.......T........]
7F2CC3D51930 2300C5CB C1020000 0201060D 1100C5CB [...#............]
7F2CC3D51940 C1020000 0201060C 4F00C5CB C1020000 [...........O....]
7F2CC3D51950 0201060B 2D00C5CB C1020000 0201060A [.......-........]
7F2CC3D51960 1900C5CB C1020000 02010609 5F00C5CB [..............._]
7F2CC3D51970 C1020000 02010608 2A00C5CB C1020000 [...........*....]
7F2CC3D51980 02010607 4800C5CB C1020000 02010606 [.......H........]
7F2CC3D51990 1600C5CB C1020000 02010605 3500C5CB [...............5]
7F2CC3D519A0 C1020000 02010604 5E00C5CB C1020000 [...........^....]
7F2CC3D519B0 02010603 3600C5CB C1020000 02010602 [.......6........]
7F2CC3D519C0 4300C5CB 00000000 00000000 00000000 [...C............]
7F2CC3D519D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F2CC3D519F0 00000000 00000000 00000000 8D6C0601 [..............l.]
Block header dump: 0x0102cbcb
Object id on Block? Y
seg/obj: 0x15a90 csc: 0x00.168d6a itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x102cbc8 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 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00168d6a
Leaf block dump
===============
header address 139830240803428=0x7f2cc3d4fa64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 101
kdxcofbo 238=0xee
kdxcofeo 6819=0x1aa3
kdxcoavs 6581
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02 #c102 表示1
col 1; len 6; (6): 01 02 cb c5 00 43 #表示rowid
row#1[8008] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03 #c103 表示2
col 1; len 6; (6): 01 02 cb c5 00 36
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04 #c104 表示3
col 1; len 6; (6): 01 02 cb c5 00 5e
row#3[7984] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 02 cb c5 00 35
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 02 cb c5 00 16
row#5[7960] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 02 cb c5 00 48
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 02 cb c5 00 2a
row#7[7936] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 02 cb c5 00 5f
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 02 cb c5 00 19
row#9[7912] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 02 cb c5 00 2d
row#10[7900] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0c
col 1; len 6; (6): 01 02 cb c5 00 4f
row#11[7888] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0d
col 1; len 6; (6): 01 02 cb c5 00 11
row#12[7876] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0e
col 1; len 6; (6): 01 02 cb c5 00 23
row#13[7864] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0f
col 1; len 6; (6): 01 02 cb c5 00 54
row#14[7852] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 10
col 1; len 6; (6): 01 02 cb c5 00 0c
row#15[7840] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 11
col 1; len 6; (6): 01 02 cb c5 00 62
row#16[7828] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 12
col 1; len 6; (6): 01 02 cb c5 00 1b
row#17[7816] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 13
col 1; len 6; (6): 01 02 cb c5 00 04
row#18[7804] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 14
col 1; len 6; (6): 01 02 cb c5 00 0a
row#19[7792] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 15
col 1; len 6; (6): 01 02 cb c5 00 1c
row#20[7780] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 16
col 1; len 6; (6): 01 02 cb c5 00 5a
row#21[7768] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 17
col 1; len 6; (6): 01 02 cb c5 00 4e
row#22[7756] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 18
col 1; len 6; (6): 01 02 cb c5 00 2e
row#23[7744] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 19
col 1; len 6; (6): 01 02 cb c5 00 2c
row#24[7732] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 1a
col 1; len 6; (6): 01 02 cb c5 00 53
row#25[7720] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 1b
col 1; len 6; (6): 01 02 cb c5 00 59
row#26[7708] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 1c
col 1; len 6; (6): 01 02 cb c5 00 1f
row#27[7696] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 1d
col 1; len 6; (6): 01 02 cb c5 00 49
row#28[7684] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 1e
col 1; len 6; (6): 01 02 cb c5 00 58
row#29[7672] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 1f
col 1; len 6; (6): 01 02 cb c5 00 3f
row#30[7660] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 20
col 1; len 6; (6): 01 02 cb c5 00 06
row#31[7648] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 21
col 1; len 6; (6): 01 02 cb c5 00 22
row#32[7636] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 22
col 1; len 6; (6): 01 02 cb c5 00 52
row#33[7624] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 23
col 1; len 6; (6): 01 02 cb c5 00 3e
row#34[7612] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 24
col 1; len 6; (6): 01 02 cb c5 00 57
row#35[7600] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 25
col 1; len 6; (6): 01 02 cb c5 00 12
row#36[7588] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 26
col 1; len 6; (6): 01 02 cb c5 00 07
row#37[7576] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 27
col 1; len 6; (6): 01 02 cb c5 00 47
row#38[7564] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 28
col 1; len 6; (6): 01 02 cb c5 00 39
row#39[7552] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 29
col 1; len 6; (6): 01 02 cb c5 00 55
row#40[7540] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 2a
col 1; len 6; (6): 01 02 cb c5 00 00
row#41[7528] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 2b
col 1; len 6; (6): 01 02 cb c5 00 34
row#42[7516] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 2c
col 1; len 6; (6): 01 02 cb c5 00 4b
row#43[7504] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 2d
col 1; len 6; (6): 01 02 cb c5 00 45
row#44[7492] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 2e
col 1; len 6; (6): 01 02 cb c5 00 63
row#45[7480] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 2f
col 1; len 6; (6): 01 02 cb c5 00 14
row#46[7468] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 30
col 1; len 6; (6): 01 02 cb c5 00 60
row#47[7456] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 31
col 1; len 6; (6): 01 02 cb c5 00 01
row#48[7444] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 32
col 1; len 6; (6): 01 02 cb c5 00 02
row#49[7432] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 33
col 1; len 6; (6): 01 02 cb c5 00 5c
row#50[7420] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 34
col 1; len 6; (6): 01 02 cb c5 00 29
row#51[7408] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 35
col 1; len 6; (6): 01 02 cb c5 00 41
row#52[7396] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 36
col 1; len 6; (6): 01 02 cb c5 00 0b
row#53[7384] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 37
col 1; len 6; (6): 01 02 cb c5 00 44
row#54[7372] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 38
col 1; len 6; (6): 01 02 cb c5 00 26
row#55[7360] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 39
col 1; len 6; (6): 01 02 cb c5 00 0f
row#56[7348] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 3a
col 1; len 6; (6): 01 02 cb c5 00 09
row#57[7336] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 3b
col 1; len 6; (6): 01 02 cb c5 00 1a
row#58[7324] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 3c
col 1; len 6; (6): 01 02 cb c5 00 15
row#59[7312] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 3d
col 1; len 6; (6): 01 02 cb c5 00 51
row#60[7300] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 3e
col 1; len 6; (6): 01 02 cb c5 00 0e
row#61[7288] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 3f
col 1; len 6; (6): 01 02 cb c5 00 1d
row#62[7276] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 40
col 1; len 6; (6): 01 02 cb c5 00 1e
row#63[7264] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 41
col 1; len 6; (6): 01 02 cb c5 00 38
row#64[7252] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 42
col 1; len 6; (6): 01 02 cb c5 00 10
row#65[7240] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 43
col 1; len 6; (6): 01 02 cb c5 00 3b
row#66[7228] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 44
col 1; len 6; (6): 01 02 cb c5 00 27
row#67[7216] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 45
col 1; len 6; (6): 01 02 cb c5 00 4d
row#68[7204] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 46
col 1; len 6; (6): 01 02 cb c5 00 3c
row#69[7192] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 47
col 1; len 6; (6): 01 02 cb c5 00 30
row#70[7180] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 48
col 1; len 6; (6): 01 02 cb c5 00 31
row#71[7168] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 49
col 1; len 6; (6): 01 02 cb c5 00 3a
row#72[7156] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 4a
col 1; len 6; (6): 01 02 cb c5 00 40
row#73[7144] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 4b
col 1; len 6; (6): 01 02 cb c5 00 0d
row#74[7132] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 4c
col 1; len 6; (6): 01 02 cb c5 00 5d
row#75[7120] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 4d
col 1; len 6; (6): 01 02 cb c5 00 18
row#76[7108] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 4e
col 1; len 6; (6): 01 02 cb c5 00 46
row#77[7096] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 4f
col 1; len 6; (6): 01 02 cb c5 00 08
row#78[7084] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 50
col 1; len 6; (6): 01 02 cb c5 00 3d
row#79[7072] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 51
col 1; len 6; (6): 01 02 cb c5 00 4c
row#80[7060] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 52
col 1; len 6; (6): 01 02 cb c5 00 61
row#81[7048] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 53
col 1; len 6; (6): 01 02 cb c5 00 2f
row#82[7036] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 54
col 1; len 6; (6): 01 02 cb c5 00 32
row#83[7024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 55
col 1; len 6; (6): 01 02 cb c5 00 13
row#84[7012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 56
col 1; len 6; (6): 01 02 cb c5 00 17
row#85[7000] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 57
col 1; len 6; (6): 01 02 cb c5 00 33
row#86[6988] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 58
col 1; len 6; (6): 01 02 cb c5 00 37
row#87[6976] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 59
col 1; len 6; (6): 01 02 cb c5 00 4a
row#88[6964] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 5a
col 1; len 6; (6): 01 02 cb c5 00 03
row#89[6952] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 5b
col 1; len 6; (6): 01 02 cb c5 00 25
row#90[6940] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 5c
col 1; len 6; (6): 01 02 cb c5 00 28
row#91[6928] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 5d
col 1; len 6; (6): 01 02 cb c5 00 05
row#92[6916] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 5e
col 1; len 6; (6): 01 02 cb c5 00 56
row#93[6904] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 5f
col 1; len 6; (6): 01 02 cb c5 00 5b
row#94[6892] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 60
col 1; len 6; (6): 01 02 cb c5 00 2b
row#95[6880] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 61
col 1; len 6; (6): 01 02 cb c5 00 20
row#96[6868] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 62
col 1; len 6; (6): 01 02 cb c5 00 24
row#97[6856] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 63
col 1; len 6; (6): 01 02 cb c5 00 42
row#98[6844] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 64
col 1; len 6; (6): 01 02 cb c5 00 50
row#99[6832] flag: ------, lock: 0, len=12
col 0; len 2; (2): c2 02
col 1; len 6; (6): 01 02 cb c5 00 21
row#100[6819] flag: ------, lock: 0, len=13
col 0; len 3; (3): c2 02 02
col 1; len 6; (6): 01 02 cb c3 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 183243 maxblk 183243
declare
n number;
begin
dbms_stats.convert_raw_value('c102',
n);
dbms_output.put_line(n);
end;
col 0: 为keyvalue.
col 1: 为Rowid
Leaf Blocks
All leaf blocks are at the same depth from the root branch block. Leaf blocks store the following:
The complete key value for every row
ROWIDs of the table rows
SQL> select
2 to_number('00','xx'),
3 to_number('41','xx'),
4 to_number('62','xx'),
5 to_number('d0','xx'),
6 to_number('00','xx'),
7 to_number('a4','xx')
8 from dual;
col 1; len 6; (6): 01 02 cb c5 00 43 #表示rowid
SQL> select
to_number('00','xx'),
to_number('02','xx'),
to_number('cb','xx'),
to_number('c5','xx'),
to_number('00','xx'),
to_number('43','xx')
from dual; 2 3 4 5 6 7 8
TO_NUMBER('00','XX') TO_NUMBER('02','XX') TO_NUMBER('CB','XX') TO_NUMBER('C5','XX') TO_NUMBER('00','XX') TO_NUMBER('43','XX')
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
0 2 203 197 0 67
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) id
from t3
where id=1 2 3 4 5 6 ;
ROWID OBJECT_ID FILE_ID BLOCK_ID ID
------------------ ---------- ---------- ---------- ----------
AAAVqPAAEAAAsvFABD 88719 4 183237 67
select rowid from t3 where id=1;
SQL> select rowid,
id,
dbms_rowid.rowid_relative_fno('AAAVqPAAEAAAsvFABD') fno,
dbms_rowid.rowid_block_number('AAAVqPAAEAAAsvFABD') bkno,
dbms_rowid.rowid_row_number('AAAVqPAAEAAAsvFABD') rno
from t3
where id = 1; 2 3 4 5 6 7
ROWID ID FNO BKNO RNO
------------------ ---------- ---------- ---------- ----------
AAAVqPAAEAAAsvFABD 1 4 183237 67
col 1; len 6; (6): 01 02 cb c5 00 43 #表示rowid
00000001 00000010 11001011 11000101 00000000 01000011
然后串起来之后前10位 0000000100 转换为10进制表示文件号,=4
然后是接下来的22位 0000101100101111000101 转换为10进制,表示块号=183237
最后面的的16位 0000000001000011 转为10进制 表示行号=67
sql得到是4号文件183237块67行.证明索引里保存的为rowid的一部分信息。
col 1; len 6; (6): 01 02 cb c5 00 36
SQL> select rowid,
id,
dbms_rowid.rowid_relative_fno('AAAVqPAAEAAAsvFAA2') fno,
dbms_rowid.rowid_block_number('AAAVqPAAEAAAsvFAA2') bkno,
dbms_rowid.rowid_row_number('AAAVqPAAEAAAsvFAA2') rno
from t3
where id = 2;
2 3 4 5 6 7
ROWID ID FNO BKNO RNO
------------------ ---------- ---------- ---------- ----------
AAAVqPAAEAAAsvFAA2 2 4 183237 54
col 0; len 2; (2): c1 03 #c103 表示2
col 1; len 6; (6): 01 02 cb c5 00 36
00000001 00000010 11001011 11000101 00000000 00110110
然后串起来之后前10位 0000000100 转换为10进制表示文件号,=4
然后是接下来的22位 0000101100101111000101 转换为10进制,表示块号=183237
最后面的的16位 0000000000110110 转为10进制 表示行号=54
dbms_rowid.rowid_relative_fno：定位该数据行所在的数据文件
dbms_rowid.rowid_block_number：定位该数据行在数据文件的第多少个块
dbms_rowid.rowid_row_number：定位该数据行在数据块的第多少行