Wednesday, December 7, 2011

ORA-00600 [kkpo_rcinfo_defstg:objnotfound] - deferred segment creation strikes.


11gR2 has a new feature, deferred segment creation. It has through lack of wisdom, been set as default in 11.2.0.2. What it does is defer the creation of a segment until there is actually data to go into it. So if you create a table, you will not see it in dba/user_segments until you add the first row, then the segment gets created.

It is buggy - just check My Oracle Support for nasty bugs that require a database restore to fix.

Here is what I ran into today:


  'alter table XXXXX enable constraint PK_XXXXX exceptions into PK_VIOLATIONS': 
  Error:
  ORA-00600: internal error code, arguments: [kkpo_rcinfo_defstg:objnotfound], [85752], [], [], [], [], [], [], 

There were various references to running the wrong catupgrd.sql script causing it, but that was not my issue. After a bit of hunting matched it to Bug 12840114 which is still open.

I highly recommend disabling this new default behaviour - that is turn off deferred segment creation.

You can do this either:

  • at the database level via your init/spfile setting parameter DEFERRED_SEGMENT_CREATION to FALSE
  • at the session level via "alter session set deferred_segment_creation = false;"
  • at the statement level via "create table t ( x int ) segment creation immediate;"


See

Deferred Segment Creation...
The Tom Kyte Blog: Deferred Segment Creation...
http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html

Oracle related stuff: ORA-14404 / 14405 and Deferred Segment Creation
http://oracle-randolf.blogspot.com/2011/04/ora-14404-14405-and-deferred-segment.html

Alter Table Enable Constraint Fails With ORA-600 [Kkpo_rcinfo_defstg:Objnotfound] [ID 1352678.1]
https://support.oracle.com/CSP/main/article?cmd=show&id=1352678.1&type=NOT

The bug that got me:
Bug 12840114: ORA-600 [KKPO_RCINFO_DEFSTG:OBJNOTFOUND] ENABLING CONSTRAINT EXCEPTIONS INTO..
https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=12840114

No comments:

Post a Comment