OraFAQ Forum: Server Administration » DBMS_STATS.GATHER

Home » RDBMS Server » Server Administration » DBMS_STATS.GATHER_STATS fails (oracle 9i, 9.2.0.4.0,Solaris 5.9(SUN SPARC-64 bit))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DBMS_STATS.GATHER_STATS fails [message #387348]

Wed, 18 February 2009 23:06

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

Report message to a moderator

Re: DBMS_STATS.GATHER_STATS fails [message #387353 is a reply to message #387348]

Wed, 18 February 2009 23:28

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: DBMS_STATS.GATHER_STATS fails [message #387361 is a reply to message #387353]

Thu, 19 February 2009 00:06

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,

Report message to a moderator

Re: DBMS_STATS.GATHER_STATS fails [message #419653 is a reply to message #387361]

Mon, 24 August 2009 16:21

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

Report message to a moderator

Previous Topic:	block cleanout
Next Topic:	Fragmentation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 03:47:43 CDT 2024