Thursday, 3 January 2013

modify statistics gather in Oracle



BEGIN
  DBMS_STATS.SET_TABLE_PREFS('<schema_name>','ICNCDR','INCREMENTAL','TRUE');
END;
/


BEGIN
  DBMS_STATS.SET_TABLE_PREFS('<schema_name>','ICNCDR','INCREMENTAL','TRUE');
END;
/


create or replace procedure user_stats
AS
      filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
      obj_lst     DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
  filter_lst.extend(1);
filter_lst(1).ownname:='<schema_name>';
filter_lst(1).objname:='<object_name>';

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'<schema_name>',objlist=>obj_lst, obj_filter_list=>filter_lst);
END;
end user_stats;
/


declare
  my_job number;
begin
  dbms_job.submit(job => my_job,
    what => 'user_stats;',
    next_date => trunc(sysdate)+1,
    interval => 'trunc(sysdate)+1');
end;
/

No comments:

Post a Comment