Re: Implicitly created TYPE object comparison between 11g & 12c+

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Fri, 31 Mar 2023 17:16:26 +0100
Message-ID: <CAOVevU44LNGPwHYoVNGq54DdFMJ1eFePEra6q91epzvu_=VAdA_at_mail.gmail.com>

I did a little research on this 5 years ago and couldn't find a direct link to the package, but here's what I discovered:

1. Considering the length and changes, it is most likely an ora_hash of some concatenation;
2. It doesn't depend on the package's object_id - recreating the package with the same name but a different object_id results in the same hash. Most likely, this is related to the emergence of Edition-based redefinition;
3. It depends on the package's owner+object_name - follows from the second point;
4. When creating the same package on another database, it is created with a different hash.

The connections I found:
https://gist.github.com/xtender/4047237147944d5e89e59f515f287d73

select

 o.obj#  , o.owner# , o.name , o.namespace,
 o.type# , o.ctime  , o.stime, o.status,
 o.flags , o.oid$   , o.signature,

  --o.dflcollid,
  'type' d,
  t.typecode,t.hashcode,
  'coll',
  c.*
  ,s.*
from sys.obj$ o, sys.user$ u,sys.type$ t,sys.collection$ c, sys.source$ s where 1=1

and u.name='&OWNER'
and o.owner#=u.user#
and o.oid$ = t.tvoid(+)
and c.toid(+) = t.tvoid
and o.flags=1048576
and o.type# in (10,13)
and o.obj#=s.obj#(+)

/

PS. I will try to find my notes later and do some research again.

On Fri, Mar 31, 2023 at 5:04 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> For this example, what's the object_id of the package?
>
> The hex might be a multi-part number from which you could split out the
> package id. Convert the object_id to hex then keep multiplying by 2 until
> you overshoot the hex.
> Alternatively, how about enabling tracing while you execute the package
> and create the objects - you might see a clue about the source of the hex -
> e.g. a call to a sequence.
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
> On Fri, 31 Mar 2023 at 16:38, DOUG KUSHNER <dougk5_at_cox.net> wrote:
>
>> Our developers have created packages that define a type. Upon package
>> creation, 3 type objects are implicitly created in the package owner's
>> schema, all with name 'SYS_PLSQL%'.
>>
>> In 11.2.0.4, the names are similar to the following, where '3557076' is
>> the package's object_id.
>>
>> SYS_PLSQL_*3557076*_DUMMY_1
>> SYS_PLSQL_3557076_27_1
>> SYS_PLSQL_3557076_9_1
>>
>> In 12.1, the type name does not contain the parent object's id, but some
>> unknown hex value.
>>
>> SYS_PLSQL_*8642B728*_9_1
>> SYS_PLSQL_8642B728_DUMMY_1
>> SYS_PLSQL_8642B728_27_1
>>
>> Does anyone know how to find the parent package's object_id in 12.1 and
>> later versions?
>>
>>
>> Regards,
>> Doug
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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

Received on Fri Mar 31 2023 - 18:16:26 CEST