sp2 0067 oracle报错,【案例】Oracle报错ORA-1652 详解rowid,index entry header修复办法

最新推荐文章于 2024-07-03 23:04:08 发布

weixin_39988331

最新推荐文章于 2024-07-03 23:04:08 发布

阅读量350

点赞数

文章标签： sp2 0067 oracle报错

【案例】Oracle报错ORA-1652 详解rowid,index entry header修复办法

时间:2016-11-22 21:57 来源:Oracle研究中心作者:网络点击:

次

天萃荷净

Oracle研究中心案例分析：运维DBA反映Oracle数据库出现报错ORA-1652，总结该报错的解决办法，详细解rowid,index entry header修复办法。

本站文章除注明转载外，均为本站原创：转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: 关于ora-1652的一点总结–续(详解rowid,index entry header)

在本文中，对上篇文章中的疑问进行的详细的实验说明和解释，实验过程如下：

SQL> conn roger/roger

Connected.

SQL> CREATE TABLE ht1 AS SELECT * FROM sys.dba_objects WHERE rownum <1000;

TABLE created.

SQL> CREATE INDEX idx_ht1 ON ht1(object_id) tablespace roger;

INDEX created.

SQL> SELECT dump(object_id)

2 FROM ht1

3 WHERE object_id <20 ORDER BY object_id;

DUMP(OBJECT_ID)

--------------------------

Typ=2 Len=2: 193,3

Typ=2 Len=2: 193,4

Typ=2 Len=2: 193,5

Typ=2 Len=2: 193,6

Typ=2 Len=2: 193,7

Typ=2 Len=2: 193,8

Typ=2 Len=2: 193,9

Typ=2 Len=2: 193,10

Typ=2 Len=2: 193,11

Typ=2 Len=2: 193,12

Typ=2 Len=2: 193,13

Typ=2 Len=2: 193,14

Typ=2 Len=2: 193,15

Typ=2 Len=2: 193,16

Typ=2 Len=2: 193,17

Typ=2 Len=2: 193,18

Typ=2 Len=2: 193,19

Typ=2 Len=2: 193,20

18 ROWS selected.

SQL> SELECT dump(object_id)

2 FROM ht1

3 WHERE object_id > 500

4 AND object_id < 510

5 ORDER BY object_id;

DUMP(OBJECT_ID)

----------------------------------------

Typ=2 Len=3: 194,6,2

Typ=2 Len=3: 194,6,3

Typ=2 Len=3: 194,6,4

Typ=2 Len=3: 194,6,5

Typ=2 Len=3: 194,6,6

Typ=2 Len=3: 194,6,7

Typ=2 Len=3: 194,6,8

Typ=2 Len=3: 194,6,9

Typ=2 Len=3: 194,6,10

9 ROWS selected.

我们可以发现，该字段object_id有些是2个字节，有些是3个字节，那么我们应该以2还是3为准呢？

我想应该以该列的平均长度为准，通过分析该表，得到该列的平均长度。

SQL> analyze TABLE ht1 compute statistics FOR TABLE FOR ALL indexes FOR ALL COLUMNS;

TABLE analyzed.

SQL> SELECT TABLE_NAME,COLUMN_NAME,DATA_LENGTH,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED

2 FROM user_tab_columns

3 WHERE TABLE_NAME='HT1';

TABLE_NAME COLUMN_NAME DATA_LENGTH AVG_COL_LEN CHAR_LENGTH C

------------- ----------------- ----------- ----------- ----------- -

HT1 OWNER 30 4 30 B

HT1 OBJECT_NAME 128 14 128 B

HT1 SUBOBJECT_NAME 30 1 30 B

HT1 OBJECT_ID 22 3 0

HT1 DATA_OBJECT_ID 22 3 0

HT1 OBJECT_TYPE 19 6 19 B

HT1 CREATED 7 7 0

HT1 LAST_DDL_TIME 7 7 0

HT1 TIMESTAMP 19 19 19 B

HT1 STATUS 7 5 7 B

HT1 TEMPORARY 1 1 1 B

HT1 GENERATED 1 1 1 B

HT1 SECONDARY 1 1 1 B

13 ROWS selected.

###### 从这里得到object_id平均列长度为3 ######

++++++ 下面再来看rowid是占据多少个字节 ++++++

SQL> SELECT rowid ,

2 substr(rowid,1,6) "OBJECT",

3 substr(rowid,7,3) "FILE",

4 substr(rowid,10,6) "BLOCK",

5 substr(rowid,16,3) "ROW"

6 FROM ht1

7 WHERE object_id <20 ORDER BY object_id;

ROWID OBJECT FILE BLOCK ROW

------------------ ------------ ------ ------------ ------

AAAMpRAAFAAAAAUAAt AAAMpR AAF AAAAAU AAt

AAAMpRAAFAAAAAUAAF AAAMpR AAF AAAAAU AAF

AAAMpRAAFAAAAAUAAu AAAMpR AAF AAAAAU AAu

AAAMpRAAFAAAAAUAAZ AAAMpR AAF AAAAAU AAZ

AAAMpRAAFAAAAAUAAU AAAMpR AAF AAAAAU AAU

AAAMpRAAFAAAAAUAAQ AAAMpR AAF AAAAAU AAQ

AAAMpRAAFAAAAAUAAh AAAMpR AAF AAAAAU AAh

AAAMpRAAFAAAAAUAAM AAAMpR AAF AAAAAU AAM

AAAMpRAAFAAAAAUAAi AAAMpR AAF AAAAAU AAi

AAAMpRAAFAAAAAUAA1 AAAMpR AAF AAAAAU AA1

AAAMpRAAFAAAAAUAAl AAAMpR AAF AAAAAU AAl

AAAMpRAAFAAAAAUAAL AAAMpR AAF AAAAAU AAL

AAAMpRAAFAAAAAUAAT AAAMpR AAF AAAAAU AAT

AAAMpRAAFAAAAAUAAD AAAMpR AAF AAAAAU AAD

AAAMpRAAFAAAAAUAAg AAAMpR AAF AAAAAU AAg

AAAMpRAAFAAAAAUAAK AAAMpR AAF AAAAAU AAK

AAAMpRAAFAAAAAUAAr AAAMpR AAF AAAAAU AAr

AAAMpRAAFAAAAAUAAS AAAMpR AAF AAAAAU AAS

18 ROWS selected.

SQL> SELECT owner,object_id

2 FROM dba_objects

3 WHERE object_name='IDX_HT1';

OWNER OBJECT_ID

------------------------------ ----------

ROGER 51794

SQL> ALTER SESSION SET events 'immediate trace name treedump level 51794';

SESSION altered.

++++++ BEGIN tree dump ++++++

branch: 0x1400024 20971556 (0: nrow: 3, level: 1)

leaf: 0x1400025 20971557 (-1: nrow: 485 rrow: 485)

leaf: 0x1400026 20971558 (0: nrow: 479 rrow: 479)

leaf: 0x1400027 20971559 (1: nrow: 35 rrow: 35)

++++++ END tree dump ++++++

SQL> SELECT DISTINCT t.*

2 FROM (SELECT DBMS_ROWID.rowid_relative_fno (ROWID) file_id,

3 DBMS_ROWID.rowid_block_number (ROWID) block_id

4 FROM ht1) t;

FILE_ID BLOCK_ID

---------- ----------

5 24

5 28

5 21

5 27

5 25

5 29

5 31

5 20

5 23

5 22

5 26

5 30

12 ROWS selected.

SQL> SELECT DBMS_UTILITY.data_block_address_file (20971557) file_id,

2 DBMS_UTILITY.data_block_address_block (20971557) block_number

3 FROM DUAL;

FILE_ID BLOCK_NUMBER

---------- ------------

5 37

###### dump file 5 block 37，信息如下 ######

*** 2011-10-02 06:59:47.502

Start dump data blocks tsn: 6 file#: 5 minblk 37 maxblk 37

buffer tsn: 6 rdba: 0x01400025 (5/37)

scn: 0x0000.00067c52 seq: 0x02 flg: 0x04 tail: 0x7c520602

frmt: 0x02 chkval: 0x4e1b type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0xB732B800 to 0xB732D800

B732B800 0000A206 01400025 00067C52 04020000 [....%.@.R|......]

.............

B732D7D0 00000000 00000000 00000000 00000000 [................]

Repeat 1 times

B732D7F0 00000000 00000000 00000000 7C520602 [..............R|]

Block header dump: 0x01400025

Object id on Block Y

seg/obj: 0xca52 csc: 0x00.67c50 itc: 2 flg: E typ: 2 - INDEX

brn: 0 bdba: 0x1400021 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.00067c50

Leaf block dump

===============

header address 3073554532=0xb732b864

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 485

kdxcofbo 1006=0x3ee

kdxcofeo 1830=0x726

kdxcoavs 824

kdxlespl 0

kdxlende 0

kdxlenxt 20971558=0x1400026

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8032

row#0[8020] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 03

col 1; len 6; (6): 01 40 00 14 00 2d

row#1[8008] flag: ------, lock: 0, len=12

col 0; len 2; (2): c1 04

col 1; len 6; (6): 01 40 00 14 00 05

... ... ...

SQL> col dump FOR a45

SQL> SET LINES 160

SQL> SELECT dump(rowid) dump,

2 dbms_rowid.rowid_object(rowid) object_number,

3 dbms_rowid.rowid_relative_fno(rowid) file_number,

4 dbms_rowid.rowid_block_number(rowid) block_number,

5 dbms_rowid.rowid_row_number(rowid) ROW_NUMBER

6 FROM ht1

7 WHERE object_id < 20

8 ORDER BY object_id;

DUMP OBJECT_NUMBER FILE_NUMBER BLOCK_NUMBER ROW_NUMBER

--------------------------------------------- ------------- ----------- ------------ ----------

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,45 51793 5 20 45

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,5 51793 5 20 5

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,46 51793 5 20 46

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,25 51793 5 20 25

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,20 51793 5 20 20

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,16 51793 5 20 16

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,33 51793 5 20 33

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,12 51793 5 20 12

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,34 51793 5 20 34

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,53 51793 5 20 53

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,37 51793 5 20 37

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,11 51793 5 20 11

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,19 51793 5 20 19

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,3 51793 5 20 3

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,32 51793 5 20 32

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,10 51793 5 20 10

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,43 51793 5 20 43

Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,18 51793 5 20 18

18 ROWS selected.

我们可以看到rowid是10个字节。

我们还需要知道index header占据多数字节，那么如何才能知道呢？当然用bbed来看是最方便的了，如下：

BBED> set file 5 block 37

FILE# 5

BLOCK# 37

BBED> map /v

File: /home/ora10g/oradata/roger/roger01.dbf (5)

Block: 37 Dba:0x01400025

------------------------------------------------------------

KTB Data Block (Index Leaf)

struct kcbh, 20 bytes @0

ub1 type_kcbh @0

ub1 frmt_kcbh @1

ub1 spare1_kcbh @2

ub1 spare2_kcbh @3

ub4 rdba_kcbh @4

ub4 bas_kcbh @8

ub2 wrp_kcbh @12

ub1 seq_kcbh @14

ub1 flg_kcbh @15

ub2 chkval_kcbh @16

ub2 spare3_kcbh @18

struct ktbbh, 72 bytes @20

ub1 ktbbhtyp @20

union ktbbhsid, 4 bytes @24

struct ktbbhcsc, 8 bytes @28

b2 ktbbhict @36

ub1 ktbbhflg @38

ub1 ktbbhfsl @39

ub4 ktbbhfnx @40

struct ktbbhitl[2], 48 bytes @44

struct kdxle, 32 bytes @100

struct kdxlexco, 16 bytes @100

b2 kdxlespl @116

sb2 kdxlende @118

ub4 kdxlenxt @120

ub4 kdxleprv @124

ub1 kdxledsz @128

ub1 kdxleunuse @129

b2 kd_off[485] @132

ub1 freespace[824] @1102

ub1 rowdata[6202] @1926

ub4 tailchk @8188

BBED> p kdxlexco

struct kdxlexco, 16 bytes @100

ub1 kdxcolev @100 0x00

ub1 kdxcolok @101 0x00

ub1 kdxcoopc @102 0x80

ub1 kdxconco @103 0x02

ub4 kdxcosdc @104 0x00000000

sb2 kdxconro @108 485

b2 kdxcofbo @110 1006

b2 kdxcofeo @112 1830

b2 kdxcoavs @114 824

这里可以看到是16个字节，但是这并不是活index entry 长度就是16个字节，还要加上2.

为什么要加2呢？因为我们还要算上行头。

SQL> SELECT INDEX_NAME,PCT_THRESHOLD,PCT_FREE,BLEVEL,LEAF_BLOCKS,NUM_ROWS

2 FROM dba_indexes

3 WHERE TABLE_NAME='HT1';

INDEX_NAME PCT_THRESHOLD PCT_FREE BLEVEL LEAF_BLOCKS NUM_ROWS

------------------------------ ------------- ---------- ---------- ----------- ----------

IDX_HT1 10 1 3 999

SQL> SELECT 8192*0.9-20 FROM dual;

8192*0.9-20

-----------

7352.8

SQL> SELECT 7352/18 FROM dual;

7352/18

----------

408.444444

SQL> SELECT COUNT(*) FROM ht1;

COUNT(*)

----------

999

SQL> SELECT 999/408 FROM dual;

999/408

----------

2.44852941

也就是说一个block最多存放408个索引条目，为表ht1创建INDEX(object_id)那么需要3个INDEX block。

那么我们来看看我们创建的idx_ht1 索引是不是使用了3个block呢？

SQL> SELECT blocks FROM dba_segments WHERE segment_name='IDX_HT1';

BLOCKS

----------

SQL> SELECT blocks,INITIAL_EXTENT,EXTENTS FROM dba_segments WHERE segment_name='IDX_HT1';

BLOCKS INITIAL_EXTENT EXTENTS

---------- -------------- ----------

8 65536 1

这里至于说为什么创建该INDEX只需要3个block即可，为啥却占据了8个block呢？

很简单，因为初始化extent 为65536大小，即为8个block。换句话说创建一个索引，最小分配初始化extent大小的空间。

SQL> analyze INDEX idx_ht1 validate STRUCTURE;

INDEX analyzed.

SQL> SET heading off

SQL> col name newline

SQL> col headsep newline

SQL> col height newline

SQL> col blocks newline

SQL> col lf_rows newline

SQL> col lf_blks newline

SQL> col lf_rows_len newline

SQL> col lf_blk_len newline

SQL> col br_rows newline

SQL> col br_blks newline

SQL> col br_rows_len newline

SQL> col br_blk_len newline

SQL> col del_lf_rows newline

SQL> col del_lf_rows_len newline

SQL> col distinct_keys newline

SQL> col most_repeated_key newline

SQL> col btree_space newline

SQL> col used_space newline

SQL> col pct_used newline

SQL> col rows_per_key newline

SQL> col blks_gets_per_access newline

SQL> SELECT

2 name,

3 '----------------------------------------------------------' headsep,

4 'height '||to_char(height, '999,999,990') height,

5 'blocks '||to_char(blocks, '999,999,990') blocks,

6 'del_lf_rows '||to_char(del_lf_rows,'999,999,990') del_lf_rows,

7 'del_lf_rows_len '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,

8 'distinct_keys '||to_char(distinct_keys,'999,999,990') distinct_keys,

9 'most_repeated_key '||to_char(most_repeated_key,'999,999,990') most_repeated_key,

10 'btree_space '||to_char(btree_space,'999,999,990') btree_space,

11 'used_space '||to_char(used_space,'999,999,990') used_space,

12 'pct_used '||to_char(pct_used,'990') pct_used,

13 'rows_per_key '||to_char(rows_per_key,'999,999,990') rows_per_key,

14 'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,

15 'lf_rows '||to_char(lf_rows, '999,999,990')||' '||+

16 'br_rows '||to_char(br_rows, '999,999,990') br_rows,

17 'lf_blks '||to_char(lf_blks, '999,999,990')||' '||+

18 'br_blks '||to_char(br_blks, '999,999,990') br_blks,

19 'lf_rows_len '||to_char(lf_rows_len,'999,999,990')||' '||+

20 'br_rows_len '||to_char(br_rows_len,'999,999,990') br_rows_len,

21 'lf_blk_len '||to_char(lf_blk_len, '999,999,990')||' '||+

22 'br_blk_len '||to_char(br_blk_len, '999,999,990') br_blk_len

23 FROM

24 index_stats

25 /

SET verify ON

IDX_HT1

----------------------------------------------------------

height 2

blocks 8

del_lf_rows 0

del_lf_rows_len 0

distinct_keys 999

most_repeated_key 1

btree_space 32,016

used_space 14,899

pct_used 47

rows_per_key 1

blks_gets_per_access 3

lf_rows 999 br_rows 2

lf_blks 3 br_blks 1

lf_rows_len 14,877 br_rows_len 22

lf_blk_len 7,996 br_blk_len 8,028

我们可以发现是3个block，下面我们继续来看看上次的问题，关于排序空间？

SQL> EXPLAIN plan FOR

2 CREATE INDEX idx_ht1 ON ht1(object_id) tablespace roger;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash VALUE: 3209147535

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | CREATE INDEX STATEMENT | | 999 | 2997 | 8 (0)| 00:00:01 |

| 2 | SORT CREATE INDEX | | 999 | 2997 | | |

----------------------------------------------------------------------------------

Note

-----

- estimated INDEX SIZE: 65536 bytes

14 ROWS selected.

SQL> SELECT 2997/1024 FROM dual;

2.92675781

我们可以发现该操作需要排序空间3k大小。但是这个2997到底是如何计算出来的呢？

SQL> SELECT 999*3 FROM dual;

2997

从这样来看，对于单列 INDEX，就是avg_col_len*row_tables

那么对于符合索引呢？其实也很简单，道理一样的，如下：

SQL> EXPLAIN plan FOR

2 CREATE INDEX idx_owner ON ht1(owner,object_id) tablespace roger;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash VALUE: 4167866385

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0 | CREATE INDEX STATEMENT | | 999 | 6993 | 8 (0)| 00:00:01 |

| 2 | SORT CREATE INDEX | | 999 | 6993 | | |

| 3 | TABLE ACCESS FULL | HT1 | 999 | 6993 | 6 (0)| 00:00:01 |

------------------------------------------------------------------------------------

Note

-----

- estimated INDEX SIZE: 65536 bytes

14 ROWS selected.

SQL> SELECT TABLE_NAME,

2 COLUMN_NAME,

3 DATA_LENGTH,

4 AVG_COL_LEN,

5 CHAR_LENGTH,

6 CHAR_USED

7 FROM user_tab_columns

8 WHERE TABLE_NAME = 'HT1'

9 AND column_name IN ('OWNER', 'OBJECT_ID');

TABLE_NAME COLUOracleoracleplus.netMN_NAME DATA_LENGTH AVG_COL_LEN CHAR_LENGTH C

------------- ----------------- ----------- ----------- ----------- -

HT1 OWNER 30 4 30 B

HT1 OBJECT_ID 22 3 0

SQL> SELECT (4+3)*999 FROM dual;

6993

最好再回到上次文章中的为什么是42M？同样很简单，如下：

SQL> SELECT COUNT(*) FROM ht1;

COUNT(*)

----------

5003900

SQL> SELECT TABLE_NAME,column_name,avg_col_len

2 FROM user_tab_columns

3 WHERE TABLE_NAME='HT1';

TABLE_NAME COLUMN_NAME AVG_COL_LEN

------------------------------ ------------------------------ -----------

HT1 OWNER 5

HT1 OBJECT_NAME 24

HT1 SUBOBJECT_NAME 2

HT1 OBJECT_ID 4

HT1 DATA_OBJECT_ID 2

......

HT1 SECONDARY 1

13 ROWS selected.

SQL> SELECT 5003900*(5+4)/1024/1024 FROM dual;

42.9488182

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-1652 详解rowid,index entry header修复办法

weixin_39988331

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
sp2 0067 oracle报错,【案例】Oracle报错ORA-1652 详解rowid,index entry header修复办法

【案例】Oracle报错ORA-1652 详解rowid,index entry header修复办法时间:2016-11-22 21:57来源:Oracle研究中心作者:网络点击:次天萃荷净Oracle研究中心案例分析：运维DBA反映Oracle数据库出现报错ORA-1652，总结该报错的解决办法，详细解rowid,index entry header修复办法。本站文章除注明转...
复制链接

扫一扫