Sequences and identity columns give keys that are unique within one table of one database. When rows are created in several databases and merged later, or a key must not reveal how many rows exist, a globally unique identifier is the usual choice. SYS_GUID returns one: a 16-byte RAW value that is unique across databases and calls.
Code for This Guide
The main example is in the examples/misc-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
sys_guid()
SYS_GUID takes no arguments and returns RAW(16), displayed as 32 hexadecimal digits. Oracle builds it from an identifier of the host and process plus a counter.
Generate GUIDs
Example:
select sys_guid() as guid1, sys_guid() as guid2, length(rawtohex(sys_guid())) as hex_len from dual;
Output:
GUID1 GUID2 HEX_LEN ___________________________________ ___________________________________ __________ 5C68EC824DD5F4F8E063020011ACCFC7 5C68EC824DD6F4F8E063020011ACCFC7 32
The two values generated one after the other differ in a single digit, the counter. That makes SYS_GUID values cluster well in an index, but also predictable: never use them as secrets, tokens, or passwords.
Use It as a Column Default
A RAW(16) primary key with DEFAULT SYS_GUID() gets a new identifier for every row. The table below also has a text column filled with a random UUID, for comparison.
Setup:
create table messages ( id raw(16) default sys_guid() primary key, body varchar2(40), uid_text varchar2(36) default raw_to_uuid(uuid()) );
Example:
insert into messages (body) values ('first'), ('second'), ('third');
select rawtohex(id) as id, body, uid_text from messages order by id;Output:
3 rows inserted. ID BODY UID_TEXT ___________________________________ _________ _______________________________________ 5D193B27B8731FB6E063020011AC2C0F first 79a51f2b-977b-4781-9f2e-d95660f60314 5D193B27B8741FB6E063020011AC2C0F second df1eef8f-a6f6-42c1-b917-8d58a3c2d0ff 5D193B27B8751FB6E063020011AC2C0F third 25877a0d-08d4-4ef5-8068-c38938933919
The SYS_GUID keys increase by one in the counter digits, while the random UUIDs have nothing in common. Drop the table afterward with drop table messages purge.
SYS_GUID Compared with UUID
| Function | Values | Good for |
|---|---|---|
| SYS_GUID | Sequential within a process; predictable | Keys that index well |
| UUID | Random version 4 UUIDs | Identifiers that must not be guessable or must follow the UUID standard |
| Sequence or identity | Small increasing numbers | Keys within one database; smallest and fastest |
Related Guides
Conclusion
SYS_GUID returns a globally unique 16-byte RAW identifier, built from the host, the process, and a counter. Use it as a column default for keys that must be unique across databases; prefer UUID when identifiers must be random, and sequences when uniqueness within one database is enough.
