sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置: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
----------
8

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

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |         |   999 |  2997 |     8   (0)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_HT1 |       |       |            |          |
|   2 |   SORT CREATE INDEX    |         |   999 |  2997 |            |          |
|   3 |    INDEX FAST FULL SCAN| IDX_HT1 |       |       |            |          |
----------------------------------------------------------------------------------

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

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
------------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |           |   999 |  6993 |     8   (0)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IDX_OWNER |       |       |            |          |
|   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修复办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1309.html

Oracle研究中心

关键词:

ORA-1652

Oracle详解rowid

index entry header修复方法