Home » SQL & PL/SQL » SQL & PL/SQL » error in using dbms_stats.gather_table_stats
- error in using dbms_stats.gather_table_stats [message #112128] Wed, 23 March 2005 03:01 Go to next message
dassudip
Messages: 22
Registered: February 2005
Location: Delhi
Junior Member
Hi,
I am facing a strange problem while using dbms_stats.gather_table_stats. Staps that I followed are as follows:

1. connected to system schema
2. SQL>exec dbms_stats.gather_table_stats('BOI','CLIENT');
PL/SQL procedure successfully completed.
3. SQL>exec dbms_stats.gather_table_stats('BOI','CLIENT', 'UNDER300000000');
*
ERROR at line 1 :
ORA-20000 Unable to analyze table "BOI"."CLIENT" UNDER300000000 insufficient provileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 9127
ORA-06512: at "SYS.DBMS_STATS", line 9150
ORA-06512: at line 1


here 'BOI' is the schema, CLIENT is the table and UNDER300000000 is the range partition in that table

I saw partition does exist in dba_tab_partitions

Regards,

Sudiopta Das

Report message to a moderator

- Re: error in using dbms_stats.gather_table_stats [message #112137 is a reply to message #112128] Wed, 23 March 2005 03:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Are you sure that you have the correct partition name? Your code works fine for me. Using a non-existent partition name produces the error that you are getting. Please see the demonstration below.

Code: [Select all] [Show/ hide]
scott@ORA92> create table client
  2    (col1 number)
  3    partition by range (col1)
  4  	 (partition under300000000 values less than (300000000),
  5  	  partition final values less than (maxvalue))
  6  /

Table created.

scott@ORA92> select partition_name from user_tab_partitions where table_name = 'CLIENT'
  2  /

PARTITION_NAME
------------------------------
UNDER300000000
FINAL

scott@ORA92> exec dbms_stats.gather_table_stats ('SCOTT', 'CLIENT')

PL/SQL procedure successfully completed.

scott@ORA92> exec dbms_stats.gather_table_stats ('SCOTT', 'CLIENT', 'UNDER300000000')

PL/SQL procedure successfully completed.

scott@ORA92> exec dbms_stats.gather_table_stats ('SCOTT', 'CLIENT', 'NONEXISTENT_PARTITION')
BEGIN dbms_stats.gather_table_stats ('SCOTT', 'CLIENT', 'NONEXISTENT_PARTITION'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SCOTT"."CLIENT" NONEXISTENT_PARTITION, insufficient privileges
or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 9127
ORA-06512: at "SYS.DBMS_STATS", line 9150
ORA-06512: at line 1


scott@ORA92>


Report message to a moderator

- Re: error in using dbms_stats.gather_table_stats [message #112174 is a reply to message #112137] Wed, 23 March 2005 08:30 Go to previous messageGo to next message
dassudip
Messages: 22
Registered: February 2005
Location: Delhi
Junior Member
Hi Barbara,
Thanks a lot for your reply. I also tried the same
sql > select partition_name from user_tab_partitions where table_name = 'CLIENT';

PARTITION_NAME
------------------------------
UNDER300000000
UNDER600000000
UNDER900000000
UNDER120000000
UNDER150000000

again from system schema

sql> exec dbms_stats.gather_table_stats ('BOI', 'CLIENT',
'UNDER300000000');
same error appears but -

sql> exec dbms_stats.gather_table_stats ('BOI', 'CLIENT')
procedure successfully completed

Now connect to boi schema:

sql> analyze table client partition(under3000000) compute statistics;
Table analyzed.


If analyze table works then dbms_stats should work. I am not getting what is the problem.

Regrds,

Sudipta Das.

Report message to a moderator

- Re: error in using dbms_stats.gather_table_stats [message #112180 is a reply to message #112128] Wed, 23 March 2005 08:55 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I'm not sure I'd be using the sys or system schemas for any kind of normal work in the database. Create yourself another account to be used for dba type activities.

Report message to a moderator

- Re: error in using dbms_stats.gather_table_stats [message #112201 is a reply to message #112174] Wed, 23 March 2005 12:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
I am baffled. I re-tested while connected as system and it still works for me, as demonstrated below.

Code: [Select all] [Show/ hide]
scott@ORA92> create table client
  2    (col1 number)
  3    partition by range (col1)
  4  	 (partition under300000000 values less than (300000000),
  5  	  partition final values less than (maxvalue))
  6  /

Table created.

scott@ORA92> select partition_name from user_tab_partitions where table_name = 'CLIENT'
  2  /

PARTITION_NAME
------------------------------
UNDER300000000
FINAL

scott@ORA92> connect system as sysdba
Connected.
scott@ORA92> @ login
scott@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys@ORA92

sys@ORA92> exec dbms_stats.gather_table_stats ('SCOTT', 'CLIENT', 'UNDER300000000')

PL/SQL procedure successfully completed.

Report message to a moderator

- Re: error in using dbms_stats.gather_table_stats [message #112202 is a reply to message #112174] Wed, 23 March 2005 12:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
It just dawned on me that the error number that you are getting, ORA-20000, is within the range of user-defined error numbers, which means that it is being generated by a call to raise_application_error. So, there must be some user-defined code that is raising the error, like a trigger. I suggest that you check for triggers on the table, etc.

Report message to a moderator

- Re: error in using dbms_stats.gather_table_stats [message #112203 is a reply to message #112174] Wed, 23 March 2005 12:24 Go to previous message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
I just found out that this happens with Bug 3026474. If you have access to Metalink, you can check it out, or if you have Oracle support, you can contact them.

Report message to a moderator

Previous Topic: Books for oracle
Next Topic: how to select column_name depending on its value
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jul 27 09:03:08 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.