Re: How to fix the INVALID dba_objects

<vwu_at_anacomp.com> wrote in message
news:1146549110.849878.143720_at_e56g2000cwe.googlegroups.com...
>
> Sybrand Bakker wrote:
>> On 1 May 2006 21:24:24 -0700, "BD" <bobby_dread_at_hotmail.com> wrote:
>>
>> >The appropriate strategy for fixing these objects will require a little
>> >more info - find out what type of objects they are, and see if you can
>> >scrutinize them to determine why they are not valid.
>> >
>> >As to the duplicate, these two objects are likely either different
>> >object types, or belong to a different owner/schema.
>> >
>> >Try
>> >
>> >SELECT OWNER || '.' || OBJECT_NAME || ' - ' || OBJECT_TYPE FROM
>> >DBA_OBJECTS WHERE STATUS = 'INVALID';
>> >
>> >This should give you slightly more to go on.
>> >
>> >If they are views, they can be recreated; if such attempts fail, the
>> >views may be referencing non-existent rows in the tables they refer to.
>> >
>> >If they are procedures, they can be recompiled. If they fail to
>> >recompile, the code for the procedures should be reviewed.
>> >
>> >BD
>>
>> Adding to that, the output of OP seems to point to the fact that he
>> has catalog or catproc objects under a non-SYS user.
>> They won't compile, ever, and need to be dropped.
>>
>> --
>> Sybrand Bakker, Senior Oracle DBA
>
> The INVALID objects are TYPE, they are not procedure or view:
>
> SQL> SELECT OWNER || '.' || OBJECT_NAME || ' - ' || OBJECT_TYPE FROM
> DBA_OBJECTS WHERE STATUS = 'INVALID';
>
> OWNER||'.'||OBJECT_NAME||'-'||OBJECT_TYPE
> --------------------------------------------------------------------------------
> SYS.ODCIARGDESC - TYPE
> SYS.ODCICOLINFO - TYPE
> SYS.ODCICOST - TYPE
> SYS.ODCIINDEXINFO - TYPE
> SYS.ODCIINDEXINFO - TYPE
>
> Should I just simply drop it?
>

Seen this issue before - it's induced by type evolution (ALTER TYPE.) Actually these are different type versions (with different obj# and OID, and all but one of them have SUBNAME like '$VSN_n', where n is type version, current version's SUBNAME is NULL.) There was bug #2782260 fixed in 10g and 9.2.0.4, which caused method duplication in user_type_methods after type evolution, and there's bug #2603393 which causes type source code lines duplication in xxx_source after type evolution (amazingly, this bug seems to be reintroduced or not backported to 9.2.0.7 even though it was discovered in 9.2.0.1.)

Anyway, I think you can ignore these invalid versions of evolved types, no need to drop or validate them as they are no longer in use. In fact, I believe you can't validate them without dropping and recreating, probably in migration mode (STARTUP MIGRATE.)

-- 
   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)
   http://www.dynamicpsp.com