Re: recompile causes an ORA-21700: object does not exist or is marked for delete
- From: Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx>
- To: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- Date: Tue, 24 Mar 2015 11:40:47 -0400
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@xxxxxxxxx>:
> 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@xxxxxxxxx> wrote:
>
>> The full trace :)
>>
>> 2015-03-24 8:59 GMT-04:00 Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx
>> >:
>>
>> 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@xxxxxxxxx>:
>>>
>>> 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@xxxxxxxxx> 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@xxxxxx>:
>>>>>
>>>>> 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@xxxxxxxxxxxxx [mailto:
>>>>>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Juan Carlos Reyes
>>>>>> Pacheco
>>>>>> *Sent:* Monday, March 23, 2015 2:49 PM
>>>>>> *To:* Andrew Kerber
>>>>>> *Cc:* dmarc-noreply@xxxxxxxxxxxxx; 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@xxxxxxxxx>:
>>>>>>
>>>>>> 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@xxxxxxxxxxxxx> 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
>>>>>>
>>>>>> --
>>>>>> //www.freelists.org/webpage/oracle-l
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>>
>>>>>> Andrew W. Kerber
>>>>>>
>>>>>> 'If at first you dont succeed, dont take up skydiving.'
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
Other related posts:
