dbms_stats - GATHER AUTO option - ORA-00933 ?

Giganews Newsgroups
Subject: dbms_stats - GATHER AUTO option - ORA-00933 ?
Posted by:  Martin T (bilbothebagginsba…@freenet.de)
Date: 6 Nov 2006

Hi all.
(Oracle 9.2.0.1.0)

I'm currently trying around with gathering statistics.
I wanted to try with monitoring and the gather auto option, but:

begin
dbms_output.put_line('Start: ' || systimestamp);
dbms_stats.gather_schema_stats(ownname => 'MYSCHEMA', options =>
'GATHER');
dbms_output.put_line('Stop: ' || systimestamp);
end;
--> OK

begin
dbms_output.put_line('Start: ' || systimestamp);
dbms_stats.gather_schema_stats(ownname => 'MYSCHEMA', options =>
'GATHER AUTO');
dbms_output.put_line('Stop: ' || systimestamp);
end;
-->
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_STATS", line 7684
ORA-06512: at "SYS.DBMS_STATS", line 9624
ORA-06512: at "SYS.DBMS_STATS", line 9777
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 3

begin
dbms_output.put_line('Start: ' || systimestamp);
dbms_stats.gather_schema_stats(ownname => 'MYSCHEMA', options =>
'GATHER SOMETHING_ELSE');
dbms_output.put_line('Stop: ' || systimestamp);
end;
-->
ORA-20001: Illegal option GATHER SOMETHING_ELSE: must be GATHER |
GATHER STALE | GATHER EMPTY | LIST STALE | LIST EMPTY
ORA-06512: at "SYS.DBMS_STATS", line 9689
ORA-06512: at "SYS.DBMS_STATS", line 9797
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 3

.... so the docs state there should be this option, and also if I use a
'really' invalid one then I get a meaningful exception ... but if I use
'GATHER AUTO' then I get an ORA-933.
I have tried with both monitoring on and off.

Any ideas what I'm doing wrong?

thanks,
Martin

Replies