Submit a job and query the user_jobs : DBMS_JOB « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_JOB

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.