How to find the User who is connected to Oracle

To know about the list of users that is now connected and the time when they connect to database issue the following query.

SQL> SELECT s.username, s.program, s.logon_time
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
AND si.sid(+) = s.sid
AND s.type = 'USER';
USERNAME PROGRAM LOGON_TIM
---------- ------------------------------------------------ ---------
ARJU sqlplus@neptune (TNS V1-V3) 28-MAY-08
oracle@neptune (J000) 28-MAY-08

To know only the current session user name you can issue the following query,

SQL> SELECT USERNAME from v$session where audsid = sys_context('userenv','sessionid');
USERNAME
------------------------------
ARJU

To know about on which schema you are connecting issue following query.

SQL> SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
ARJU

To know about the connected user user for the current session issue,
SQL> SELECT sys_context('USERENV', 'SESSION_USER') FROM dual;
SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
ARJU

If you use SQL*plus then simply issue,
SQL> SHOW USER;
USER is "ARJU"

Related Documents
Get IP Address from hostname within Oracle Database
How to find current session ID
How to know which objects are being accessed by a user