Create a procedure to collect statistics on all my objects : DBMS_STATS « System Packages « Oracle PL/SQL Tutorial

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

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>