Viswamitra's blog

To check the status of Oracle scheduler job which executed on specific date , use the below query

 select JOB_NAME,STATUS,RUN_DURATION,ADDITIONAL_INFO,TRUNC(ACTUAL_START_DATE) from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name=’REFRESH_ACD_GROUP_HISTORY_M_J’ and TRUNC(ACTUAL_START_DATE)=’27-MAY-16′;

Today one of my friend asked me for a trigger by which he want to stop a particular user from misusing his privileges to create/drop any objects. Below is the trigger for the same.

  create or replace trigger ddl_trigger
  before DDL ON DATABASE
  declare
      l_sysevent varchar2(25);
  begin
      select ora_sysevent into l_sysevent from dual;

      if (l_sysevent in (‘CREATE’,’DROP’) AND ora_login_user in (‘TEMP’))
      then
      RAISE_APPLICATION_ERROR(-20998, ‘You do not have the privilege to create tables or other database objects. Please contact your manager if you need the privilege.’);  
      end if;
  end;
  /