-
dbms_submit to run dbms_stats failed
Hi all,
I am trying to schedule a job to run dbms_stats and it failed, below is my syntax:
var j number
begin
dbms_job.submit
(
:j,
'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_A',CASCADE=>TRUE);',
'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_B',CASCADE=>TRUE);',
'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_C',CASCADE=>TRUE);',
'SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_D',CASCADE=>TRUE);',
SYSDATE,
'NEXT_DAY(trunc(sysdate),''SATURDAY'')+1/12'
);
COMMIT;
end;
any advises???? thanks
-
sure, post the error message.
Jeff Hunter
-
AFAIK, you can't submit more than one proc. So either submit several jobs or put the calls to DBMS_STATS into a single proc.
-
since I have more than schemas in the datbase, instead of scheduling many job, can I use DBMS_STATS.GATHER_DATABASE_STATS????
-
There might be the problem that DBMS_STATS.GATHER_DATABASE_STATS collects stats on the SYS schema - which might not be a good idea. Someone raised the question here and even Jurij didn't know the answer.
-
Dapi,
I also saw that somewhere saying dbms_stats.gather_database_stats would collect stats on SYS which is not a good thing to do. couple questions:
1. if dbms_stats.gather_database_stats collect stats on SYS, so when can you use it???? for what purpose???
2. if I have close to 20 schemas in my database and I want to collect stats for all of them once a week, so I have to submit the job via dbms_submit 20 times????
thanks so much for your input
-
Originally posted by learning_bee
Dapi,
I also saw that somewhere saying dbms_stats.gather_database_stats would collect stats on SYS which is not a good thing to do. couple questions:
1. if dbms_stats.gather_database_stats collect stats on SYS, so when can you use it???? for what purpose???
2. if I have close to 20 schemas in my database and I want to collect stats for all of them once a week, so I have to submit the job via dbms_submit 20 times????
thanks so much for your input
no.
Use a procecure to wrap them together as dapi suggested
Code:
create or replace procedure gather_stats as
begin
dbms_stats.gather_schema_stats(...);
dbms_stats.gather_schema_stats(...);
dbms_stats.gather_schema_stats(...);
dbms_stats.gather_schema_stats(...);
end gather_stats;
/
declare
var j number
begin
dbms_job.submit
(
:j,
sys.gather_stats,
SYSDATE,
'NEXT_DAY(trunc(sysdate),''SATURDAY'')+1/12'
);
COMMIT;
end;
/
as for system stats, sometimes they can be useful - other times not
-
Originally posted by davey23uk
no.
as for system stats, sometimes they can be useful - other times not
sorry to come back to this question again, when could be good to run system stats????
-
thanks Davey,
when I run the below proc,
create or replace procedure gather_stats as
begin
dbms_stats.gather_schema_stats(...);
dbms_stats.gather_schema_stats(...);
dbms_stats.gather_schema_stats(...);
dbms_stats.gather_schema_stats(...);
end gather_stats;
/
I got an error about "Error: ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at line 1, Batch 1 Line 1 Col 1"
does the user who own this proc has to have SYSDBA privilege??? what privilege do I need to run this proc???
thanks,
-
I got it.
grant analyze any and grant select any table to the user.
thanks all
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|