DBMS_Stats - Error collecting statistics

[Trevisolli]

Personal, good morning.
Is everything okay?

I'm having trouble collecting statistics here, and I wonder if any language has any tips.

The scenario is as follows:

Bank and SO:

Select all

 
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
PL/SQL Release 11.2.0.3.0 - Production                                       
CORE	11.2.0.3.0	Production                                                   
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production            

] Call the Package DBMS_STATS procedure:

Select all

 
declare 
  wlista   dbms_stats.objecttab; 
begin 
  dbms_stats.gather_database_stats(options =>     'GATHER EMPTY', 
                                   objlist =>     wlista, 
                                   gather_sys =>  false); 
end; 

Error displayed:

Select all

 
ORA-20000: Statistics collection failed for 1 objects in the database 
ORA-06512: at "SYS.DBMS_STATS", line 25058 
ORA-06512: at line 4 

I would like to know if anyone has any solution or, how I look for this object that failed.

Thank you very much.

Trevisolli

[Trevisolli]

Brothers, Good morning ...
I found a solution here:

List the objects that are in the list, with the List Auto option

Select all

 
declare 
  wlista   dbms_stats.objecttab; 
begin 
  dbms_stats.gather_database_stats(options =>     'LIST AUTO', 
                                   objlist =>     wlista, 
                                   gather_sys =>  false); 
 
  for l in 1..wlista.count loop 
    dbms_output.put_line('Objeto: '||wlista(l).objname); 
  end loop;                                    
 
                                    
end; 

In this list, a table should not be collected. I removed the object as it was not used and, OK, the "Gather Empty" has collected correctly.

is there what happened and, open to new tips!

Abra,

Trevisolli

[dr_gori]

Weird.
has a bug almost equal to this, but it is for all and not 1:

Select all

ORA-20000: Statistics collection failed for all objects in database

Select all

Bug 11808462  dbms_stats raises ora-20000: statistics collection failed for all objects in database

I found nothing like With this too

[dr_gori]

Good Tip Trevis!

[Trevisolli]

Thanks dr_gori .

I also searched Metalink and found the DOC ID = 11808462 but, as you said, there is a bug and is not for 1, but for everyone. ORA-20000: Statistics collection failed for all objects in database.

Hence, talking to another DBA here, gave me the tip of "List Auto" and, I saw the table that was causing me the problem.

Worth the brother.

Big hug!

Trevis