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

By the way, I wanted to check on the current version 19c (I don't have 12.1/12.2 at hand right now) and found out that now there are no such objects in sys.obj$.
And you can simply view the related objects through sys.type#.package_obj#:

select

   t.version
  ,t.typ_name,t.package_obj#
  ,o.owner,o.object_name,o.object_type
  ,t.version#,t.typecode,t.properties,t.attributes,t.methods

    ,dba_objects o
where t.package_obj# is not null
  and o.object_id = t.package_obj#;

On Fri, Mar 31, 2023 at 5:16 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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
>

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

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