ORA-08103物件不再存在問題分析

SQL> create table A_D_BRXXX_XXX_XXX_1125 as select * from .A_D_BRXXX_XXX_XXX;

ORA-08103: object no longer exists

Error: ORA 8103 
Text: object no longer exists 
-------------------------------------------------------------------------------
Cause: The object has been deleted by another user since the operation began.
Action: Remove references to the object.

Description

ORA-8103 is reporting that a SQL statement found a block that no longer belongs to the object referenced in the statement.  

Cause

ORA-8103 is caused by an invalid block type. The block header has an invalid block type or the block type inside the block is not expected; e.g. a data block (Type=6) was expected but the actual block information is not a data block (Type!=6).
ORA-8103 is also caused by an unexpected data_object_id where it is changing for the involved objects while the affected SQL statement is executed.

以下部分 10046 日誌擷取：

CLOSE #4574899200:c=3,e=4,dep=1,type=0,tim=47159071133647

=====================

PARSING IN CURSOR #4574893000 len=83 dep=0 uid=0 oct=1 lid=0 tim=47159071134928 hv=506090881 ad='7000000a7827840' sqlid='2918v7hg2npc1'

  create table  OOO .A_D_BRXXX_XXX_XXX_1126 as select * from  OOO .A_D_BRXXX_XXX_XXX    <<<< 執行的 CTAS 語句

END OF STMT

PARSE #4574893000:c=2530,e=4075,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=1,plh=2393565612,tim=47159071134928

BINDS #4574047816:

。。。。。 <<<< 省略

。。。。。

DDE Action 'DB_STRUCTURE_INTEGRITY_CHECK' was flood controlled

----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (FLOOD CONTROLLED, 1 csec) -----

Executing ASYNC actions

----- END DDE Actions Dump (total 0 csec) -----

=====================

PARSING IN CURSOR #4574654208 len=67 dep=1 uid=0 oct=3 lid=0 tim=47159074207269 hv=785625969 ad='7000000a789fd58' sqlid='at1ygf4rd7cvj'

select file#, block#, blocks from   seg$ where type# = 3 and ts# = :1     <<<< 透過 seg$ 查詢 type#=3 的檔案和塊號， type#=3 是  TEMPORARY  ，這裡指的是臨時段

END OF STMT

PARSE #4574654208:c=53,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1764637915,tim=47159074207269

BINDS #4574654208:

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=110af7520  bln=22  avl=02  flg=05

   value=6    <<<< 繫結變數 ts# 的值，這裡是 6,6 號表空間正是 OOO 使用者所在的表空間

EXEC #4574654208:c=63,e=102,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1764637915,tim=47159074207506

FETCH #4574654208:c=1793,e=2805,p=0,cr=312,cu=0,mis=0,r=1,dep=1,og=4,plh=1764637915,tim=47159074212837

FETCH #4574654208:c=25453,e=53743,p=0,cr=4792,cu=0,mis=0,r=1,dep=1,og=4,plh=1764637915,tim=47159074266671

WAIT #4574893000: nam='db file sequential read' ela= 28 file#=678 block#=116881 blocks=1 obj#=-1 tim=47159074266832   <<<< 最後一個讀取的塊是 678 號檔案的 116881 號塊

EXEC #4574893000:c=1236647,e=3132010,p=8505,cr=81566,cu=13568,mis=0,r=0,dep=0,og=1,plh=2393565612,tim=47159074266999   <<<< 執行

ERROR  #4574893000: err=8103  tim=47159074267016    <<< 丟擲了 8103 的錯誤

STAT #4574893000 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD AS SELECT  (cr=0 pr=0 pw=0 time=37 us)'

STAT #4574893000 id=2 cnt=376158 pid=1 pos=1 obj=0 op='PARTITION RANGE ALL PARTITION: 1 1945 (cr=9786 pr=8504 pw=0 time=387949 us cost=22628 size=453306100 card=2887300)'

STAT #4574893000 id=3 cnt=376158 pid=2 pos=1 obj=4312763 op='TABLE ACCESS FULL A_D_BRXXX_XXX_XXX PARTITION: 1 1945 (cr=9786 pr=8504 pw=0 time=274483 us cost=22628 size=453306100 card=2887300)'

STAT #4574654208 id=1 cnt=2 pid=0 pos=1 obj=14 op='TABLE ACCESS FULL SEG$ (cr=5104 pr=0 pw=0 time=2790 us cost=989 size=20 card=1)'

CLOSE # 4574654208:c=26,e=42,dep=1,type=0,tim=47159074267249   <<<< 報錯之後結束執行

WAIT #4574893000: nam='log file sync' ela= 60983 buffer#=4081 sync scn=2662421508 p3=0 obj#=4314004 tim=47159074331972

WAIT #4574893000: nam='SQL*Net break/reset to client' ela= 11 driver id=1650815232 break?=1 p3=0 obj#=4314004 tim=47159074336018

WAIT #4574893000: nam='SQL*Net break/reset to client' ela= 10448 driver id=1650815232 break?=0 p3=0 obj#=4314004 tim=47159074346509

WAIT #4574893000: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=4314004 tim=47159074346570

create tablespace cwdtest datafile '+DATA' size 1G autoextend on;

TABLESPACE_NAME                    SUM_MB    FREE_MB USE_PRECENT

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

CWDTEST                              1024       1023          .1

SQL> insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapranges) values (86,2291642,3,53,8,1,1,2147483645,128,0,46,8,0,0,0,118552,DECODE(4194561,0,NULL,4194561),0,2147483645);        

1 row created.

SQL> commit;

Commit complete.

SQL> select file#, block#, blocks from SYS.seg$ where type# = 3;

     FILE#     BLOCK#     BLOCKS

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

          86    2291642          8

SQL> create table s (n number,c varchar2(4000)) nologging tablespace cwdtest;

Table created.

SQL> BEGIN

  2  FOR I IN 1 .. 100000000

LOOP

EXECUTE IMMEDIATE 'alter table s allocate extent(size 1048576 datafile ''+DATA/cspadg/datafile/cwdtest.409.961946541'')';

END LOOP;

END;  3    4    5    6  

  7  /