Skip navigation

Oracle Database 18c.png

Desde Oracle 8i es posible crear Tablas temporales en Oracle, aunque la definición de Temporal en esos casos es relativa. Si bien el "contenido" (los datos) de dichas tablas es temporal, su definición no lo es.

 

Las tablas temporales tal cual las conocemos, llamadas Global Temporary Table, son objectos de diccionario de datos, por lo que su definición es almacenada en el mismo y persiste, no así con el contenido de las mismas.

 

¿Que son las tablas temporales privadas?

A diferencia de las tablas temporales globales, las tablas temporales privadas no se almacenan en el diccionario de datos, su definición reside en memoria en la PGA (Process Global Area, el area de memoria destinada a mantener valores de cada sesión en Oracle) por lo que son eliminadas en forma automática ya sea al hacer commit (opción por defecto) o bien al finalizar la sesión que la crea.

 

Al ser objectos que existen sólo en memoria y no se encuentran almacenados en el Diccionario de Datos, no pueden ser utilizados en forma directa en objectos PL/SQL (procedimientos, funciones) ni en vistas, ya que su definición es "volátil". Si necesitamos utilizar una tabla temporal privada dentro de un procedimiento o función, deberemos crear la misma y utilizarla mediante código dinámico.

 

¿Como se crea una tabla temporal privada?

La sintaxis para crear una tabla temporal privada es la siguiente:

 

CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction 
(time_id      DATE,
amount_sold  NUMBER(10,2))
[ON COMMIT [DELETE | PRESERVE] ROWS]
ON COMMIT [DROP|PRESERVE] DEFINITION;

 

La primer cosa a considerar es el nombre de las tablas temporales privadas. Las mismas deben comenzar con un prefijo específico, el cual se define en el parámetro "PRIVATE_TEMP_TABLE_PREFIX", y cuyo valor por defecto es "ORA$PTT_".

 

Por otro lado, al declarar una tabla temporal privada deberemos especificar la "duración" de la misma, con la clausula "ON COMMIT [DROP|PRESERVE] DEFINITION". Por defecto, la tabla es eliminada (tanto su definición como su contenido) cuando se ejecuta un COMMIT en la sesión que la creó. Pero se puede preservar la existencia de la misma incluyendo la clausula "ON COMMIT PRESERVE DEFINITION".

 

La opción "ON COMMIT [DELETE|PRESERVE] ROWS" se mantiene igual que en la sintaxis de las tablas temporales globales.

 

Restricciones de las Tablas Temporales Privadas

De por si, las tablas temporales poseen un conjunto de restricciones que detallamos a continuación.

 

  • Las tablas temporales no puede ser particionadas, ni de tipo Cluster o Index Organized.
  • No se pueden definir claves foráneas sobre las tablas temporales.
  • Las tablas temporales no pueden contener columnas de tipo tabla anidada.
  • No se puede realizar UPDATE, DELETE ni MERGE en paralelo en las tablas temporales.
  • La única parte de la clausula de atributos de segmento que se puede especificar para las tablas temporales es la option TABLESPACE, la cual debe referenciar a un único tablespace de tipo temporal.
  • Las tablas temporales no pueden ser utilizadas en transacciones distribuidas.
  • Las tablas temporales no pueden contener columnas de tipo INVISIBLE.

 

 

Cuando trabajamos con tablas temporales privadas debemos tener en cuenta las siguientes restricciones adicionales:

 

  • No se puede crear índices, vistas materializadas con tablas temporales privadas.
  • No se puede asignar un valor por defecto a las columnas de una tabla temporal privada.
  • No se puede hacer referencia a tablas temporales privadas en objetos permanentes, como ser vistas o triggers.
  • Las tablas temporales privadas no son visibles a trabes de database links.
  • Al no almacenarse su definición en el diccionario de datos, no es factible ver las mismas en las vistas usuales del diccionario de datos, como ser "USER_TABLES". Para poder conocer las tablas temporales privadas existentes en un momento dado se pueden consultar estas dos nuevas vistas:

 

¿Cuando usar tablas temporales privadas?

Un ejemplo de cuando utilizar una tabla temporal privada es en una base de datos en modo READ ONLY, ya que la misma no es almacenada en el diccionario de datos.

Oracle Database 18c.png

Tablas Externas "Inline"

¿Que son las tablas externas?

Las tablas externas existen en Oracle desde la versión 9i, y permiten acceder a información contenida en archivos de texto como si fueran tablas almacenadas dentro de la base de datos.

 

Las mismas fueron adquiriendo nuevas características con el paso del tiempo. Inicialmente, eran de solo lectura, pero desde Oracle 10g se puede realizar modificaciones a las mismas.

 

En Oracle 12.2 es posible “particionar” el contenido de las tablas externas, con lo cual podemos ver varios archivos como si fueran una única tabla, y también es posible modificar algunos parámetros (como ser el directorio por defecto o los nombres de archivo) directamente al momento de escribir una consulta, sin necesidad de modificar la definición de la tabla en forma permanente.

 

¿Qué es lo nuevo en Tablas Externas en Oracle 18c?

Oracle 18c introduce la opción de Tablas Externas "INLINE", lo que traducido a conceptos básicos significa que la definición de la tabla externa se puede hacer directamente en la sentencia que la va a utilizar, sin necesidad de que la misma exista previamente.

 

SELECT Pais, Provincia, Sucursal, MontoMensual 
FROM   EXTERNAL (
                    ( Pais    VARCHAR2(30),
                      Provincia VARCHAR2(30),
                      Sucursal  VARCHAR2(30),
                      MontoMensual NUMBER(18,4) )
                    TYPE oracle_loader
                    DEFAULT DIRECTORY datos_externos
                    ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE
                                        BADFILE datos_externos
                                        LOGFILE datos_externos:'inline_ext_tab_%a_%p.log'
                                        DISCARDFILE datos_externos
                                        FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
                                        MISSING FIELD VALUES ARE NULL ( Pais, Provincia, Sucursal, MontoMensual )
               ) LOCATION ('ventas_argentina.txt', 'ventas_chile.txt', 'ventas_uruguay.txt')
               REJECT LIMIT UNLIMITED ) inline_ext_tab
ORDER BY Pais, Provincia, Sucursal;

 

Si bien el código no queda muy elegante, permite acceder a datos externos (que sean accesibles para la base de datos) sin necesidad de crear un objeto de tipo tabla, lo cual puede ser muy útil en bases de datos o esquemas de solo lectura.

 

La sintraxis completa puede ser consultada aqui y aqui.

Oracle Database 18c.png

ALTER SYSTEM CANCEL

A partir de la version 18c, es posible cancelar una sentencia ejecutándose en la base de datos sin necesidad de terminar (matar) a la sesión que la está ejecutando.

 

Para ello, Oracle 18c introduce una nueva opción "CANCEL" en la sentencia "ALTER SYSTEM", la cual tiene la siguiente sintaxis:

 

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL, [, @INST_ID] [, SQL_ID]’;

 

Los valores de "SID" y "SERIAL" son idénticos a los que se utilizan históricamente para terminar una sesión utilizando "ALTER SYSTEM KILL". La opción "CANCEL" nos permite indicar dos parámetros adicionales:

 

  • @INST_ID: Indica la instancia donde la sesión se está ejecutando, y va siempre precedida del símbolo @. Si no se especifica un valor, Oracle asume que la sentencia a cancelar se está ejecutando en la misma instancia donde se ejecuta la sentencia "ALTER SYSTEM CANCEL".
  • SQL_ID: Indica el identificador de la sentencia SQL que deseamos cancelar. Si no se especifica un valor, la sentencia que se esté ejecutando al momento de hacer el "ALTER SYSTEM CANCEL" será cancelada.

 

Para obtener los datos de las sesiones que están activas, se puede ejecutar la siguiente sentencia:

 

SELECT s.sid,
       s.serial#,
       s.sql_id,
       s.username,
       s.program,
       s.status
FROM v$session s
WHERE s.status = 'ACTIVE';

 

 

 

En entornos RAC, se debe usar la vista global "gv$session" e incluir la columna "inst_id" en los resultados:
SELECT  s.sid,
        s.serial#,
        s.inst_id,
        s.sql_id,
        s.username,
        s.program
FROM   gv$session s
WHERE s.status = 'ACTIVE';

Se puede consultar la documentación oficial aquí.