Home » RDBMS Server » Server Administration » DBMS_STATS.GATHER_STATS fails (oracle 9i, 9.2.0.4.0,Solaris 5.9(SUN SPARC-64 bit))
DBMS_STATS.GATHER_STATS fails [message #387348] Wed, 18 February 2009 23:06 Go to next message
ajaysharma2907
Messages: 14
Registered: June 2008
Location: Delhi
Junior Member

Hi Team,

I am getting the following error in the stats gathering job.
++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++

18-FEB-2009 10:00:01 PM

BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('BSES',GRANULARITY=>'ALL',CASCADE=>TRUE , OPTIONS=>'GATHER AUTO'); END;

*
ERROR at line 1:
ORA-00904: "T2"."SYS_DS_ALIAS_1": invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 10070
ORA-06512: at "SYS.DBMS_STATS", line 10553
ORA-06512: at "SYS.DBMS_STATS", line 10718
ORA-06512: at "SYS.DBMS_STATS", line 10805
ORA-06512: at "SYS.DBMS_STATS", line 10782
ORA-06512: at line 1

++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++++++++++++

Any idea for the solution, or if it was a known BUG.

Kindly Help..

Regards,
Ajay Sharma
Re: DBMS_STATS.GATHER_STATS fails [message #387353 is a reply to message #387348] Wed, 18 February 2009 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
or if it was a known BUG.

Did you search on Metalink or on Google?

Regards
Michel
Re: DBMS_STATS.GATHER_STATS fails [message #387361 is a reply to message #387353] Thu, 19 February 2009 00:06 Go to previous messageGo to next message
ajaysharma2907
Messages: 14
Registered: June 2008
Location: Delhi
Junior Member

same error on 10g revealed a bug, but could not get any on 9i.

Regards,
Re: DBMS_STATS.GATHER_STATS fails [message #419653 is a reply to message #387361] Mon, 24 August 2009 16:21 Go to previous message
starsky
Messages: 5
Registered: September 2008
Junior Member
We've been getting this same error, & I think we can attribute it to having a Function-Based index on the table. At least, it's the only table that we have these indexes on. The weird thing is, if we drop & recreate the index, then the stats gathering job works again:

SQL> begin
  2  
  3  dbms_stats.gather_table_stats( 
  4  ownname=> 'EMPR59', 
  5  tabname=> 'POLICY' , 
  6  estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
  7  cascade=> DBMS_STATS.AUTO_CASCADE, 
  8  degree=> null, 
  9  no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
 10  granularity=> 'AUTO', 
 11  method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
 12  
 13  end;
 14  /
begin
*
ERROR at line 1:
ORA-00904: "T2"."SYS_DS_ALIAS_1": invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 12192
ORA-06512: at "SYS.DBMS_STATS", line 12211
ORA-06512: at line 3

SQL> drop INDEX EMPR59.POLICYIX1;

Index dropped.

SQL> begin
  2  
  3  dbms_stats.gather_table_stats( 
  4  ownname=> 'EMPR59', 
  5  tabname=> 'POLICY' , 
  6  estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
  7  cascade=> DBMS_STATS.AUTO_CASCADE, 
  8  degree=> null, 
  9  no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
 10  granularity=> 'AUTO', 
 11  method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
 12  
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX EMPR59.POLICYIX1 ON POLICY
  2  (LEAST("POLICY_DATE",NVL("BINDER_DATE",TO_DATE('9999-12-12 00:00:00', 'yyyy
-mm-dd hh24:mi:ss')),NVL("CANCEL_DATE",TO_DATE('9999-12-12 00:00:00', 'yyyy-mm-dd hh24:mi:
ss')),NVL("DELETE_DATE",TO_DATE('9999-12-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss')),NVL
("RENEWAL_DATE",TO_DATE('9999-12-12 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))));

Index created.

SQL> begin
  2  
  3  dbms_stats.gather_table_stats( 
  4  ownname=> 'EMPR59', 
  5  tabname=> 'POLICY' , 
  6  estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
  7  cascade=> DBMS_STATS.AUTO_CASCADE, 
  8  degree=> null, 
  9  no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
 10  granularity=> 'AUTO', 
 11  method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
 12  
 13  end;
 14  /

PL/SQL procedure successfully completed.




--=Chuck
version 10.1.0.4
Previous Topic: block cleanout
Next Topic: Fragmentation
Goto Forum:
  


Current Time: Fri Dec 09 13:30:19 CST 2016

Total time taken to generate the page: 0.06017 seconds