This is almost too easy.

1. From an Oracle account with DBA privileges, execute the command
   
   alter system set audit_trail = db scope = spfile;
   
   This command enables database auditing, recording the audit information in the table sys.aud$. audit_trail is not a dynamic parameter, so the scope has to be spfile, and then the database restarted.
    
2. Restart your database.
    
3. From an Oracle account with DBA privileges, execute the command
   
   audit session;

You have just successfully begun auditing database connections. What that means is that

• Every attempt to log into the database is recorded and timestamped.
• The success or failure of the attempt is recorded.
• If the connection is successful, when the session disconnects, a timestamp is recorded.

The following commands provide a simple report about connections to the database:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
COLUMN "Oracle User" FORMAT A12
COLUMN "Network User" FORMAT A30
SET LINES 100
SET PAGES 50
SELECT userid "Oracle User", returncode "Status", timestamp# "Logon Time",
       logoff$time "Logoff Time", spare1 "Network User"
FROM   sys.aud$
ORDER BY timestamp# DESC;

Oracle User      Status Logon Time           Logoff Time          Network User

------------ ---------- -------------------- -------------------- -----------------------------

SYSTEM                0 25-APR-2006 14:36:20                      MYDOMAIN\admin

SYSTEM                0 25-APR-2006 14:11:41 25-APR-2006 14:34:25 MYDOMAIN\admin

SCOTT              1017 20-APR-2006 11:37:54                      MYDOMAIN\scott

SCOTT                 0 20-APR-2006 11:36:37 20-APR-2006 11:37:42 MYDOMAIN\scott

4 rows selected.

The list is in descending order by the column timestamp#, so the most recent login attempts are first. A status of 0 indicates a successful login. A status of 1017 is the Oracle error ORA-01017: invalid username/password; logon denied. So the above listing shows that this database had four logon attempts with one unsuccessful. The first line of the report shows a successful logon with no logoff time, so that user was connected when this report was run.

Please note that the sys.aud$ table is a physical table, and this information does take space in your database. Rows should be deleted as necessary to maintain the size of the table.

And if you want to stop auditing connections, the command is simply

noaudit session;

Note: This tip was tested using Oracle9i.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.