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