ORA-12008 y ORA-904 al refrescar Vistas Materializadas

Hace unos días saltó un error al confirmar una inserción en Oracle. El error se producía a la hora de refrescar una vista materializada en modo “fast refresh on commit”. Pero la descripción del error no ofrecía mucha información:

ORA-12008: error in materialized view refresh path
ORA-00904: "<column_name>": invalid identifier

En el error 904 nos informaba de una de las columnas de la tabla sobre la que está construida la vista materializada. Pero esta columna no tenía ninguna característica particular: es de tipo varchar2 (no de un tipo de datos complejo), no está indexada, no tiene referencias externas con otras tablas; además, en la sentencia INSERT que daba el error venía informada con datos admisibles. ¿Qué puede estar ocurriendo?

Después de lanzar varios refrescos de la vista materializada, cambiando distintas opciones sin que nos dieran error, y comprobando que las inserciones seguían fallando: ¿por dónde seguimos investigando?

Comenzamos a realizar búsquedas en MOS con los códigos de error, acotando por versión de la base de datos, y dimos con la nota ID 1331516.1 “Errors ORA-12008 And ORA-904 Invalid Identifier While Fast Refreshing”.

La nota reconoce este comportamiento como un bug de las versiones 11.2 que se corrigen en la 12.1. Bueno, esto ya es algo. El problema ahora es que nuestra base de datos ya está en versión 12. En teoría, el bug estaba corregido.

De todas formas, revisamos la nota completa y nos llamó la atención el parche temporal que proponían:

alter system set "_replace_virtual_columns" = FALSE;

¿Hay que desactivar el reemplazo de columnas virtuales? Nosotros no utilizamos columnas virtuales en la tabla sobre la que se construye la vista materializada. ¿O si? Vamos a averiguarlo:

select * from user_tab_cols where table_name = '<table_name>' and virtual_column = 'YES';

Pues resulta que si tenemos columnas virtuales. Y, ¿de dónde han salido? El nombre de las columnas es un nombre generado por el sistema. Así que alguna funcionalidad interna de Oracle está creando columnas virtuales en la tabla. Y, tal vez, estén interfiriendo en el refresco de las vistas materializadas. Para salir de dudas, buscamos la forma de eliminar las columnas virtuales y damos con el post de Chris Saxon sobre el misterio de las columnas virtuales:

https://blogs.oracle.com/sql/ora-54033-and-the-hidden-virtual-column-mystery

Parece ser que las estadísticas extendidas de Oracle han decidido crear estas columnas virtuales para mejorar sus reportes de rendimiento. Y una de ellas contiene la columna que se refería en el error ORA-00904. Como no creemos en las casualidades, y esto ya es mucha casualidad, decidimos eliminar la columna virtual sospechosa. En este mismo artículo explican cómo hacerlo:

exec dbms_stats.drop_extended_stats(user, '<table_name>', '(<x, y>)');

Y, efectivamente, el refresco de la vista materializada ha vuelto a funcionar.

Anuncios

Bang for the buck(et)!

dbdev01555

On numerous occasions I said: Why are we doing that ? We already have it!

Customers pay serious money for their Oracle Database. The problem is that neither the customer nor the developers tend to know the product. They still think the Oracle database is…well just a very very very expensive bucket. I tried to find a picture which actually shows what you bought. Couldn’t find any, so I made my own.Not pretty but’s that’s not the point. 🙂

If you are running Oracle Enterprise Edition 11g/12c this is what you roughly have without any extra licenses. Know what you pay for. Use what you pay for.

db-features

On purpose , the level of detail is restricted in this picture. Also Oracle Streams “cannot be used” with multitenant container databases (CDBs) or pluggable databases (PDBs). Too bad, I guess Golden Gate is the replacer for that?

If I missed out any obvious…

Ver la entrada original 6 palabras más

Oracle 18c: automatización de las tareas de administración

En la presentación realizada por Larry Ellison en Oracle OpenWorld 2017 ha destacado que la nueva versión de la base de datos Oracle 18c incorporará la capacidad de realizar la siguientes tareas de manera autónoma:

  • Aprovisionamiento
  • Copias de seguridad
  • Parcheado
  • Actualizaciones

Entre otras tareas de administración menores. Los procesos que van a gestionar estas tareas se basan en algoritmos Machine Learning (ML) que permiten a la base de datos adaptarse a distintos escenarios y mejorar sus prestaciones.

Los detalles de la presentación han sido publicados en el siguiente enlace: Larry Ellison on Oracle’s “self-driving” database.

Oracle’s Machine Learning and Advanced Analytics 12.2 and Oracle Data Miner 4.2 New Features | Oracle Data Mining (ODM) Blog

Presentación de las nuevas funcionalidades incorporadas en Oracle 12.2 para trabajar con Machine Learning y análisis de datos a través de Oracle’s Machine Learning and Advanced Analytics 12.2 and Oracle Data Miner 4.2 New Features | Oracle Data Mining (ODM) Blog

Tratamiento de NIF españoles (parte 2)

Una expresión regular para tratar los NIF españoles eliminando todos los caracteres que no sean números o letras. Además, comprueba el tamaño del NIF para eliminar el código de país (identificador fiscal comunitario) si fuera necesario:

select result1 ORIGINAL_TAXID, 
    upper(
        regexp_replace( 
            regexp_replace(result1, '[^[:alnum:]]', ''), -- Remove non-digit and non-alphabetic characters 
            '^(ES)([[:alnum:]]{9})$', -- Identify spanish NIFs with country code 
            '\2')) CLEAN_AND_NATIONAL_TAXID -- Remove spanish country code 
from ( 
    WITH test AS
     (SELECT 'ESA45678901,B23456789,GB3456789,12345678L,B234567890123,ES-A45678901,B.23456789,GB3::456789,B234_567_890123' col1 FROM dual) 
    SELECT regexp_substr(col1, '[^,]+', 1, rownum) result1 
      FROM test 
    CONNECT BY LEVEL <= regexp_count(col1, ',') + 1);

El resultado es:

ORIGINAL_TAXID	CLEAN_AND_NATIONAL_TAXID
--------------  ------------------------
ESA45678901	A45678901
B23456789	B23456789
GB3456789	GB3456789
12345678L	12345678L
B234567890123	B234567890123
ES-A45678901	A45678901
B.23456789	B23456789
GB3::456789	GB3456789
B234_567_890123	B234567890123

En el artículo Tratamiento de NIF españoles se muestra una expresión regular para añadir el código de país.

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

Optimización de cambios de contexto entre SQL y PL/SQL gracias a PRAGMA UDF en Oracle 12c.

Martin Widlake's Yet Another Oracle Blog

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

{please see this second post on some limitations of pragma UDF in respect of IN & RETURN data types and parameter defaults}.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.

A problem with this, though, is that every time you swap from SQL to PL/SQL (or the other way around) you have to do a context switch each time, which can be quite cpu and memory intensive. If you are using your own PL/SQL function in the SELECT list of a SQL statement and…

Ver la entrada original 814 palabras más