gather stats ora-20000

[Database Administrator]

I've been trying to execute code ( logged on as user LIVE ) to gather
system stats & get the following

BEGIN
*
ERROR at line 1:
ORA-20000: Unable to analyze INDEX "LIVE"."DSX_CODA_C_LINES_DT_IDX_1",
insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 2


I've granted gather_system_stats & analyze any to user ...any ideas?

regards
Chris B

PS The object does exist

PS I've included code below
BEGIN
DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'LIVE',
tabname => 'DSX_CODA_CANCEL_LINES',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 150',
CASCADE => TRUE,
granularity => 'PARTITION');
END;

[Database Administrator]

Which version of Oracle are you using?

I've encountered a bug in 8i (8.1.7) that has resulted in that
exception being thrown for a Local Index on a partitioned table when
using CASCADE => TRUE. I don't personally have access to Metalink at my
current site (I'm a contractor, and not allowed ;o)), but I seem to
remember reading something about this bug somewhere, either on this
forum or on http://asktom.oracle.com.

I got around it by setting CASCADE => FALSE in the
DBMS_STATS.GATHER_TABLE_STATS procedure, and just using the
GATHER_INDEX_STATS procedure instead.

[Database Administrator]

Which version of Oracle are you using?

I've encountered a bug in 8i (8.1.7) that has resulted in that
exception being thrown for a Local Index on a partitioned table when
using CASCADE => TRUE. I don't personally have access to Metalink at my
current site (I'm a contractor, and not allowed ;o)), but I seem to
remember reading something about this bug somewhere, either on this
forum or on http://asktom.oracle.com.

I got around it by setting CASCADE => FALSE in the
DBMS_STATS.GATHER_TABLE_STATS procedure, and just using the
GATHER_INDEX_STATS procedure instead.

[Database Administrator]

chris b wrote:
> I've been trying to execute code ( logged on as user LIVE ) to gather
> system stats & get the following
>
> BEGIN
> *
> ERROR at line 1:
> ORA-20000: Unable to analyze INDEX "LIVE"."DSX_CODA_C_LINES_DT_IDX_1",
> insufficient privileges or does not exist
> ORA-06512: at "SYS.DBMS_STATS", line 9375
> ORA-06512: at "SYS.DBMS_STATS", line 9389
> ORA-06512: at line 2

It isn't telling you that DBMS_STATS doesn't exist ... it is telling
you that the index doesn't exist or you have no privileges to analyze
it.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Database Administrator]

chris b wrote:
> I've been trying to execute code ( logged on as user LIVE ) to gather
> system stats & get the following
>
> BEGIN
> *
> ERROR at line 1:
> ORA-20000: Unable to analyze INDEX "LIVE"."DSX_CODA_C_LINES_DT_IDX_1",
> insufficient privileges or does not exist
> ORA-06512: at "SYS.DBMS_STATS", line 9375
> ORA-06512: at "SYS.DBMS_STATS", line 9389
> ORA-06512: at line 2

It isn't telling you that DBMS_STATS doesn't exist ... it is telling
you that the index doesn't exist or you have no privileges to analyze
it.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Database Administrator]

thanks ...I am trying to find out what this priviledge is!!

Any ideas?

regards
Chris B

[Database Administrator]

thanks ...I am trying to find out what this priviledge is!!

Any ideas?

regards
Chris B

[oratel123]

Is it custom table? If it is please check whether the owner (db user for custom schema) is in fnd_oracle_userid table. This database username should be in that table.

[Gandraphael]

If objects get created while logged in as one user yet gather stats get run for another owner that doesn't own the object for which dbms_stats are being gathered then ora-20000 can occur as shown:

SQL> show user
USER is "SYSTEM"

SQL> CREATE USER XXX IDENTIFIED BY "123XYZ!#$"
2 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
3 PROFILE GENERAL_SECURITY ACCOUNT UNLOCK
4 QUOTA 10M ON USERS ;

User created.

SQL> GRANT "CONNECT" TO XXX ;

Grant succeeded.

SQL> CREATE TABLE T (T_ID NUMBER(10) NOT NULL, T_VALUE VARCHAR2(30 CHAR) NULL )
2 TABLESPACE USERS ;

Table created.

SQL> SET LINES 150
SQL> COL OWNER FOR A11
SQL> COL OBJECT_NAME FOR A11
SQL> COL OBJECT_TYPE FOR A11
SQL> SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED
2 FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE'
3 AND CREATED > TRUNC(SYSDATE) ;

OWNER OBJECT_NAME OBJECT_TYPE CREATED
----------- ----------- ----------- ---------
SYSTEM T TABLE 06-OCT-09

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'XXX',TABNAME=>'T') ;
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'XXX',TABNAME=>'T') ; END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "XXX"."T", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13427
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

The table got create while logged in as SYSTEM so it owns it not user XXX so dbms_stats kicks out ORA-20000.

[giteshtrivedi]

Hi,

Nice discussion with knowledge sharing. Thanks a lot.