Re: recompile causes an ORA-21700: object does not exist or is marked for delete
Date: Tue, 24 Mar 2015 22:44:09 +0700
Message-ID: <CAP50yQ_EwhSOJ4C86LFy9+39V2xfPaz2L0s4Z_FoOoKXeeDwzg_at_mail.gmail.com>
If you do have dictionary inconsistencies - likely an upgrade won't help you - I wouldn't be surprised if the upgrade would fail.
What I would suggest instead is to open an SR with Oracle - and have them assist with the corruption. Actions like the ones you perform can be supported - if sanctioned by Oracle support.
There is also a dictionary health check script on My Oracle Support that you can use to run a couple additional tasks (like Oracle support will ask you to run that anyway).
Stefan
On Tue, Mar 24, 2015 at 10:40 PM, Juan Carlos Reyes Pacheco < jcdrpllist_at_gmail.com> wrote:
> Thank you Stefan and everyone.
>
> I "solved" in a test database of course you CAN'T do that.
> The solution will be to export to 12c and see what happens.
>
> But only for test I did the following
> The drop type fon.SYS_PLSQL_133027_131_2 FORCE;
> said marked for deleted.
>
> The I search all the views related to type.
> select * from dba_views where view_name like '%TYPE%';
> select * from dba_objects where object_name = 'SYS_PLSQL_133027_131_2';
> select * from dba_views where view_name like '%TYPE%';
> SELECT * FROM DBA_TYPES WHERE TYPE_NAME= 'SYS_PLSQL_133027_131_2';
> SELECT * FROM DBA_TYPE_METHODS WHERE TYPE_NAME= 'SYS_PLSQL_133027_131_2';
> SELECT * FROM DBA_TYPE_ATTRS WHERE TYPE_NAME= 'SYS_PLSQL_133027_131_2';
> SELECT * FROM DBA_INDEXTYPES WHERE indextype_name=
> 'SYS_PLSQL_133027_131_2';
> SELECT * FROM DBA_COLL_TYPES WHERE TYPE_NAME= 'SYS_PLSQL_133027_131_2';
> .....
>
> In verison I found the type SYS_PLSQL_133027_47_2 was related to it
> then I dropped it
> drop type fon.SYS_PLSQL_133027_47_2 FORCE; (ok)
>
> But the problem continued and because it was a very simple type.
> I did the follow ( don't do it)
> delete from sys.type$ where tvoid in (select oid$ from sys.obj$ where
> name='SYS_PLSQL_133027_131_2');
> delete from sys.obj$ where name='SYS_PLSQL_133027_131_2';
>
> and then I could drop the type
> The drop type fon.SYS_PLSQL_133027_131_2 FORCE; (dropped)
>
> ALTER PACKAGE FON.PCK_REP_FORMULARIOS_UIF COMPILE;
> (compiled ok :) ),
> of course this is not a solution for a production database, but at least I
> found where the problem was.
>
>
>
>
> 2015-03-24 9:50 GMT-04:00 Stefan Knecht <knecht.stefan_at_gmail.com>:
>
> From this:
>>
>> 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-lReceived on Tue Mar 24 2015 - 16:44:09 CET