How can we schedule a job to run in Oracle ( sql developer or Toad ) , to trigger a mail whoever are recently connected to particular database

Question

Is there a way how can i get all the users recently connected to particular database along with their Host Name . I want to trigger this Sql or procedure in a form of job and schedule the job in oracle to run in every one hour.

Please suggest with any options.

Answer 1

The below PL/SQL block creates a job that looks at the audit trail, creates a list of users and hosts that have logged on in the past hour, and emails the results.

Before the code can work, you may have to install UTL_MAIL:

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

You may also need to set the parameter SMTP_OUT_SERVER, and possibly configure an ACL in the database to give you permission to send emails. Just follow the error messages and use Google to fix them.

If your database is not already auditing connections, run:

audit connect;

Finally, create the job like below. This is a very simple, text formatted email using DBMS_SCHEDULER, DBA_AUDIT_TRAIL, and UTL_MAIL.

--Create an hourly job to email the last hours worth of logons.
begin
    dbms_scheduler.create_job(
        job_name        => 'hourly_logons',
        job_type        => 'plsql_block',
        start_date      => systimestamp at time zone 'US/Eastern',
        repeat_interval => 'freq=hourly;byminute=0;',
        enabled         => true,
        job_action      =>
        q'[
            declare
                v_message varchar2(32767);
            begin
                --Create the message based on audit entries.
                for rows in
                (
                    select username, userhost, count(*) the_count
                    from dba_audit_trail
                    where timestamp > systimestamp - interval '1' hour
                        and action_name = 'LOGON'
                    group by username, userhost
                    order by 1,2
                ) loop
                    v_message := v_message || rows.username || chr(9) || rows.userhost || chr(9) ||
                        rows.the_count || chr(10);
                end loop;

                --Email the message
                utl_mail.send
                (
                    sender     => 'some_address@some_domain.com',
                    recipients => 'some_address@some_domain.com',
                    subject    => 'Logons in past hour',
                    message    => v_message
                );
            end;
        ]'
    );
end;
/

You may want to manually run the job during testing, and check the scheduler metadata:

--Force the job to run:
begin
    dbms_scheduler.run_job('HOURLY_LOGONS');
end;
/

--Check the job status and history.
select * from dba_scheduler_jobs where job_name = 'HOURLY_LOGONS';
select * from dba_scheduler_job_run_details where job_name = 'HOURLY_LOGONS' order by log_date desc;

The above code is not tested because I don't have a mail server setup on my home machine. But I've done this several times before and am certain that something very close to this should work.