Re: recompile causes an ORA-21700: object does not exist or is marked for delete

EXEC #4:c=15625,e=4649,p=0,cr=129,cu=0,mis=0,r=0,dep=1,og=1,tim=58560626455 ERROR #4:err=21700 tim=5856214
EXEC #2:c=31250,e=32025,p=0,cr=892,cu=58,mis=0,r=0,dep=0,og=1, tim=58560626535
ERROR #2:err=21700 tim=5856214

It looks like the error is first raised in cursor #4 - which is this one:

PARSING IN CURSOR #4 len=44 dep=1 uid=215 oct=78 lid=215 tim=58560621788 hv=0 ad='f34f7b0'
drop type "FON".SYS_PLSQL_133027_131_2 force

And is then propagated up to

PARSING IN CURSOR #2 len=52 dep=0 uid=45 oct=95 lid=45 tim=58560594388 hv=945101829 ad='d0e122d8'
  ALTER PACKAGE FON.PCK_REP_FORMULARIOS_UIF COMPILE So I would have a look at the types that are defined in the package header.

Since you're mentioning a corruption of some sort in your earlier email - it's hard to suggest any concrete action plan. But this should provide you a starting point.

Stefan

On Tue, Mar 24, 2015 at 8:02 PM, Juan Carlos Reyes Pacheco < jcdrpllist_at_gmail.com> wrote:

> The full trace :)
>
> 2015-03-24 8:59 GMT-04:00 Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
> :
>
> Thank you Stefan,
>>
>>
>> I executed select order#,columns,types from access$ where d_obj#=148209;
>> and is ok.
>> the last in the trace is:
>>
>> =====================
>> PARSING IN CURSOR #5 len=56 dep=2 uid=0 oct=3 lid=0 tim=58560626276
>> hv=3993603298 ad='dbafcf88'
>> select order#,columns,types from access$ where d_obj#=:1
>> END OF STMT
>> PARSE #5:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=58560626274
>> BINDS #5:
>> kkscoacd
>> 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=114a6160 bln=22 avl=04 flg=05
>> value=148209
>> EXEC #5:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=58560626335
>> FETCH #5:c=0,e=20,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=58560626364
>> FETCH #5:c=0,e=10,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=58560626384
>> STAT #5 id=1 cnt=1 pid=0 pos=1 obj=97 op='TABLE ACCESS BY INDEX ROWID
>> ACCESS$ (cr=4 pr=0 pw=0 time=30 us)'
>> STAT #5 id=2 cnt=1 pid=1 pos=1 obj=129 op='INDEX RANGE SCAN I_ACCESS1
>> (cr=3 pr=0 pw=0 time=18 us)'
>> EXEC
>> #4:c=15625,e=4649,p=0,cr=129,cu=0,mis=0,r=0,dep=1,og=1,tim=58560626455
>> ERROR #4:err=21700 tim=5856214
>> EXEC
>> #2:c=31250,e=32025,p=0,cr=892,cu=58,mis=0,r=0,dep=0,og=1,tim=58560626535
>> ERROR #2:err=21700 tim=5856214
>> STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT UNIQUE (cr=724 pr=0 pw=0
>> time=4872 us)'
>> STAT #3 id=2 cnt=7 pid=1 pos=1 obj=18 op='TABLE ACCESS FULL OBJ$ (cr=724
>> pr=0 pw=0 time=4831 us)'
>> WAIT #2: nam='SQL*Net break/reset to client' ela= 5 driver id=1413697536
>> break?=1 p3=0 obj#=-1 tim=58560626772
>> WAIT #2: nam='SQL*Net break/reset to client' ela= 466 driver
>> id=1413697536 break?=0 p3=0 obj#=-1 tim=58560627252
>> WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536
>> #bytes=1 p3=0 obj#=-1 tim=58560627269
>>
>>
>> 2015-03-24 6:09 GMT-04:00 Stefan Knecht <knecht.stefan_at_gmail.com>:
>>
>> Have you tried to enable 10046 trace and check where exactly the error is
>>> raised ? It may shed some light on what happens underneath the covers.
>>>
>>> Stefan
>>>
>>>
>>> On Tue, Mar 24, 2015 at 4:08 AM, Juan Carlos Reyes Pacheco <
>>> jcdrpllist_at_gmail.com> wrote:
>>>
>>>> Thank you Mark, but no, there is the specification and the body, I
>>>> dropped the body and recreated, and same problem. The problem is in the
>>>> specification.
>>>>
>>>> It is a mysterious mystery.
>>>>
>>>> 2015-03-23 15:37 GMT-04:00 Powell, Mark <mark.powell2_at_hp.com>:
>>>>
>>>> Andrew, I do not remember the previous entries in the thread but any
>>>>> change you have a package body without a specification. I am pretty sure I
>>>>> have seen this once or twice. If so, drop the package body only: drop
>>>>> package body owner.package_name.
>>>>>
>>>>>
>>>>>
>>>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Juan Carlos Reyes
>>>>> Pacheco
>>>>> *Sent:* Monday, March 23, 2015 2:49 PM
>>>>> *To:* Andrew Kerber
>>>>> *Cc:* dmarc-noreply_at_freelists.org; ORACLE-L
>>>>> *Subject:* Re: recompile causes an ORA-21700: object does not exist
>>>>> or is marked for delete
>>>>>
>>>>>
>>>>>
>>>>> Hello Andrew, I reproduced the error and don't work.
>>>>> ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=SPFILE; and
>>>>> restarted
>>>>>
>>>>>
>>>>> and
>>>>>
>>>>>
>>>>> startup upgrade, because in theory has a similar effect.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> SQL> ALTER PACKAGE FON.PCK_REP_FORMULARIOS_UIF COMPILE;
>>>>> ALTER PACKAGE FON.PCK_REP_FORMULARIOS_UIF COMPILE
>>>>> *
>>>>> ERROR at line 1:
>>>>> ORA-21700: object does not exist or is marked for delete
>>>>>
>>>>> I'm going to move data to a empty database anyway.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 2015-03-13 14:12 GMT-04:00 Andrew Kerber <andrew.kerber_at_gmail.com>:
>>>>>
>>>>> This is pretty much a wild shot in the dark because you provide us
>>>>> very little information. But did you try setting _system_trigs_enabled to
>>>>> false?
>>>>>
>>>>>
>>>>>
>>>>> On Fri, Mar 13, 2015 at 1:04 PM, Mladen Gogala <
>>>>> dmarc-noreply_at_freelists.org> wrote:
>>>>>
>>>>> On 03/13/2015 12:24 PM, Juan Carlos Reyes Pacheco wrote:
>>>>>
>>>>> Hello, please do you know some solution to this problem
>>>>> This is not the first time, a database become corrupted previously
>>>>> only for this.
>>>>>
>>>>> I tried to reconnect, restart, etc.
>>>>>
>>>>> Do you know some trick or procedure?
>>>>>
>>>>> Thank you :)
>>>>>
>>>>> That depends on what are you doing. With this much information, I can
>>>>> only suggest prayer based solutions.
>>>>>
>>>>> --
>>>>> Mladen Gogala
>>>>> Oracle DBA
>>>>> http://mgogala.freehostia.com
>>>>>
>>>>> --
>>>>> http://www.freelists.org/webpage/oracle-l
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>>
>>>>> Andrew W. Kerber
>>>>>
>>>>> 'If at first you dont succeed, dont take up skydiving.'
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>

--
http://www.freelists.org/webpage/oracle-l