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

Comments

Popular posts from this blog

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

stick the job to the specific instance in Oracle RAC

finding fragmentation at table level and tablespace level in Oracle and steps to remove them