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>
|