Re: dbms_stats - GATHER AUTO option - ORA-00933 ?

"Martin T." <bilbothebagginsbab5_at_freenet.de> a écrit dans le message de news: 1162828211.700575.176920_at_m73g2000cwd.googlegroups.com...
| 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
|

Try to execute
alter session set NLS_NUMERIC_CHARACTERS='.,'; before you gather your statistics.
The procedure does not work if you don't have default numeric characters.

Regards
Michel Cadot Received on Mon Nov 06 2006 - 10:08:50 CST