How to audit CREATE SESSION

Audit all session connecting to the database:

audit create session [ whenever successful | not successful ];

To audit sessions created by a specific user:

audit create session by SCOTT [ whenever successful | not successful ];

If you omit the condition "whenever successful/not successful", Oracle will audit all connections, regardless of their success or failure.


To disable auditing:

noaudit create session;
noaudit create session by scott;

To verify that the auditing options has indeed been set:

SELECT * 
 FROM DBA_PRIV_AUDIT_OPTS 
 WHERE USER_NAME = 'SCOTT';

Result:

USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
SCOTT		CREATE SESSION	BY ACCESS	BY ACCESS

The view DBA_PRIV_AUDIT_OPTS gives you "current system privileges being audited across the system and by user".
Note that a NULL value in the user_name column of DBA_PRIV_AUDIT_OPTS means system-wide auditing.

To find your audit info, use the query outlined here