|
SQL>
SQL>
SQL> create table my_stats_table
2 (object_name varchar2(30),
3 gather_date date default sysdate);
Table created.
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>
SQL> select object_name, to_char(gather_Date, 'mm/dd/yyyy hh:mi')
2 from my_Stats_table;
no rows selected
SQL>
SQL> drop table my_stats_table;
SQL>
|