Focus on Oracle: September 2009 Archives

解决ORA-06502一例

By cui hua on September 24, 2009 8:30 PM | 2 Comments

昨天把瑞星防火墙升级到2009后，今天就出问题了。

用一会儿后整个windows就死掉了，点"任务管理器"也没有反应。只好硬关机，但重启后症状依旧。

无奈之下尝试卸载瑞星防火墙2009，卸载的过程中死了一次，硬关机重启后再次卸载成功。

但是卸载完后发现本地网卡、无线网卡和蓝牙均不能使用，后发现是瑞星在我的"设备管理器"的"网络适配器"下面针对本地网卡、无线网卡和蓝牙都安装了一个瑞星自己的驱动程序，而且这些驱动程序现在是处在异常状态（前面有黄色惊叹号），更要命的是这些处于异常状态的驱动程序无法卸载。无奈之下卸载掉正常的本地网卡、无线网卡和蓝牙的驱动，卸载完毕后前面有黄色惊叹号的瑞星的那些驱动也随之消失，但始终还有一个针对wan口的瑞星驱动无法卸载。

我知道瑞星2009很烂，所以我的防火墙一直还是2008，但昨天手欠，点了一下升级，恶梦就降临了！

于是心乱如麻的开始了重装的过程。

在重装的过程中，有同事问我问题：说他的一个存储过程，导入一批数据，这一批数据中会往某个表里插入10几条特殊数据，在插入了这10几条记录的过程中写错误日志写了4000多条，而且错误内容全部是"ORA-06502: PL/SQL: numeric or value error"，他问我如何解决上述问题？

这里报错是非常奇怪的，因为oracle报错ORA-06502的时候通常会在报错的最后写错误的具体原因，如：

ORA -06502 : PL/SQL: numeric or value error: character string buffer too small

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

我注意到上述报错后面没有跟具体的错误原因。

我在我整个人的状态很混乱的情况下开始了定位错误的过程，最后被我发现造成上述错误的原因有3点，这3点我就不细说了，我这里只说为什么oracle在报错ORA-06502的时候，后面并没有跟具体的错误原因。

我们先来看一个报错ORA-06502，但是后面会跟具体的错误原因的例子：

SQL> create table t1(id number,name varchar2(10));

Table created

SQL> create or replace procedure P_TEST is

2 vc_name varchar2(10);

3 begin

4 vc_name := '12345678910';

5 execute immediate 'insert into t1(id,name) values(null,:1)' using vc_name;

6

7 commit;

8 end P_TEST;

9 /

Procedure created

SQL> exec p_test;

begin p_test; end;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "IPRA.P_TEST", line 4

ORA-06512: at line 1

我们再来看一个报错ORA-06502，但是后面不跟具体的错误原因的例子：

SQL> create or replace package PCK_TEST is

2 type typ_t1 is record

3 (

4 id t1.id%type,

5 name t1.name%type,

6 n_count number(1)

7 );

8 end PCK_TEST;

9 /

Package created

SQL> create or replace procedure P_TEST is

2 rec_t1 pck_test.typ_t1;

3 begin

4 for i in 1 .. rec_t1.n_count loop

5 execute immediate 'insert into t1(id,name) values(:1,:2)' using rec_t1.id,rec_t1.name;

6 end loop;

7

8 commit;

9 end P_TEST;

10 /

Procedure created

SQL> exec p_test;

begin p_test; end;

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at "IPRA.P_TEST", line 4

ORA-06512: at line 1

这里我们可以看到很清晰的看到，为什么第二次报错ORA-06502的时候并没有跟具体的错误原因----因为这里我们尝试使用rec_t1的n_count，但这里rec_t1并没有初始化。

这就是我那位同事报错ORA-06502的根本原因。

About extent map block

By cui hua on September 22, 2009 4:09 PM | 1 Comment

Kostas Hairopoulos continued to ask me three questions：

1、What's the meaning of an extent map purpose however of an extent map since we have BMB blocks which already cover many extents as you mentioned?

2、Do we have always an extent map for every table or only if we allocate more extents except the initial allocation during table creation?

3、I checked the buffer touch counts for the staging table in my application and I noticed that extent map is not loaded in the buffer cache. I am wondering why

The answers are:

1、Extent map blocks are used where the extent map can no longer fit within the segment header. Extent map blocks are extent maps that overflow when the extent map area in the segment header block is not large enough to describe all extents that are used by a segment. Extent maps are achieved by a linked list of blocks containing extent map portions.

Let's see an example:

adcbth and uplpdt are two tables both reside in ASSM tablespace.

uplpdt have four extent map blocks. However, adcbth desn't have any.

SQL> select count(*) from armshistemp.adcbth;

COUNT(*)

----------

367

SQL> select count(*) from armshistemp.uplpdt;

COUNT(*)

----------

6962716

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARMSHISTEMP' and segment_name='ADCBTH';

HEADER_FILE HEADER_BLOCK

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

112 908

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARMSHISTEMP' and segment_name='UPLPDT';

HEADER_FILE HEADER_BLOCK

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

111 12

BBED> set dba 112,908

DBA 0x1c00038c (469762956 112,908)

BBED> dump

File: /dras14/oradata/astca/armshistemptbs_02.dbf (112)

Block: 908 Offsets: 0 to 511 Dba:0x1c00038c

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

23020000 1c00038c 968702fd 00020104 c6020000 00000000 00000000 00000000

00000000 00000001 00000080 0a9c0000 00000000 00000040 00000080 1c0003c9

00000000 00000000 00000000 0000003c 00000000 00000000 00000000 00000000

00000004 00000080 1c00038d 00000000 00000000 00000000 00000000 1c000389

1c000389 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00010000 00002000 00000000 00001434

00000000 1c00038b 00000001 1c00038a 1c00038b 00000000 00000000 00000000

00000000 00000000 00000001 00000000 00014f15 20000000 1c000389 00000080

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> set dba 111,12

DBA 0x1bc0000c (465567756 111,12)

BBED> dump

File: /dras14/oradata/astca/armshistemptbs_01.dbf (111)

Block: 12 Offsets: 0 to 511 Dba:0x1bc0000c

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

23020000 1bc0000c aa2aee16 00080104 65b90000 00000000 00000000 1ec3ef0a

00000004 00000906 00048300 0a9c0000 00000905 00000080 00000080 1e443009

1ec3ef0a 000001de 00000000 00047ffb 00000000 00000000 00000000 00000905

00000080 00000080 1e443009 1ec3ef0a 000001de 00000000 00048300 1ec42a09

1ec42a09 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00010000 00002000 00000000 00001434

00000000 1bc0000b 00000001 1ec42a09 1bc0000b 00000000 00000000 00000000

00000000 00000000 00000133 1ec3260a 00014f3c 20000000 1bc00009 00000080

1e430009 00000080 1e82fd89 00000080 1ec2fc89 00000080 1f02fb89 00000080

1e430089 00000080 1e82fe09 00000080 1ec2fd09 00000080 1f02fc09 00000080

1e430109 00000080 1e82fe89 00000080 1ec2fd89 00000080 1f02fc89 00000080

1e430189 00000080 1e82ff09 00000080 1ec2fe09 00000080 1f02fd09 00000080

1e430209 00000080 1e82ff89 00000080 1ec2fe89 00000080 1f02fd89 00000080

1e430289 00000080 1e830009 00000080 1ec2ff09 00000080 1f02fe09 00000080

1e430309 00000080 1e830089 00000080 1ec2ff89 00000080 1f02ff09 00000080

<32 bytes per line>

So, for table adcbth, there is no Extent Map Block！because the last EMB's dba and the next EMB's dba are both 0x00000000.

But for table uplpdt，the last EMB's dba is 0x1ec3ef0a and the next EMB's dba is 0x1ec3260a.

Let's see those two EMBs plus another two EMBs：

BBED> set dba 0x1ec3ef0a

DBA 0x1ec3ef0a (516157194 123,257802)

BBED> dump

File: /dras21/astca/armshistemptbs_05.dbf (123)

Block: 257802 Offsets: 0 to 511 Dba:0x1ec3ef0a

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

24020000 1ec3ef0a 968db1d2 00020104 c7300000 000001df 00000000 00014f3c

40000000 1ec3ef09 00000080 1f03ee89 00000080 1e43f409 00000080 1e83f109

00000080 1ec3ef89 00000080 1f03ef09 00000080 1e43f489 00000080 1e83f189

00000080 1ec3f009 00000080 1f03ef89 00000080 1e43f509 00000080 1e83f209

00000080 1ec3f089 00000080 1f03f009 00000080 1e43f589 00000080 1e83f289

00000080 1ec3f109 00000080 1f03f089 00000080 1e43f609 00000080 1e83f309

00000080 1ec3f189 00000080 1f03f109 00000080 1e43f689 00000080 1e83f389

00000080 1ec3f209 00000080 1f03f189 00000080 1e43f709 00000080 1e83f409

00000080 1ec3f289 00000080 1f03f209 00000080 1e43f789 00000080 1e83f489

00000080 1ec3f309 00000080 1f03f289 00000080 1e43f809 00000080 1e83f509

00000080 1ec3f389 00000080 1f03f309 00000080 1e43f889 00000080 1e83f589

00000080 1ec3f409 00000080 1f03f389 00000080 1e43f909 00000080 1e83f609

00000080 1ec3f489 00000080 1f03f409 00000080 1e43f989 00000080 1e83f689

00000080 1ec3f509 00000080 1f03f489 00000080 1e43fa09 00000080 1e83f709

00000080 1ec3f589 00000080 1f03f509 00000080 1e43fa89 00000080 1e83f789

00000080 1ec3f609 00000080 1f03f589 00000080 1e43fb09 00000080 1e83f809

<32 bytes per line>

BBED> set dba 0x1ec3260a

DBA 0x1ec3260a (516105738 123,206346)

BBED> dump

File: /dras21/astca/armshistemptbs_05.dbf (123)

Block: 206346 Offsets: 0 to 511 Dba:0x1ec3260a

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

24020000 1ec3260a 968c90a8 00020104 cf490000 000001fc 1ec3700a 00014f3c

40000000 1ec32609 00000080 1f032589 00000080 1e432b09 00000080 1e832809

00000080 1ec32709 00000080 1f032689 00000080 1e432b89 00000080 1e832889

00000080 1ec32789 00000080 1f032709 00000080 1e432c09 00000080 1e832989

00000080 1ec32809 00000080 1f032789 00000080 1e432c89 00000080 1e832a09

00000080 1ec32889 00000080 1f032809 00000080 1e432d09 00000080 1e832a89

00000080 1ec32909 00000080 1f032889 00000080 1e432d89 00000080 1e832b09

00000080 1ec32989 00000080 1f032909 00000080 1e432e89 00000080 1e832b89

00000080 1ec32a09 00000080 1f032989 00000080 1e432f09 00000080 1e832c09

00000080 1ec32a89 00000080 1f032a09 00000080 1e432f89 00000080 1e832d09

00000080 1ec32b89 00000080 1f032b09 00000080 1e433009 00000080 1e832d89

00000080 1ec32c09 00000080 1f032b89 00000080 1e433109 00000080 1e832e09

00000080 1ec32c89 00000080 1f032c09 00000080 1e433189 00000080 1e832e89

00000080 1ec32d09 00000080 1f032c89 00000080 1e433209 00000080 1e832f09

00000080 1ec32d89 00000080 1f032d09 00000080 1e433289 00000080 1e833009

00000080 1ec32e09 00000080 1f032d89 00000080 1e433309 00000080 1e833089

<32 bytes per line>

BBED> set dba 0x1ec3700a

DBA 0x1ec3700a (516124682 123,225290)

BBED> dump

File: /dras21/astca/armshistemptbs_05.dbf (123)

Block: 225290 Offsets: 0 to 511 Dba:0x1ec3700a

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

24020000 1ec3700a 968cf558 00020104 64b90000 000001fc 1ec3af89 00014f3c

40000000 1ec37009 00000080 1f036f89 00000080 1e437509 00000080 1e837209

00000080 1ec37089 00000080 1f037009 00000080 1e437589 00000080 1e837289

00000080 1ec37109 00000080 1f037089 00000080 1e437609 00000080 1e837309

00000080 1ec37189 00000080 1f037109 00000080 1e437689 00000080 1e837389

00000080 1ec37209 00000080 1f037189 00000080 1e437709 00000080 1e837409

00000080 1ec37289 00000080 1f037209 00000080 1e437789 00000080 1e837489

00000080 1ec37309 00000080 1f037289 00000080 1e437809 00000080 1e837509

00000080 1ec37389 00000080 1f037309 00000080 1e437889 00000080 1e837589

00000080 1ec37409 00000080 1f037389 00000080 1e437909 00000080 1e837609

00000080 1ec37489 00000080 1f037409 00000080 1e437989 00000080 1e837689

00000080 1ec37509 00000080 1f037489 00000080 1e437a09 00000080 1e837709

00000080 1ec37589 00000080 1f037509 00000080 1e437a89 00000080 1e837789

00000080 1ec37609 00000080 1f037589 00000080 1e437b09 00000080 1e837809

00000080 1ec37689 00000080 1f037609 00000080 1e437b89 00000080 1e837889

00000080 1ec37709 00000080 1f037689 00000080 1e437c09 00000080 1e837909

<32 bytes per line>

BBED> set dba 0x1ec3af89

DBA 0x1ec3af89 (516140937 123,241545)

BBED> dump

File: /dras21/astca/armshistemptbs_05.dbf (123)

Block: 241545 Offsets: 0 to 511 Dba:0x1ec3af89

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

24020000 1ec3af89 968d5057 00020104 a6220000 000001fc 1ec3ef0a 00014f3c

40000000 1ec3af89 00000080 1f03af09 00000080 1e43b489 00000080 1e83b189

00000080 1ec3b009 00000080 1f03af89 00000080 1e43b509 00000080 1e83b209

00000080 1ec3b089 00000080 1f03b009 00000080 1e43b589 00000080 1e83b289

00000080 1ec3b109 00000080 1f03b089 00000080 1e43b609 00000080 1e83b309

00000080 1ec3b189 00000080 1f03b109 00000080 1e43b689 00000080 1e83b389

00000080 1ec3b209 00000080 1f03b189 00000080 1e43b709 00000080 1e83b409

00000080 1ec3b289 00000080 1f03b209 00000080 1e43b789 00000080 1e83b489

00000080 1ec3b309 00000080 1f03b289 00000080 1e43b809 00000080 1e83b509

00000080 1ec3b389 00000080 1f03b309 00000080 1e43b889 00000080 1e83b589

00000080 1ec3b409 00000080 1f03b389 00000080 1e43b909 00000080 1e83b609

00000080 1ec3b489 00000080 1f03b409 00000080 1e43b989 00000080 1e83b689

00000080 1ec3b509 00000080 1f03b489 00000080 1e43ba09 00000080 1e83b709

00000080 1ec3b589 00000080 1f03b509 00000080 1e43ba89 00000080 1e83b789

00000080 1ec3b609 00000080 1f03b589 00000080 1e43bb09 00000080 1e83b809

00000080 1ec3b689 00000080 1f03b609 00000080 1e43bb89 00000080 1e83b889

<32 bytes per line>

The first byte of those blocks are 0x24, means Pagetable extent map block.

From the dump contents of above four blocks, we can clearly see that those EMB are linked together.

If adcbth and uplpdt are reside in MSSM tablespace, then the first byte of those EMB blocks are 0x12，and you can track those EMB in MSSM by using ktemh struct in segment header block.

2、No. we don't always have EMB for every table. Extent map blocks are created where the extent map can no longer fit within the segment header.

3、You are right，EMB blocks are not loaded into the buffer cache nor dictionary cache, but I don't know why.

我所有文章的索引

By cui hua on September 19, 2009 6:41 PM | No Comments

如下是所有我写过的文章的索引，并且以后我会同步更新，以方便朋友们查找。

如果以后要查看我写过的文章，只需要点击我blog右上方Categories栏的"article index"就能看到包含我所有文章的索引。

所有的文章已分类，并且在每个类里面基本上是按照时间顺序从上到下排列，新写的文章排在后写文章的上面。

Oracle experiences：

·Internal

Some lob internal

Lob的字符集和字节序

如何修改核心bootstrap$对象

9i下Move系统表DEPENDENCY$导致索引失效的恢复（续3）

9i下Move系统表DEPENDENCY$导致索引失效的恢复（续2）

9i下Move系统表DEPENDENCY$导致索引失效的恢复（续）

9i下Move系统表DEPENDENCY$导致索引失效的恢复

ASM里的条带化和extent大小

Insert操作会导致ITL等待吗

truncate或者drop后一定可以恢复数据吗

ASM里如何确定一个datafile的物理存储位置

ASM里如何修改指定的Block

ACOUG已经完全具备恢复ASM故障的能力

如何跳过缺失的归档

详细解析system回滚段损坏导致的ora-600[4193]错误

普通B树索引跟IOT的一点差异

Oracle中如何dump内存中的块

关于外键列上的索引

ASSM和MSSM下block结构的一点差异

简单描述一下buffer cache管理的过程

详细解析ASSM的Segment Header的结构

oracle中如何分析索引的结构

详细解析truncate引发的object checkpoint

如何通过修改数据字典来恢复被truncate的数据

记一次current online redo log缺失后的恢复

log switch checkpoint

详细解析oracle中的transaction

关于Fast Block Cleanout

详细解析datafile的status

如何找到你要修改的数据字典的物理存储位置（续）

如何找到你要修改的数据字典的物理存储位置

一个通过BBED强制恢复offline状态的tablespace的例子

关于shadow block

一个通过BBED强制恢复offline状态的datafile的例子

关于SCN

About extent map block

详细解析LMT的datafile的物理结构

详细解析9i和10g的datafile header

怎样找到指定键值的 index block

怎样在windows上用DD配合ultraEdit修改数据

利用BBED修改block内数据的一个例子

利用BBED修改checksum值的过程

利用dd修改checksum值的过程

·Trouble shooting：

记对一次truncate操作的诊断过程

利用猜测定位问题的一个实例

一次library cache pin故障的解决过程

解决undo global data等待一例

解决权限不足错误一例

解决历史数据转移权限错误一例

解决Multibyte character error一例（续2）

解决Multibyte character error一例（续）

解决Multibyte character error一例

一个诡异问题的分析过程

·Performance Tuning：

如何在长文本上建索引

·Bug：

10.2.0.4.0里请慎用压缩表

9iR2 RAC环境下请慎重刷buffer cache

·CBO：

size auto与histogram的关系

对index直接rebuild的时候一定会是index fast full scan吗

当无统计信息的时候ix_sel的值取决于什么

·Development：

oracle里如何调用db2 v9上的存储过程

关于AIX上proc的两个问题的解答

如何在oracle里以binary的ftp方式传送文件

如何在oracle里实现zip压缩

cursor和绑定变量配合使用的标准模板

关于PL/SQL中对存储过程add debug information

自动产生不同数据库下指定schema间DDL同步脚本的存储过程

·Other：

mount状态下如何dump一个block

用ODU恢复数据的一点注意事项

oracle如何入门

backupset和backuppiece的区别

如何确定被多次truncate后的data object id

对373472.1中脚本的一点解释

Stored Outline在不同版本之间的一点差异

v$sort_usage和v$tempseg_usage的区别

关于 distributed transaction

关于 parallel rebuild index

关于 AWR

我以前写的关于Data Guard的两篇文章

在有连接池的情况下如何定位 session

关于10g的automatic statistics collection

如何通过存储过程名得到 session id

如何恢复一个被误drop的存储过程

2008年我写的一些文章

db2 experiences：

Metalink上也许会有我的一篇文章

如何在oracle里使用java存储过程连接db2

Other experiences：

My life：

About me：

回答一下关于BBED的两个问题

By cui hua on September 18, 2009 5:19 PM | No Comments

今天有朋友问了我两个关于BBED的问题，我这里回答一下。

1、BBED不能够正常make，报如下错误：

ld: 0706-006 Cannot find or open library file: -l m

ld:open(): A file or directory in the path name does not exist.

ld: 0706-006 Cannot find or open library file: -l m

ld:open(): A file or directory in the path name does not exist.

ld: 0706-006 Cannot find or open library file: -l m

ld:open(): A file or directory in the path name does not exist.

make: 1254-004 The error code from the last command is 255.

为什么会这样？

答：这是因为缺少bos.adt.libm，如下所示：

$ lslpp -l | grep bos.adt

bos.adt.base 5.3.0.51 COMMITTED Base Application Development

bos.adt.include 5.3.0.51 COMMITTED Base Application Development

bos.adt.lib 5.3.0.50 COMMITTED Base Application Development

正常情况下应该这样：

$ lslpp -l | grep bos.adt

bos.adt.base 5.3.0.51 APPLIED Base Application Development

bos.adt.debug 5.3.0.50 COMMITTED Base Application Development

bos.adt.graphics 5.3.0.50 COMMITTED Base Application Development

bos.adt.include 5.3.0.51 APPLIED Base Application Development

bos.adt.insttools 5.3.0.50 COMMITTED Tool to Create installp

bos.adt.lib 5.3.0.50 COMMITTED Base Application Development

bos.adt.libm 5.3.0.40 COMMITTED Base Application Development

bos.adt.libmio 5.3.0.0 COMMITTED Modular IO Library

bos.adt.prof 5.3.0.53 COMMITTED Base Profiling Support

bos.adt.prt_tools 5.3.0.0 COMMITTED Printer Support Development

bos.adt.samples 5.3.0.40 COMMITTED Base Operating System Samples

bos.adt.sccs 5.3.0.50 COMMITTED SCCS Application Development

bos.adt.syscalls 5.3.0.50 COMMITTED System Calls Application

bos.adt.utils 5.3.0.50 COMMITTED Base Application Development

bos.adt.data 5.3.0.0 COMMITTED Base Application Development

2、BBED里怎样在不看trace文件的情况下知道某一行的offset，或者说定位到这一行？

答：有两种方法，我们来举一个实例：

SQL> select owner,object_name,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from sys.testlmttb where rownum<6;

OWNER OBJECT_NAME LOCATION

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

SYS /1005bd30_LnkdConstant 138_13

SYS /10076b23_OraCustomDatumClosur 138_13

SYS /10297c91_SAXAttrList 138_13

SYS /103a2e73_DefaultEditorKitEndP 138_13

SYS /1048734f_DefaultFolder 138_13

现在我要在不看dump文件的前提下通过BBED找到上述select结果中的那条蓝色的记录，这条蓝色的记录是第4行。

方法1、直接find：

SQL> select dump('/103a2e73_DefaultEditorKitEndP',16) from dual;

DUMP('/103A2E73_DEFAULTEDITORK

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

Typ=96 Len=30: 2f,31,30,33,61,32,65,37,33,5f,44,65,66,61,75,6c,74,45,64,69,74,6f,72,4b,69,74,45,6e,64,50

所以这里我们直接find字符串2f31303361326537335f44656661756c74456469746f724b6974456e6450：

BBED> set dba 138,13

DBA 0x2280000d (578813965 138,13)

BBED> find /x 2f31303361326537335f44656661756c74456469746f724b6974456e6450 TOP

File: /dras20/astca/testlmt01.dbf (138)

Block: 13 Offsets: 7798 to 8191 Dba:0x2280000d

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

2f313033 61326537 335f4465 6661756c 74456469 746f724b 6974456e 6450ff04

c3022a64 ff0a4a41 56412043 4c415353 07786a06 13043221 07786a06 13043221

13323030 362d3036 2d31393a 30333a34 393a3332 0556414c 4944014e 014e014e

2c000d03 53595315 2f313032 39376339 315f5341 58417474 724c6973 74ff04c3

032233ff 0a4a4156 4120434c 41535307 786a0613 04331c07 786a0613 04342f13

32303036 2d30362d 31393a30 333a3530 3a323705 56414c49 44014e01 4e014e2c

000d0353 59531e2f 31303037 36623233 5f4f7261 43757374 6f6d4461 74756d43

6c6f7375 72ff03c2 4c24ff0a 4a415641 20434c41 53530778 6a061304 320a0778

6a061304 320a1332 3030362d 30362d31 393a3033 3a34393a 30390556 414c4944

014e014e 014e2c00 0d035359 53162f31 30303562 6433305f 4c6e6b64 436f6e73

74616e74 ff04c302 500eff0a 4a415641 20434c41 53530778 6a061304 322e0778

6a061304 322e1332 3030362d 30362d31 393a3033 3a34393a 34350556 414c4944

014e014e 014ec78d 0601

<32 bytes per line>

这里的offset是7798，很容易计算出这一行的行头的offset是7790。

方法2、用kdbr结构定位offset：

上述这条蓝色的记录是第4行，所以这条记录在这个block里的row directory对应的就是kdbr[3]：

BBED> set offset *kdbr[3]

OFFSET 7790

BBED> dump

File: /dras20/astca/testlmt01.dbf (138)

Block: 13 Offsets: 7790 to 8191 Dba:0x2280000d

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

2c000d03 5359531e 2f313033 61326537 335f4465 6661756c 74456469 746f724b

6974456e 6450ff04 c3022a64 ff0a4a41 56412043 4c415353 07786a06 13043221

07786a06 13043221 13323030 362d3036 2d31393a 30333a34 393a3332 0556414c

4944014e 014e014e 2c000d03 53595315 2f313032 39376339 315f5341 58417474

724c6973 74ff04c3 032233ff 0a4a4156 4120434c 41535307 786a0613 04331c07

786a0613 04342f13 32303036 2d30362d 31393a30 333a3530 3a323705 56414c49

44014e01 4e014e2c 000d0353 59531e2f 31303037 36623233 5f4f7261 43757374

6f6d4461 74756d43 6c6f7375 72ff03c2 4c24ff0a 4a415641 20434c41 53530778

6a061304 320a0778 6a061304 320a1332 3030362d 30362d31 393a3033 3a34393a

30390556 414c4944 014e014e 014e2c00 0d035359 53162f31 30303562 6433305f

4c6e6b64 436f6e73 74616e74 ff04c302 500eff0a 4a415641 20434c41 53530778

6a061304 322e0778 6a061304 322e1332 3030362d 30362d31 393a3033 3a34393a

34350556 414c4944 014e014e 014ec78d 0601

<32 bytes per line>

BBED> x /r

rowdata[6681] @7790

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

flag@7790: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@7791: 0x00

cols@7792: 13

col 0[3] @7793: 0x53 0x59 0x53

col 1[30] @7797: 0x2f 0x31 0x30 0x33 0x61 0x32 0x65 0x37 0x33 0x5f

0x44 0x65 0x66 0x61 0x75 0x6c 0x74 0x45 0x64 0x69 0x74 0x6f 0x72

0x4b 0x69 0x74 0x45 0x6e 0x64 0x50

col 2[0] @7828: *NULL*

col 3[4] @7829: 0xc3 0x02 0x2a 0x64

col 4[0] @7834: *NULL*

col 5[10] @7835: 0x4a 0x41 0x56 0x41 0x20 0x43 0x4c 0x41 0x53 0x53

col 6[7] @7846: 0x78 0x6a 0x06 0x13 0x04 0x32 0x21

col 7[7] @7854: 0x78 0x6a 0x06 0x13 0x04 0x32 0x21

col 8[19] @7862: 0x32 0x30 0x30 0x36 0x2d 0x30 0x36 0x2d 0x31 0x39

0x3a 0x30 0x33 0x3a 0x34 0x39 0x3a 0x33 0x32

col 9[5] @7882: 0x56 0x41 0x4c 0x49 0x44

col 10[1] @7888: 0x4e

col 11[1] @7890: 0x4e

col 12[1] @7892: 0x4e

从结果里我们可以看到，上述行的行头的offset确实是7790。

我发现好像有不少朋友都对BBED很感兴趣，其实我觉得BBED真没什么，就是一个工具而已，用ultraEdit+dd往往可以达到同样的目的（当然，会麻烦一些）。

用好BBED的关键就是要了解Block的结构。

朋友们，能从上面的结果中看出oracle中的null在block里会存成什么吗？

回答一下Kostas的两个问题

By cui hua on September 15, 2009 11:18 PM | No Comments

Kostas Hairopoulos在"HHWM下为什么会出现未格式化的块"这篇文章里给我提了两个问题，我也作了回答。

但感觉还是回答的不够清晰，不知道Kostas明白没有，我这里还是直白的回答一下吧。

1、Is the L3 bitmap block at the beginning within the segment header?

Answer: No. But the segment header can be viewed as the first L3 BMB present in an ASSM segment. In ASSM segments, BMBs (including L1、L2 and L3) are placed ahead of the segment header for the first extent (extent 0). If BMBs are required for subsequent extents, they exist as the first blocks in the extent.

2、Where is the extent map header located?

Answer: You can track the extent map block by using ktemh struct in segment header block. Extent map blocks are extent maps that overflow when the extent map area in the segment header block is not large enough to describe all extents that are used by a segment. Entries in an extent map block have the same format as the extent map in the segment header.

An extent map block is always allocated as the first block in the first extent in FLM segments. Each extent map block is pointed to by the previous extent map block. The head of the linked list is in the segment header block.

The concept is the same in either FLM segments or ASSM segments. With ASSM segments, the extent map block follows the associated bitmap blocks for that extent

Oracle里段头的类型

By cui hua on September 15, 2009 2:52 PM | No Comments

首先什么是段头？

The segment header is the header block of the first extent of a segment. In Free List Managed (FLM) segments the header block is always the first block of a segment. In Automatic Segment Space Managed (ASSM) segments, the bitmap blocks always appear before the header block within the first extent of the segment.

The segment header contains information on the extents allocated to the segment and free space within the segment.

接着我们来看oracle里段头的具体类型：

0x01 undo segment header

0x0b data file header

0x0c data segment header with FLG blocks

0x0e unlimited undo segment header

0x0f unlimited save undo segment header

0x10 unlimited data segment header

0x11 unlimited data segment header with FLG blocks

0x12 extent map block

0x17 bitmapped segment header

0x1d bitmapped file space header

0x20 first level bitmap block

0x21 second level bitmap block

0x22 third level bitmap block

0x23 Pagetable segment header block

0x24 Pagetable extent map block

0x25 System Managed Undo Extent Map Block

然后我们来看一个ASSM的实例：

SQL> create tablespace testlmt datafile '/dras20/astca/testlmt01.dbf' size 10M extent management local uniform size 1m segment space management auto;

Tablespace created

SQL> create table testlmttb tablespace testlmt as select * from dba_objects;

Table created

SQL> select extent_id,file_id,block_id,bytes from dba_extents where segment_name='TESTLMTTB';

EXTENT_ID FILE_ID BLOCK_ID BYTES

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

0 138 9 1048576

1 138 137 1048576

2 138 265 1048576

3 138 393 1048576

1、datafile header，即段头类型为0x0b

BBED> set file 138

FILE# 138

BBED> set block 1

BLOCK# 1

BBED> dump

File: /dras20/astca/testlmt01.dbf (138)

Block: 1 Offsets: 0 to 511 Dba:0x22800001

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

0b020000 22800001 00000000 00000104 4bbb0000 09200000 08000000 df7fd2dd

41535443 41000000 000102df 00000500 00002000 008a0003 00000000 00000000

......省略显示部分内容

00000000 00000000 00000000 00000059 00075445 53544c4d 54000000 00000000

<32 bytes per line>

2、bitmapped file space header，即段头类型为0x1d

BBED> set block 2

BLOCK# 2

BBED> dump

File: /dras20/astca/testlmt01.dbf (138)

Block: 2 Offsets: 0 to 511 Dba:0x22800002

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

1d020000 22800002 aa35c791 00080204 8a370000 0000008a 00000080 00000500

00000001 00000000 00000000 00000007 00000488 00000004 00000005 00000000

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

3、ASSM里的first level bitmap block，即L1 BMB，段头类型为0x20

BBED> set block 9

BLOCK# 9

BBED> dump

File: /dras20/astca/testlmt01.dbf (138)

Block: 9 Offsets: 0 to 511 Dba:0x22800009

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

20020000 22800009 aa35c793 00080304 8ef60000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

这里Block 137、138、265、266、393、394也是L1 BMB。

4、ASSM里的second level bitmap block，即L2 BMB，段头类型为0x21

BBED> set block 11

BLOCK# 11

BBED> dump

File: /dras20/astca/testlmt01.dbf (138)

Block: 11 Offsets: 0 to 511 Dba:0x2280000b

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

21020000 2280000b aa35c793 00080804 a63a0000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

5、ASSM里的Pagetable segment header block，即大家从dba_segments里select出来的ASSM下的"段头"，段头类型为0x23

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='TESTLMTTB';

HEADER_FILE HEADER_BLOCK

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

138 12

BBED> set block 12

BLOCK# 12

BBED> dump

File: /dras20/astca/testlmt01.dbf (138)

Block: 12 Offsets: 0 to 511 Dba:0x2280000c

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

23020000 2280000c aa35c797 00080104 c59e0000 00000000 00000000 00000000

00000000 00000004 00000200 0a9c0000 00000003 0000004b 00000080 228001d4

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

最后我们来看一个MSSM的实例：

因为是9i，所以system表空间就是MSSM。

6、unlimited undo segment header，其实就是系统回滚段的段头，段头类型为0x0e

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='SYSTEM';

HEADER_FILE HEADER_BLOCK

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

1 9

BBED> set file 1

FILE# 1

BBED> set block 9

BLOCK# 9

BBED> dump

File: /dras11/oradata/astca/system01.dbf (1)

Block: 9 Offsets: 0 to 511 Dba:0x00400009

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

0e020000 00400009 aa340432 00080104 cc3f0000 00000000 00000000 00000000

00000000 00000006 0000002f 10200000 00000001 00000006 00000008 00400017

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

7、MSSM里的unlimited data segment header，即大家从dba_segments里select出来的MSSM下的"段头"，段头类型为0x10

SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where segment_name='OBJ$';

HEADER_FILE HEADER_BLOCK

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

1 121

BBED> set file 1

FILE# 1

BBED> set block 121

BLOCK# 121

BBED> map /v

File: /dras11/oradata/astca/system01.dbf (1)

Block: 121 Dba:0x00400079

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

Unlimited Data Segment Header

struct kcbh, 20 bytes @0

ub1 type_kcbh @0

......省略显示部分内容

ub2 spare3_kcbh @18

struct ktech, 72 bytes @20

ub4 spare1_ktech @20

......省略显示部分内容

ub4 flag_ktech @88

struct ktemh, 16 bytes @92

ub4 count_ktemh @92

ub4 next_ktemh @96

ub4 obj_ktemh @100

ub4 flag_ktemh @104

struct ktetb[19], 152 bytes @108

ub4 ktetbdba @108

ub4 ktetbnbk @112

......省略显示部分内容

ub4 tailchk @8188

BBED> set offset 96

OFFSET 96

BBED> dump

File: /dras11/oradata/astca/system01.dbf (1)

Block: 121 Offsets: 96 to 607 Dba:0x00400079

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

00000000 00000012 40000000 0040007a 00000007 00401169 00000008 004011f9

00000008 00401309 00000008 00401661 00000008 00402049 00000008 00403191

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> set offset 0

OFFSET 0

BBED> dump

File: /dras11/oradata/astca/system01.dbf (1)

Block: 121 Offsets: 0 to 511 Dba:0x00400079

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

10020000 00400079 aa2de25f 00080104 e69d0000 00000000 00000000 00000000

00000000 00000013 000001ff 10200000 00000012 00000014 00000080 0040d91d

......省略显示部分内容

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

Kostas Hairopoulos曾经问我如何找到extent map header，我理解他的意思是问我如何找到extent map block，朋友们能从我上文中找到定位extent map header的方法吗？

一个自己锁自己的例子

By cui hua on September 9, 2009 2:55 PM | 1 Comment

有朋友问我："一个transaction会自我死锁吗？也就是自己锁死了自己"。

很凑巧，半个月前我刚好帮同事处理过这种自我死锁的情况。

我们这里来构造一个自我死锁的例子：

SQL> select sid from v$mystat where rownum<2;

SID

----------

362

SQL> create table t1 (id varchar2(10),amount number(10));

Table created

SQL> insert into t1 values('cuihua',100);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from t1;

ID AMOUNT

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

cuihua 100

SQL> create procedure p_autonomous is

2 PRAGMA AUTONOMOUS_TRANSACTION;

3 begin

4 update t1 set amount=102 where id='cuihua';

5 commit;

6 end;

7 /

Procedure created

SQL> create procedure p_test is

2 begin

3 update t1 set amount=101 where id='cuihua';

4 p_autonomous;

5

6 commit;

7 end;

8 /

Procedure created

现在只要我执行上述存储过程p_test，就会产生自我死锁，如下所示：

SQL> exec p_test;

begin p_test; end;

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "IPRA.P_AUTONOMOUS", line 4

ORA-06512: at "IPRA.P_TEST", line 4

ORA-06512: at line 1

此时alert log里会显示：

ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/ipra/udump/ipra_ora_921828.trc.

从上述trace文件里我们可以看到：

Deadlock graph:

---------Blocker(s)--------