Falla al coleccionar estadisticas para un esquema ( ORA-20000 y ORA-06512 Statistics collection failed for all objects in schema )

Aqui de regreso con un post mas, estos ultimos dos meses he estado algo ocupado por eso no he podido mantener el paso de 1 post por semana, pero aqui tengo uno que si se lo topan les puede ayudar en un futuro o un problema actual.

Tengo un proceso personalizado que corre diario buscando objetos que no tienen estadisticas, este proceso llevaba corriendo mas de un año sin ningun problema, y hace unos pocos dias empezo a fallar y ahora si que me agarro por sorpresa este error, pero pudimos arreglarlo despues de investigar un rato.

Lo que sucedio fue que dias atras se corrio un proceso con Datapump, si no conoces esta herramienta de Oracle, es usada para exportar/importar informacion; Este proceso fallo y algunas veces cuando eso sucede, deja una tabla externa, que eso era lo que estaba causando que fallara la coleccion de estadisticas para mi base de datos.

Para poder encontrar y solucionar el error hicimos lo siguiente:
Alteramos nuestra sesion para generar trace del error que estaba marcando, una vez que alteramos la sesion, corremos la generacion de estadisticas.

DBATEST >alter session set events '6512 trace name errorstack level 3';

Session altered.

DBATEST >begin
2 dbms_stats.gather_schema_stats( ownname => 'HR',
3 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
4 degree => 8,cascade => TRUE,
5 options => 'GATHER EMPTY',
6 method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
7 end;
8 /
*
ERROR at line 1:
ORA-20000: Statistics collection failed for all objects in schema
ORA-06512: at "SYS.DBMS_STATS", line 24189
ORA-06512: at "SYS.DBMS_STATS", line 24130
ORA-06512: at line 2

Como podemos ver en el trace, aqui es donde nos esta diciendo que la tabla externa es la causante de nuestro problema

----- Error Stack Dump -----
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.
ORA-06512: at "SYS.DBMS_STATS", line 20184

Siguiendo la nota de MOS 336014.1, pudimos limpiar esta tabla, lo primero es ver si el proceso de Datapump no esta realmente corriendo:

DBATEST >SET lines 200
DBATEST >COL owner_name FORMAT a10;
DBATEST >COL job_name FORMAT a20
DBATEST >COL state FORMAT a11
DBATEST >COL operation LIKE state
DBATEST >COL job_mode LIKE state

-- localizar jobs de Data Pump :

DBATEST >SELECT owner_name, job_name, operation, job_mode,
2 state, attached_sessions
3 FROM dba_datapump_jobs
4 WHERE job_name NOT LIKE 'BIN$%'
5 ORDER BY 1,2;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ----------- ----------- ----------- -----------------
HR SYS_IMPORT_FULL_01 IMPORT FULL NOT RUNNING 0

Despues pasamos a encontrar la tabla maestra de datapump

DBATEST >SELECT o.status, o.object_id, o.object_type,o.owner||'.'||object_name "OWNER.OBJECT"
2 FROM dba_objects o, dba_datapump_jobs j
3 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
4 AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;


STATUS OBJECT_ID OBJECT_TYPE
--------------------- ----------
OWNER.OBJECT
-------------
VALID 44461 TABLE
HR.SYS_IMPORT_FULL_01

Una vez que localizamos la tabla maestra del job, tiramos la tabla, si llegaras a encontrar mas de una tabla, antes de tirarla, cerciorate que estas sean de un proceso de Datapump fallido.

DBATEST >drop table HR.SYS_IMPORT_FULL_01;

Table dropped.

Ahora pasamos a encontrar la tabla externa y de la misma manera tirarla.

DBATEST >set linesize 200 trimspool on
DBATEST >set pagesize 2000
DBATEST >col owner form a30
DBATEST >col created form a25
DBATEST >col last_ddl_time form a25
DBATEST >col object_name form a30
DBATEST >col object_type form a25
DBATEST >
DBATEST >select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
2 to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
3 ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
4 from dba_objects
5 where object_name like 'ET$%'
6 /

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME
------------------------------ ------------------------------ ------------------------- --------------------- -------------------------
HR ET$01A305940001 TABLE VALID 27-apr-2011 13:32:45 28-apr-2010 08:15:28

DBATEST >select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
2 from dba_external_tables
3 order by 1,2
4 /

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_TYPE
------------------------------ ------------------------------ ------------------------------ ---------------------
HR ET$01A305940001 DATA_PUMP CLOB

DBATEST >spool off

DBATEST >drop table HR.&1 purge;
Enter value for 1: ET$01A305940001
old 1: drop table HR.&1 purge
new 1: drop table HR.ET$01A305940001 purge

Table dropped.

Una vez que completamos los pasos mencionados arriba, volvemos a correr las estadisticas,y vamos a ver que el proceso termina sin ningun error.

DBATEST >begin
2 dbms_stats.gather_schema_stats( ownname => 'HR',
3 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
4 degree => 8,cascade => TRUE,
5 options => 'GATHER EMPTY',
6 method_opt => 'FOR ALL COLUMNS SIZE AUTO' );
7 end;
8 /

PL/SQL procedure successfully completed.

Conclusion
Como podemos ver, aunque es simple la solucion, poder encontrar el problema y solucionarlo nos puede llevar algo de tiempo, asi como hay que asegurarnos siempre que falle un proceso de Datapump, limpiar las tablas que genera, aunque este deberia ser algo que Oracle deberia de hacer internamente, pero al dia de hoy no lo hace, asi que con cuidado y espero que te sirva esta entrada.