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

Hi Sayan,

I think I saw a similar algorithm in one of Steve Adams/Jonathan Lewis books. Or maybe on MOS, but couldn't find it now. This is the closest I was able to find though: Querying V$Access Contents On Latch: Library Cache (Doc ID 757280.1)

No, I don't use anything but Linux x86-64. Yes, endianness might be the reason for this type of behavior (it is also mentioned in the MOS note above).

On Sun, 2 Apr 2023 at 01:17, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Mikhail,
>
> Great catch! I'm pleased to see that my guess about ora_hash() working on
> concatenation was correct. By the way, how did you come across this
> information? I'm always eager to learn more about such investigations.
>
> P.S. To be honest, I haven't been able to find my notes from 2017, so I'm
> uncertain about the differing object names in various databases (as
> mentioned in my previous point #5, that was from my answer in RuOUG
> telegram channel). Have you had a chance to test this behavior on different
> platforms? It might be related to the big/little endian issue, or perhaps I
> simply made a mistake. :)
>
> On Sat, Apr 1, 2023 at 9:37 PM Mikhail Velikikh <mvelikikh_at_gmail.com>
> wrote:
>
>> 1. The hash value is an ora_hash on
>> <package_name>||chr(0)||<owner>||chr(0).
>> E.g.:
>>
>> SQL> select ora_hash('PKG_DUMMY'||chr(0)||'TC'||chr(0)) from dual;
>>
>> ORA_HASH('PKG_DUMMY'||CHR(0)||'TC'||CHR(0))
>> -------------------------------------------
>> 3182294215
>>
>> Whereas the actual types were called sys_plsql_*bdadf4c7*% (3182294215
>> in hex)
>>
>> 2. the hash should be the same across different databases. At least, the
>> algorithm is the same in both 12.1 (Jan'23 Bundle) and 19.18.
>>
>> 3. to get those types in 18c and beyond, set _EnableShadowTypes = true
>> (can be set at the session level).
>> Useful MOS note discussing this parameter: Pipeline Function Returning
>> UDT Defined in the Package Specification Raises ORA-00902 Post Database
>> Upgrade to 18c (Doc ID 2696498.1)
>>
>>
>>
>> On Sat, 1 Apr 2023 at 19:58, DOUG KUSHNER <dougk5_at_cox.net> wrote:
>>
>>> No luck with either recommendation. I ran all of the SYS queries in the
>>> trace file that preceded the type creation statements, but none of them
>>> returned the value *8642B728.*
>>>
>>> Guess this will remain a mystery.
>>>
>>> Thanks,
>>> Doug
>>>
>>>
>>> On March 31, 2023 at 9:03 AM 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