PLSQL block for run the job using dbms_scheduler for gather statistics of 30 schema

Question

I want to create one PL/SQL block where try to run the job using dbms_scheduler package and I want to gather schema stats of all 30 schema. Eg:

begin
     dbms_scheduler_create_job(
     job_name => ....,
     job_type = > 'PL/SQL BLOCK',
     job_action => 'declare
      sch_lst dbms_stats.objecttab := dbms_stats.objecttab()
      begin
      sch_lst.extend(10);
      sch_lst(1).ownname := "ab";   --ab is the Schema name
      sch_lst(2).ownname := "cd";
      .........
      sch_lst(30).ownname := "xy";
      dbms_stats.gather_schema_stats( ......)
      end;
      /
      ',
     start_date => sysdate,
     ..........);
     end;
     /

`sch_lst(1).ownname := "ab"; ` should be `sch_lst(1).ownname := ''ab'';` (two single quotes instead of one double quote) — , Dec 04 '18 at 12:22
@a_horse_with_no_name will it be run or why we use `' '` instead of `"` here. — user8487380, Dec 04 '18 at 14:53

score 3 · Accepted Answer · answered Dec 05 '18 at 13:33

Before start_date => sysdate, remove / and also in the schema name instead of " (double quote) use '' (double single quote) because it is inside the declare statement which is already inside single quote.

begin
 dbms_scheduler_create_job(
 job_name => ....,
 job_type = > 'PL/SQL BLOCK',
 job_action => 'declare
  sch_lst dbms_stats.objecttab := dbms_stats.objecttab()
  begin
  sch_lst.extend(10);
  sch_lst(1).ownname := ''ab'';   --ab is the Schema name
  sch_lst(2).ownname := ''cd'';
  .........
  sch_lst(10).ownname := ''kl'';
  dbms_stats.gather_schema_stats( ......)
  end;
  ',
 start_date => sysdate,
 ..........);
 end;
 /

Then after compile this one. you can check the job by using execute dbms_schedule.run_job('<job_name>');

Barbaros Özhan · Answer 2 · 2018-12-18T05:46:27.977

1

Firstly, you may create such a procedure :

create or replace procedure pr_schema_stats is
  sch_lst owa.vc_arr;
begin
  sch_lst(1) := 'ab';
  sch_lst(2) := 'cd';  
  sch_lst(3) := 'ef';
  sch_lst(4) := 'gh';  
  sch_lst(5) := 'ij';
  sch_lst(6) := 'kl';  
  sch_lst(7) := 'mn';
  sch_lst(8) := 'op';  
  sch_lst(9) := 'rs';  
  sch_lst(10):= 'tu';  

 for i in 1..10
 loop    
   dbms_stats.gather_schema_stats(upper(sch_lst(i)),degree => 4, cascade => true );      
 end loop;
end;

and then call from scheduler as :

declare
    v_job_name varchar2(70) := 'jb_gather_stats';
begin  
    dbms_scheduler.create_job(
        job_name => v_job_name,
        job_type => 'STORED_PROCEDURE',
        job_action => 'pr_schema_stats', 
        start_date => to_date('04-12-2018 19:00:00', 'dd-mm-yyyy hh24:mi:ss'),
        repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;',
        auto_drop => false,
        comments => 'Produces statistics for Cost based SQL statements');

    dbms_scheduler.enable(v_job_name);    
end;

EDIT : You can replace your procedure's code with :

create or replace procedure pr_schema_stats is   
begin

 for c in (   
           select u.username,
                 row_number() over (order by u.username) as rn
            from dba_users u
           where u.account_status = 'OPEN' 
             and u.username not like 'SYS%' 
           )
 loop
  begin
    dbms_stats.gather_schema_stats(c.username,degree => 4, cascade => true );      
   exception when others then 
    dbms_output.put_line(sqlerrm);
  end;
 end loop;
end;

to include all of the ordinary schemas in the analyze task.

edited Dec 18 '18 at 05:46

answered Dec 04 '18 at 12:21

Barbaros Özhan

59,113
10
31
55

can we use `' '` instead of `"`. – user8487380 Dec 04 '18 at 14:54
1

@user8487380 yes, if have to use quotes inside quotes, we mostly use `''`( repeating single-quotes per each single quote inside ) . Yes, we can create `dbms_scheduler` without a stored procedure such as `PL/SQL BLOCK` as in your case with this format : `PLSQL_BLOCK`, but each double quotes `"` must be replaced with `''`, I prefer using `STORED PROCEDURE`. – Barbaros Özhan Dec 04 '18 at 15:15
even schema name also use ' ' or only '. – user8487380 Dec 04 '18 at 16:22
@user8487380 yes, of course even for schema names use as `''schema_name''` for the cases of `PLSQL_BLOCK` for which `job_action` argument is wrapped up with single quotes for the whole string. – Barbaros Özhan Dec 04 '18 at 17:25
1

Inside the declare statement means `job_action` I used `''`(double single quote) instead of `"` and outside i keep `'` as it is means from `start_date` parameter. So is it good. – user8487380 Dec 05 '18 at 07:17
My job is in `scheduled` state but i want to change it to `running` state. How can i do this the same above job. Please let me know. – user8487380 Dec 05 '18 at 15:49
1

@user8487380 convert `start_date` argument to a suitable value such as a value with current day and hour in the near future, and then you need to enable by `dbms_scheduler.enable('jb_gather_stats')`, whenever the defined time reached, it should work, and `running` state persists depending on your job's duration. – Barbaros Özhan Dec 05 '18 at 16:48
After running the above statement only few schema had been analyzed not all. How to resolve this issue so that all user schema has been analyze. Please help me. – user8487380 Dec 10 '18 at 11:27
@user8487380 Unfortunately, I don't know. – Barbaros Özhan Dec 10 '18 at 12:04
@user8487380 you can include `exception handling` added against such situation( to skip the problematic step ). – Barbaros Özhan Dec 18 '18 at 05:47
but is it a good practice i use 1 `begin .. end;` for each schema. If any one fails then rest of the schema will gather the stats. inside each `begin .. end;` i am using `exception handling`. It means for 10 schema use 10 `begin .. end;` and 10 `exception handling'. Is it correct? – user8487380 Dec 18 '18 at 05:52
@user8487380 I think that would be mess of code. In my case if the process fails for any of the schema, already iterates to the next. – Barbaros Özhan Dec 18 '18 at 05:59
1

@user8487380 exactly, yes. Consider this : `begin for c in ( select level lvl from dual connect by level <= 10 ) loop begin dbms_output.put_line(c.lvl / (c.lvl - 5)); exception when others then dbms_output.put_line(sqlerrm); end; end loop; end;` raises an exception at the 5th row but iterates after that. – Barbaros Özhan Dec 18 '18 at 06:25
@user8487380 yes, sure you can. – Barbaros Özhan Dec 18 '18 at 07:56
`For i in 1 .. lvl.count(20) loop` it is showing some error. I want to select only 20 iteration for 1st time & next 20 iteration for next time for different operation. How to take 1st **20** schema in iteration. – user8487380 Dec 18 '18 at 09:56
@user8487380 nothing but replace `10` with `20` in `connect by level <= 10` – Barbaros Özhan Dec 18 '18 at 10:04
but here i am using PL/SQL variable declare means `a_arr` as `VARRAY`. Ex- `type a_arr is varray(30) of varchar2(20)`. This variable i am using inside the `For loop` like `for i in 1 .. a_arr.count(20)` like this for 1st time & then for 2nd time `for i in a_arr.count(21).. a_arr.count`. Same time i am getting error. how to resolve this issue. – user8487380 Dec 18 '18 at 10:06
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/185407/discussion-between-barbaros-ozhan-and-user8487380). – Barbaros Özhan Dec 18 '18 at 10:13

PLSQL block for run the job using dbms_scheduler for gather statistics of 30 schema

2 Answers2