SYS_CONTEXT

SYS_CONTEXT returns the value of parameter associated with the context namespace.
USERENV is an Oracle provided namespace that describes the current session.

1. sid of current session
SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
OR
SELECT USERENV('SID') FROM DUAL

2. name of host from which client has connectd to oracle
SELECT SYS_CONTEXT('USERENV', 'HOST') FROM DUAL;

3. os username of the client process that has started the session.
SELECT SYS_CONTEXT('USERENV', 'OS_USER') FROM DUAL;

4. ip address from which the client is connected.
SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') FROM DUAL;

5. name of the instance
SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME') FROM DUAL;

6. name of the database as db_name initialization parameter.
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') FROM DUAL;

7. domain of the database as db_domain initialization parameter.
SELECT SYS_CONTEXT('USERENV', 'DB_DOMAIN') FROM DUAL;

8. db user name by which the current user is authenticated.
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

9. get sid and serial# from current session using the auditing session identifier
SELECT SID, SERIAL# FROM V$SESSION WHERE AUDSID = SYS_CONTEXT('USERENV','SESSIONID');

Reference: http://download.oracle.com/docs/cd/B13789_01/server.101/b10759/functions150.htm