SQL Performance (MOSC)

MOSC Banner

DBMS_Scheduler Table stats collection issue

edited Feb 12, 2018 12:37PM in SQL Performance (MOSC) 2 commentsAnswered ✓

Hi Team,

I want to schedule table collect stats through SQL developer by using DBMS_Scheduler. I tried the query like below and the job creation is success but it is failed to run.

Create job:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

   job_name             => 'STATSNEW',

   job_type             => 'PLSQL_BLOCK',

   job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS("SREE", "XYZ",estimate_percent=>20,block_sample=>TRUE,degree=>4); end;/',

   start_date           => '12-FEB-2018 10:35:00AM',

   repeat_interval      => 'FREQ=HOURLY',

   enabled              =>  TRUE,

   comments             => 'Gather table statistics');

END;

/

PL/SQL procedure successfully completed.

Execution output:

select log_id,job_name,status,log_date

from dba_scheduler_job_run_details

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