%20(1).png)
Session Login Detail
To retrieve login details for all users in an Oracle database, you can use the DBA_AUDIT_SESSION view or the AUD$ table if auditing for logon events is enabled. Here's an example SQL query using the DBA_AUDIT_SESSION view:
sql
set linesize 1000
set pagesize 1000
col OS_USERNAME format a20;
col USERNAME format a20;
col USERHOST format a30;
FROM DBA_AUDIT_SESSION
WHERE ACTION_NAME = 'LOGON';
This query retrieves information about all user logons, including the username, user host, terminal, timestamp, and logoff time.
If you prefer to use the AUD$ table directly, here's an example query:
sql
FROM AUD$
WHERE ACTION# = 100; -- 100 corresponds to LOGON action
Ensure that you have the necessary privileges to query the DBA_AUDIT_SESSION view or the AUD$ table, and remember that the availability of login information depends on your Oracle Database's auditing configuration. Additionally, querying the AUD$ table directly might not be recommended in a production environment, as it involves querying an internal Oracle table.
Always consult with your database administrator and adhere to your organization's policies regarding auditing and data access.
To retrieve details of the last logons for specific users within the last 12 months in Oracle Database, you can modify your query to include a time range condition. Here's the updated query:
- Use distinct parameter to filter out unique username and then use it in below query.