Unique session ID

The UNIQUE_SESSION_ID function returns a character string unique to the session among all sessions currently connected to the database. The return string can be up to 24 bytes in length. Multiple calls to the function from the same session will always return the same string. The program header follows:

FUNCTION DBMS_SESSION.UNIQUE_SESSION_ID
RETURN VARCHAR2;

Notes

UNIQUE_SESSION_ID is functionally identical to the DBMS_PIPE.UNIQUE_SESSION_NAME function; however, their return values are not identical. Be very careful not to write code that assumes that these two functions are equivalent.
The UNIQUE_SESSION_ID function does not raise any exceptions.

Example

To display the value of the unique id for the current session, specify the following:

DECLARE
   my_unique_id   VARCHAR2(30);
BEGIN
   my_unique_id := DBMS_SESSION.UNIQUE_SESSION_ID;
   DBMS_OUTPUT.PUT_LINE('UNIQUE ID: '||my_unique_id);
END;

An example of output from executing the preceding PL/SQL block follows:

UNIQUE ID: F000E4020000

Source: https://www.toadworld.com/platforms/oracle/w/wiki/3246.dbms-session-unique-session-id

When you have problem receive information about session in Oracle PLSQL database for example purposes journalling. Look bellow on SQLs witch rewrite two or more rows:

SELECT * FROM v$session s WHERE username IS NOT NULL AND s.AUDSID = SYS_CONTEXT ('userenv', 'sessionid') ORDER BY username, osuser;

You need add two condition to receive one row.

WHERE username IS NOT NULL AND s.AUDSID = SYS_CONTEXT ('userenv', 'sessionid') AND S.SID = SYS_CONTEXT ('userenv', 'sid')

Final query return unique session row:

SELECT * FROM v$session s WHERE username IS NOT NULL AND s.AUDSID = SYS_CONTEXT ('userenv', 'sessionid') AND S.SID = SYS_CONTEXT ('userenv', 'sid') ORDER BY username, osuser;