Schedule the procedure to run now and every night at 3 AM: : DBMS_JOB « System Packages « Oracle PL/SQL Tutorial






SQL>
SQL> 
SQL> create table my_stats_table
  2  (object_name  varchar2(30),
  3   gather_date  date default sysdate);


SQL>
SQL> 
SQL> create or replace procedure gather_my_stats as
  2    olist  dbms_stats.objectTab;
  3  begin
  4    dbms_stats.gather_schema_stats(user,
  5            cascade=>TRUE,
  6            method_opt=> 'AUTO',
  7            options=>'GATHER AUTO',
  8            objlist=>olist);
  9
 10    if olist.COUNT > 0 then
 11       FOR x in 1..olist.COUNT LOOP
 12           insert into my_stats_table (object_name)
 13            values(olist(x).objname);
 14       END LOOP;
 15    end if;
 16  end;
 17  /

Procedure created.

SQL>
SQL> variable job_no number
SQL>
SQL> exec dbms_job.submit(:job_no, 'GATHER_MY_STATS;', SYSDATE, 'TRUNC(SYSDATE+1) + 3/24')

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> print job_no

    JOB_NO
----------
       141

SQL>
SQL>
SQL> drop table my_stats_table;

Table dropped.

SQL>
SQL>
SQL>








31.11.DBMS_JOB
31.11.1.DBMS_JOB demonstration
31.11.2.DBMS_JOB.SUBMIT
31.11.3.Submit a job and run
31.11.4.Submit a job and query the user_jobs
31.11.5.Job number
31.11.6.Use dbms_job.submit to call 'execute immediate'
31.11.7.Submit job to change password
31.11.8.Schedule the procedure to run now and every night at 3 AM: