How to Generate Globally Unique Identifier on the Fly

Problem

How do we generate an identifier that is globally unique across databases and machines? A globally unique identifier is very useful. For example, if we mark a credit card transaction uniquely, we trace it in different stages including authorization, settlement, dispute etc.

Solution

In the earlier post Five Ways of Creating Unique Record Identifier For Oracle Tables we mentioned unique identifiers in Oracle can be created by using: 1. Oracle pseudocolumn rowid; 2. Oracle rownum; 3. row_number() function; 4. Sequence; 5. sys_guid().
The simplest solution to globally unique identifier is to call sys_guid() whenever we need to. It does not depend on a separate structure like sequence. Other mechanisms include rowid, rownum and row_number() are only guaranteed to be unique for a single table. I have seen a self-made globally unique identifier generator does not work 100% of the time.
The following query uses sys_guid function to generate globally unique identifier for every row in EMP table. Sys_guid returns 16 byte "raw" type.

SQL> select sys_guid() as gid, EMPNO, ENAME from EMP;

GID                                   EMPNO ENAME
-------------------------------- ---------- ----------
EB567FE077DC5427E040D00AD17F0769       7839 KING
EB567FE077DD5427E040D00AD17F0769       7698 BLAKE
EB567FE077DE5427E040D00AD17F0769       7782 CLARK
EB567FE077DF5427E040D00AD17F0769       7566 JONES
EB567FE077E05427E040D00AD17F0769       7788 SCOTT
EB567FE077E15427E040D00AD17F0769       7902 FORD
EB567FE077E25427E040D00AD17F0769       7369 SMITH
EB567FE077E35427E040D00AD17F0769       7499 ALLEN
EB567FE077E45427E040D00AD17F0769       7521 WARD
EB567FE077E55427E040D00AD17F0769       7654 MARTIN
EB567FE077E65427E040D00AD17F0769       7844 TURNER
EB567FE077E75427E040D00AD17F0769       7876 ADAMS
EB567FE077E85427E040D00AD17F0769       7900 JAMES
EB567FE077E95427E040D00AD17F0769       7934 MILLER

14 rows selected.