Re: sys_guid()

Connor,

Thanks for the examples, and the

It's useful to know that Windows and (at least some of the) Unix implementations are completely different - with all considerations implied by the sequential/random side effects.

Regards
Jonathan Lewis

On Mon, 4 Oct 2021 at 15:47, Connor McDonald <mcdonald.connor_at_gmail.com> wrote:

> Windows
>
> SQL> select sys_guid() from all_tables where rownum <= 5;
>
> SYS_GUID()
> --------------------------------
> CD48E63999534BEABA6BD8A58ACE134B
> 096CB75B5BDF472F92CD04DAEF98EFFC
> E440AB8C9A3E4DE79E8F8043EFE0B0AB
> F56E2B6AC420482A9B45C3470FA04F33
> B3259B96759A454BABAF0C8C6C927CB1
>
> and
>
> SQL> select sys_guid() from all_tables where rownum <= 5;
>
> SYS_GUID()
> --------------------------------
> F03D31EF992C4F3D92B496315A4BE319
> D18A8AE49AB64E848938F4E328C91B97
> B2A38E71E34F4436BAA13BAE13DAB5AA
> 6996772B3C2946869DDADF41BE1037CC
> C249BA6CD0B14147A013B3A0E02E86B0
>
> For as long as I can remember, Windows has been the only one to look
> suitably "random". I've seen various projects (inside and outside of
> Oracle) that steer clear of GUID for keys etc for this reason, often using
> a blend of sequences and dbms_crypto.randombytes
>
>
>
> On Mon, Oct 4, 2021 at 10:34 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Quick follow-up.
>> Does anyone have an instance running on Windows that they can do this
>> test on
>>
>> Thanks
>> Jonathan Lewis
>>
>>
>> On Fri, 1 Oct 2021 at 10:28, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> I put out a tweet yesterday (
>>> https://twitter.com/JLOracle/status/1443302780266029061 ) about
>>> sys_guid() and the different pattern of results you could get depending on
>>> the platform you were running on. The most recent check I had done was
>>> 12.1.0.2, and I don't have access to anything other than OEL 7.9 at the
>>> moment to check more recent versions.
>>>
>>> It would be nice if a few people could run the following query on their
>>> systems and report the results, along with O/S, for 19c to see if there are
>>> stll differences.
>>>
>>>
>>> Here's my result from 19.11
>>>
>>> SQL> select sys_guid() from all_tables where rownum <= 5;
>>>
>>> SYS_GUID()
>>> --------------------------------
>>> CD47CA7FBE987B90E0530100007FE7D5
>>> CD47CA7FBE997B90E0530100007FE7D5
>>> CD47CA7FBE9A7B90E0530100007FE7D5
>>> CD47CA7FBE9B7B90E0530100007FE7D5
>>> CD47CA7FBE9C7B90E0530100007FE7D5
>>>
>>> 5 rows selected.
>>>
>>> Almost simultaneously from another session
>>> SYS_GUID()
>>> --------------------------------
>>> CD47CB493173114BE0530100007FA8A8
>>> CD47CB493174114BE0530100007FA8A8
>>> CD47CB493175114BE0530100007FA8A8
>>> CD47CB493176114BE0530100007FA8A8
>>> CD47CB493177114BE0530100007FA8A8
>>>
>>> This seems to have a sequential component tied to the session (possibly
>>> to the moment the session connected** rather than the session id). If you
>>> follow the links in the tweet you'll find that Oracle on Windows used to
>>> produce very random-looking leading values
>>> you reconnect once per session and run t
>>>
>>> ** If I reconnect once per session then the 7th digit of the guid
>>> increments by 1.
>>>
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>
> --
> Connor McDonald
> ===========================
> blog: connormcdonald.wordpress.com
> twitter: _at_connor_mc_d
>
> "If you are not living on the edge, you are taking up too much room."
> - Jayne Howard
>
> *Fine print: Views expressed here are my own and not necessarily that of
> my employer*
>

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