SQL Performance (MOSC)

MOSC Banner

Schedule gather stats job in scheduler

edited Feb 8, 2019 1:37PM in SQL Performance (MOSC) 7 commentsAnswered ✓

Hello All,

I am trying to gather schema stats(all tables in specifc schemas) by scheduing a job in scheduler.

Working as expected on a specific table in a schema :

begin

dbms_scheduler.create_job(

job_name => 'GATHER_VENDOR_SCHEMA_STATS_JOB',

job_type => 'PLSQL_BLOCK',

job_action => 'DECLARE

filter_lst  dbms_stats.objecttab := dbms_stats.objecttab();

BEGIN

  filter_lst.extend(3);

  filter_lst(3).ownname := ''DBO'';

  filter_lst(3).objname := ''OI_ORGANIZATION'';

  dbms_stats.gather_schema_stats(

          OWNNAME          => ''DBO'',

          ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,

          METHOD_OPT      => ''FOR ALL INDEXED COLUMNS SIZE AUTO'',

          DEGREE          => DBMS_STATS.DEFAULT_DEGREE,

          GRANULARITY      => ''ALL'',

              force        => TRUE,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center