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.