ora-600[25027][x][0] 插入大字段问题

重庆八怪 2015-04-16

SQL js url

一个朋友数据库断电后重启遭遇此问题
ORA-00600: internal error code, arguments: [25027], [22], [0], [], [], [], [], [], [], [], [], []
----- Current SQL Statement for this session (sql_id=8ywdkvd1vbrqm) -----
INSERT INTO VEH_XML_DATA (ID, JYLSH, JKID, HPHM, HPZL, CLSBDH, SCCS, ISSC, STATE, XMLDOC, HCSCCS, HCISSC, JYW) VALUES (:B7 , :B6 , '18C62', :B5 , :B4 , :B3 , 0, 0, 1, :B2 , 0, 0, :B1 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1df9aaec0      2716  package body JCLW_DEV.PKG_JCGCSJ
0x1e3b17fb0         1  anonymous block


关于ORA-00600 25027的相关描述
ERROR: 


  Format: ORA-600 [25027] [a] [b]
VERSIONS:
  versions 9.2 and above


ARGUMENTS:
  Arg [a]  Tablespace Number (TSN)
  Arg [b]  Decimal Relative Data Block Address (RDBA)



SUGGESTIONS:
  
 1. If the Arg [b] (the RDBA) is 0 (zero), then this could be due to fake indexes.

  The following query will list fake indexes:

     select do.owner,do.object_name, do.object_type,sysind.flags
     from dba_objects do, sys.ind$ sysind
     where do.object_id = sysind.obj#
     and bitand(sysind.flags,4096)=4096;

  If the above query returns any rows, check the objects involved and consider dropping them as they can cause this error. 

2. Run analyze table validate structure on the table referenced in the Current SQL statement in 
    the related trace file.

另外一个文档说明了如何判断是否是索引有问题还是INDEX有问题

1) SQL> Analyze table validate structure cascade ; 

2) query dba_indexes to get all indexes for failed table 'table_name'

    SQL> select index_name from user_indexes where table_name = 'TABLE_NAME'; 

3) validate table indexes :
    SQL > Analyze index validate structure ; 

If the analyze indicates corruption and that corruption is within an index, drop and recreate the index.


如果是0 文档说应该是索引,但是analyze 所有索引并没有问题。最后发现表中有大字段

可以根据 文档处理
Insert into table with lob fails with ora-600[25027][x][0] where x is ts# for the tablespace that has the lob.
Tracefile shows the stack function similar to:
krtd2abh  kcbgcur  ktspgfblk3  ktsplbfmb  ktsplbrecl  ktspgsp_main  kdlgsp_init  kdl_write1  kdlf_write   koklicbf  koklcre

CAUSE

The cause of this error can be LOST IO which may cause other errors like ORA-600 [kdlpdba:kcbz_objdchk] during INSERT.
The problem described in bug 13869187 is because a Block is marked as Formatted in the ASSM metadata L1 bitmap block but the block is unformatted for the LOB segment.  
The 3rd argument may not be always 0 (zero) as the problem is that if the block is unformatted, Oracle still tries to locate a pdba assuming that the block is formatted and that pdba offset may be zero when the block is empty (affected block has never formatted:block flag contains 1 - KCBHFNEW and type is zero).  If the block is formatted for a former dropped object, then the argument can be different than  zero.
DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY with verify_option=>DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC and 
attrib=>DBMS_SPACE_ADMIN.BITMAPS_CHECK; however it may be canceled when visiting the first problematic block; thus may not identify all affected blocks.
Syntax example of executing the above procedure:
exec DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY('SYS','T_C2_LOB','LOB',null,DBMS_SPACE_ADMIN.SEGMENT_VERIFY_SPECIFIC,DBMS_SPACE_ADMIN.BITMAPS_CHECK)
For more details reference Bug 18607613
SOLUTION
The error is fixed by:
recreating the table using exp-drop-import.
OR
Move the lob in a new tablespace.
Alter table move lob(&lob_column) store as (tablespace &tbsp);

进行处理,但是朋友处理的时候出现ORA-1555错误如下:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

需要按照:
ORA-01555: snapshot too old: rollback segment number with name "" too small
 and sometimes followed by ORA-22924 error.  


ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old
CAUSE


LOB data doesn't use the Undo segment for retaining the read consistent images. The old versions are stored in the LOB Segments itself, before any DMLs.


The ORA-01555 on a LOB segment is reported generally in two cases:


a) The query is accessing a LOB segment which is corrupted


OR


b) Read consistent images of the LOB data is not available in the LOB Segment. This happens due to the wrong setting of PCTVERSION / RETENTION attributes of the LOB segment.


SOLUTION


1) The first step to resolve ORA-1555 on  LOB column is to check for corruption. This is the most common case.


1.a) Create a dummy table for storing all rowids of the corrupted LOBs. Let's call it "corrupt_lobs"


SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number);
1.b) Find the column names containing LOB data. You can DESCRIBE the table encountering the error and note down the columns names with datatype CLOB and BLOB.


SQL> DESC LOBDATA


Name Null? Type 
---------- --------- ------------
ID NOT NULL NUMBER 
DOCUMENT BLOB


1.c) Execute the following PL/SQL block to identify the corrupted rows. Ensure to replace and
阅读(5128) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册

登录 后评论
下一篇
云栖号资讯小编
227人浏览
2020-07-07
相关推荐
[20130217]11GR2 adrci的学习.txt
823人浏览
2013-02-17 16:19:25
一个oracle查询引起的bug
792人浏览
2015-02-27 18:18:25
ora-600[qesmmCValStat4]一例
718人浏览
2017-11-12 15:49:00
0
0
0
1643