Home » SQL & PL/SQL » SQL & PL/SQL » What could be wrong with DBMS_STATS
What could be wrong with DBMS_STATS [message #14139] Wed, 11 August 2004 03:17 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi,

I just tried to run execute DBMS_STATS.GATHER_SCHEMA_STATS('Schema',DBMS_STATS_AUTO_SAMPLE_SIZE);

But I got error messages:

SQL> execute dbms_stats.gather_schema_stats('SCHEMA',dbms_stats.auto_sample_size);
BEGIN dbms_stats.gather_schema_stats('SCHEMA',dbms_stats.auto_sample_size); END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 1

Whats wrong with DBMS_STATS ? As far I know the Statistics are installed rightly. What we need to get from Oracle is Statistics for several Tables and Indexes once a night.

Any help welcome

ciao

Uwe

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #14140 is a reply to message #14139] Wed, 11 August 2004 04:14 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You are hitting bug 2968571, I'm afraid. Here's more info:
Facts 
~~~~~ 
Oracle Server - Enterprise Edition 8i, 9i 
SQL>execute DBMS_STATS.GATHER_SCHEMA_STATS (..., - 
> estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE); fails 
SQL>execute DBMS_STATS.GATHER_SCHEMA_STATS (..., - 
> estimate_percent=>'A number with a decimal character like x.y'); fails 

Symptom(s) 
~~~~~~~~~~ 
ORA-6502 PL/SQL: numeric or value error: character to number conversion error in a PL/SQL block 
ORA-933 SQL command not properly ended in sqlplus 

Cause 
~~~~~~~ 
NLS_NUMERIC_CHARACTERS = ',.'(Most European countries) 
As NLS_NUMERIC_CHARACTERS is defined implicitly by NLS_TERRITORY, or NLS_LANG 
check NLS_NUMERIC_CHARACTERS value if you hit this error. 
By example, you'll have this behaviour when : 
NLS_TERRITORY = FRANCE&#124GERMANY&#124SPAIN&#124PORTUGAL&#124NORWAY&#124SWEDEN ... 

Workaround 
~~~~ 
Alter session set NLS_NUMERIC_CHARACTERS='.,' --before executing the dbms_stats command 

Fix 
~~~~ 
Patchset 9.2.0.5 available on Metalink
MHE

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #14142 is a reply to message #14140] Wed, 11 August 2004 05:03 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Thanks Maheer,

this seems to work. I did not actual know if we could take 9.2.0.5 because we're running a hughe ERP System on it and we're not stable with this system now.

I will clarify this
thanks again
Uwe

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #262265 is a reply to message #14140] Sat, 25 August 2007 13:41 Go to previous messageGo to next message
sandip_kate
Messages: 3
Registered: August 2007
Location: MUMBAI
Junior Member
Hi Maheer

I am getting ORA-06502 PL/SQL numeric or value error

The data is from European countries.

I am giving following command.

exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', 30, FALSE, 'FOR ALL COLUMNS SIZE 1', DBMS_STATS.DEFAULT_DEGREE,'DEFAULT', TRUE);

Before giving the above exec command i have run
Alter session set NLS_NUMERIC_CHARACTERS='.,'

After doing the above i am getting the same error like

ORA-06502 PL/SQL numeric or value error
ORA-06512: ved "SYS.DBMS_STATS", line 9136
ORA-06512: ved "SYS.DBMS_STATS", line 9616
ORA-06512: ved "SYS.DBMS_STATS", line 9800
ORA-06512: ved "SYS.DBMS_STATS", line 9854
ORA-06512: ved "SYS.DBMS_STATS", line 9831
ORA-06512: ved line 1

Thanks & Regards

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #262268 is a reply to message #262265] Sat, 25 August 2007 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Copy and paste the execution of:
begin
  execute immediate 'Alter session set NLS_NUMERIC_CHARACTERS='''.,''';
  DBMS_STATS.GATHER_SCHEMA_STATS(
    'SCOTT', 30, FALSE, 'FOR ALL COLUMNS SIZE 1',
    DBMS_STATS.DEFAULT_DEGREE,'DEFAULT', TRUE);
end;
/

Regards
Michel

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #262273 is a reply to message #262268] Sat, 25 August 2007 14:47 Go to previous messageGo to next message
sandip_kate
Messages: 3
Registered: August 2007
Location: MUMBAI
Junior Member
Hi Michel

Thanks for your reply.

If i give the Alter session set NLS_NUMERIC_CHARACTERS='.,' separetely it work successfully.

The problem with the following command

exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', 30, FALSE, 'FOR ALL COLUMNS SIZE 1',

DBMS_STATS.DEFAULT_DEGREE,'DEFAULT', TRUE);

Second thing if i run the following command without parameters it works successfully.
exec DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT');

Thanks & Regards

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #262274 is a reply to message #262273] Sat, 25 August 2007 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't understand what you meant, when it works and when it doesnt'.
Juts copy and paste your session.

Regards
Michel

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #262275 is a reply to message #262274] Sat, 25 August 2007 15:19 Go to previous messageGo to next message
sandip_kate
Messages: 3
Registered: August 2007
Location: MUMBAI
Junior Member
Hi

Thanks for your reply.

The code which u have given it's not working.

I am talking about without using Alter session set NLS_NUMERIC_CHARACTERS='.,' statment if i give (exec DBMS_STATS
without parameter)
exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT') ;
it work successfully.

But if do the following

Alter session set NLS_NUMERIC_CHARACTERS='.,'

exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', 30, FALSE, 'FOR ALL COLUMNS SIZE 1', DBMS_STATS.DEFAULT_DEGREE,'DEFAULT', TRUE);

It's not working.

Thanks

Report message to a moderator

Re: What could be wrong with DBMS_STATS [message #262303 is a reply to message #262275] Sun, 26 August 2007 01:44 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 25 August 2007 21:58
I don't understand what you meant, when it works and when it doesnt'.
Juts copy and paste your session.

Regards
Michel


Use SQL*Plus and apply.

Regards
Michel

Report message to a moderator

Previous Topic: sqlplus connection issue - ora -01017
Next Topic: submit a process without waiting
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 06 16:56:25 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.