Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Packages

 

Submit a job and query the user_jobs

SQL> SQL> create or replace procedure analyze_my_tables   2  as   3  begin   4      for x in ( select table_name from user_tables )   5      loop   6          execute immediate   7              'analyze table ' || x.table_name || ' compute statistics';   8      end loop;   9  end;  10  / Procedure created. SQL> SQL> declare   2      l_job number;   3  begin   4      dbms_job.submit( job       => l_job,   5                       what      => 'analyze_my_tables;',   6                       next_date => trunc(sysdate)+1+3/24,   7                       interval  => 'trunc(sysdate)+1+3/24' );   8  end;   9  / PL/SQL procedure successfully completed. SQL> SQL> select job, to_char(sysdate,'dd-mon'),   2                    to_char(next_date,'dd-mon-yyyy hh24:mi:ss'),   3                    interval, what   4  from user_jobs   5  /        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT --------------------------------------------------------------------------------         21 25-jul 01-jan-4000 00:00:00 SYSDATE + (10/(24*60*60)) P_RUN_INSERT;         22 25-jul 01-jan-4000 00:00:00 SYSDATE + (10/(24*60*60)) P_RUN_INSERT;        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT --------------------------------------------------------------------------------         41 25-jul 26-jul-2008 03:00:00 TRUNC(SYSDATE+1) + 3/24 GATHER_MY_STATS;         61 25-jul 25-jul-2008 19:34:32 SYSDATE + 1/24        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT -------------------------------------------------------------------------------- LOG_SOURCE;        101 25-jul 01-jan-4000 00:00:00 SYSDATE + (10/(24*60*60)) P_RUN_INSERT;         82 25-jul 26-jul-2008 03:00:00        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT -------------------------------------------------------------------------------- trunc(sysdate)+1+3/24 analyze_my_tables;        121 25-jul 01-jan-4000 00:00:00 SYSDATE + (10/(24*60*60)) P_RUN_INSERT;        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT --------------------------------------------------------------------------------        122 25-jul 01-jan-4000 00:00:00 SYSDATE + (10/(24*60*60)) P_RUN_INSERT;        141 25-jul 25-jul-2008 21:18:41 TRUNC(SYSDATE+1) + 3/24 GATHER_MY_STATS;        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT --------------------------------------------------------------------------------        161 25-jul 25-jul-2008 19:46:13 SYSDATE + 1/24 LOG_SOURCE;        162 25-jul 25-jul-2008 19:49:44 SYSDATE + 1/24        JOB TO_CHA TO_CHAR(NEXT_DATE,'D ---------- ------ -------------------- INTERVAL -------------------------------------------------------------------------------- WHAT -------------------------------------------------------------------------------- LOG_SOURCE;        182 25-jul 26-jul-2008 03:00:00 trunc(sysdate)+1+3/24 analyze_my_tables; 12 rows selected.