Archivo del Autor: PabloE

Bang for the buck(et)!


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.


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, 
            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:

--------------  ------------------------
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.

Sharing a tablespace between 2 databases

Una alternativa para compartir datos maestros o históricos entre distintas bases de datos Oracle sin replicar los datos.

Learning is not a spectator sport

I was reading an interesting discussion today about multiple databases each containing large amounts of read-only data. If that read-only data is common, then it would make sense to have a single copy of that data and have both databases share it.

Well, as long as you can isolate that data into its own tablespace, then you can do that easily with Oracle by transporting the metadata between two databases and leaving the files in place.

Here’s an example

Source database

 SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release - 64bit Production PL/SQL Release - Production CORE Production TNS for 64-bit Windows: Version - Production NLSRTL Version - Production SQL> create tablespace i_am_on_121 datafile 'C:oracleoradatattsmy_tspace' size 50m; Tablespace created. SQL> create table t tablespace i_am_on_121 as select * from dba_objects; Table created. SQL> alter tablespace i_am_on_121 read only; Tablespace altered…

Ver la entrada original 359 palabras má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

Benefits of Oracle Sharding

Oracle nos proporciona con la versión 12.2 una nueva forma de escalar servicios horizontalmente a través de la distribución de la base de datos en nodos independientes.

Distributed Database Technologies

In this post, we will take a look at the advantages of Oracle Sharding.

  • Linear scalability with complete fault isolation. OLTP applications designed for Oracle sharding can elastically scale (data, transactions and users) to any level, on any platform, simply by deploying new shards on additional stand-alone servers. The unavailability or slowdown of a shard due to either an unplanned outage or planned maintenance affects only the users of that shard, it does not affect the availability or performance of the application for users of other shards. Upon the unavailability of a shard, failover is initiated automatically to another copy of the data. Each shard may run a different release of the Oracle Database as long as the application is backward compatible with the oldest running version – making it simple to maintain availability of an application while performing database maintenance.


  • Global data distribution for data proximity – to…

Ver la entrada original 113 palabras más