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

最新推荐文章于 2024-06-19 23:59:25 发布

cri5768

最新推荐文章于 2024-06-19 23:59:25 发布

阅读量321

点赞数

文章标签：数据库

一个朋友数据库断电后重启遭遇此问题
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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1580285/，如需转载，请注明出处，否则将追究法律责任。