Oracle APEX, pequeña introducción

¿Que es APEX?

Oracle Application Express (ApEx) es una herramienta de desarrollo web que permite compartir datos y crear aplicaciones personalizadas de forma rápida. Con ayuda de un explorador web y sin necesidad de contar con conocimientos avanzados de programación, se podrán desarrollar y desplegar potentes aplicaciones, rápidas y seguras.

El desarrollo de aplicaciones con APEXestá basado en el explorador y permite desarrollar aplicaciones desde cualquier PC conectado en red,utilizando para ello tan sólo un explorador web actual. La tecnología APEX se basa y usa como núcleo SQL y PL / SQL. APEX es una herramienta declarativa que proporciona gran cantidad de características diseñadas para hacer el trabajo de desarrollo más fácil. El propio APEX se encarga de muchas de las funciones subyacentes comunes a todas las aplicaciones basadas en web; esto permite al programador centrarse en la lógica específica de la aplicación.

¿Cuáles son los requisitos?

• Conocer sobre consultas SQL (básico) y programación con PL SQL (básico)

• HTML, CSS (no excluyente)

Instalación de Oracle APEX 

Antes de instalar Oracle Application Express (Oracle APEX) es necesario:

• Instalar base de datos Oracle (10g, 11g o 12c)

Estas son las versiones soportadas por Oracle APEX:

• Linux x86
• Linux x86-64
• Oracle Solaris on SPARC (64 bit)
• Oracle Solaris x86-64 (64 bit)
• HP-UX Itanium
• Microsoft Windows (32-bit)
• Microsoft Windows x64 (64-bit)
• IBM AIX on POWER Systems (64-bit)
• IBM: Linux on System z
• HP-UX PA-RISC (64-bit)

Descomprima el archivo zip que ha descargado en alguna posición de la unidad de disco duro. Aquí lo descomprimo en C: / DevProgram

A continuación, introduzca la sentencia sqlplus para iniciar sesión en sqlplus (Nota: no abra sqlplus directamente, sino a través de CMD después de posicionarse en el APEX home)

--
-- Run script apexins.sql with parameters:
--
-- @apexins.sql tablespace_apex tablespace_files tablespace_temp images
--
-- Where:
-- tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
-- tablespace_files is the name of the tablespace for the Oracle Application Express files user.
-- tablespace_temp is the name of the temporary tablespace or tablespace group.
-- images is the virtual directory for Oracle Application Express images.
-- (To support future Oracle Application Express upgrades, define the virtual image directory as /i/.)
--
 
@apexins.sql sysaux sysaux temp /i/

El script anterior crea un SCHEMA en la base de datos, puede probar consultando Ver ALL_USERS en SQLPlus

--
-- Running script apex_epg_config.sql with parameter:
-- @apex_epg_config.sql parent of apex directory
--
 
@apex_epg_config.sql C:\DevPrograms

Habilitar usuario anonymous

--
-- Unlock user anonymous:
 alter user anonymous account unlock;

apexconf.sql 

 Ejecutar script apexconf.sql Se utiliza para realizar los pasos finales de configuración de Oracle Application Express, incluida la configuración del puerto de escucha HTTP XDB ​​y la contraseña ADMIN de Application Express.

--
-- Running script: @apxconf.sql
-- Setup password for ADMIN
-- Configuring XDB Http Listener port
--
 
@apxconf.sql

El usuario, puede usar ADMIN de forma predeterminada y el correo electrónico puede omitirse. Para la contraseña, el sistema requiere que ingrese una contraseña segura: 

• La contraseña no se ajusta a las reglas de complejidad de contraseñas de este sitio. 
• La contraseña debe contener al menos 6 caracteres.
• La contraseña debe contener al menos un carácter alfabético (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ). 
• La contraseña debe contener al menos un carácter de puntuación (! "# $% & ()` '* +, - /:;? _).
• La contraseña debe contener al menos un carácter alfabético en mayúsculas. 
• La contraseña debe contener al menos un carácter alfabético en minúsculas.

El puerto predeterminado es 8080

Vamos a un navegador y tecleamos

• http://localhost:8080/apex/apex_admin

Introducimos clave y usuario.

Java Forensics en bases de datos Oracle

Esta vez quiero mostrar cómo determinar si los ataques de privilegio de Java se han producido en su base de datos, tanto como medida de precaución como para el análisis forense después del incidente, con el fin de verificar si un ataque de esta naturaleza ha ocurrido o no.

En primer lugar veamos la situación en la que un atacante utiliza DBMS_JVM_EXP_PERMS.TEMP_JAVA_POLICY para concederse a sí mismos cualquier privilegio Java de CREATE SESSION debido a ejecutar DBMS_JVM_EXP_PERMS.

Si su base de datos está recogiendo mensajes de error a continuación, busca errores del tipo ORA-29532:

SQL> DECLARE  
  2  POL DBMS_JVM_EXP_PERMS.TEMP_JAVA_POLICY;  
  3  CURSOR C1 IS SELECT 'GRANT','JAVATEST','SYS','java.io.FilePermission','$','execute',
                        'ENABLED' FROM DUAL;  
BEGIN  
OPEN C1;  
  4    5  FETCH C1 BULK COLLECT INTO POL;  
  6  CLOSE C1;  
DBMS_JVM_EXP_PERMS.IMPORT_JVM_PERMS(POL);  
  7    8  END;  
  9  / 10  
DECLARE  
*  
ERROR at line 1:  
ORA-29532: Java call terminated by uncaught Java exception:  
java.lang.SecurityException: policy table update java.lang.RuntimePermission,  
loadLibrary.*  
ORA-06512: at "SYS.DBMS_JVM_EXP_PERMS", line 189  
ORA-06512: at line 8 

La escalada de privilegios aún ha tenido éxito por lo que el mensaje de error es un poco engañoso, pero podría ser un indicador útil.

SQL>  SELECT TYPE_NAME, NAME, ACTION FROM USER_JAVA_POLICY WHERE GRANTEE_NAME ='JAVATEST';  
  
TYPE_NAME  
--------------------------------------------------------------------------------  
NAME  
--------------------------------------------------------------------------------  
ACTION  
--------------------------------------------------------------------------------  
java.io.FilePermission  
 $ 
execute  

Vamos a centrarnos en la identificación de la escalada de privilegios de Java. 
Una vez que se han asignado privilegios de Java existen múltiples formas de utilizar el privilegio de ganar SYSDBA, y una vez adquirido, el atacante tendría limpiar el rastro que habían dejado atrás, primero instalando un rootkit, y luego revocar privilegios y eliminando las cuentas que se crearon en el camino. 

Así, el reto de un investigador forense es buscar pistas que un atacante puede no haber sido lo suficientemente diligente para limpiar. 

Así, en primer lugar, ¿cómo podemos saber si un usuario se ha concedido privilegios Java? DBA_JAVA_POLICY es la vista fundamental, y esto muestra amablemente todos los privilegios concedidos por orden cronológico de Java utilizando el número de secuencia de incrementar…

SELECT TYPE_NAME, NAME, ACTION FROM USER_JAVA_POLICY WHERE GRANTEE_NAME ='JAVATEST';  
  
TYPE_NAME  
--------------------------------------------------------------------------------  
NAME  
--------------------------------------------------------------------------------  
ACTION  
--------------------------------------------------------------------------------  
java.io.FilePermission  
ALL FILES
execute 

Así que si un atacante sabe esto, es probable que ha ga drop de a cuenta de ataque y lo recrea si es necesario como parte de un ataque posterior. Sin embargo, si nos fijamos en la tabla base sys.java $ policy $ para la vista DBA_JAVA_POLICY, veremos que la tabla realmente persiste en las concesiones de Java, incluso después de que el usuario haya sido eliminado !!! Esto es útil para una investigación posterior a incidentes.

SELECT * FROM sys.java$policy$ ORDER BY key DESC;  
  
KIND#   GRANTEE#    TYPE_SCHEMA#    TYPE_NAME   NAME    ACTION  STATUS# KEY  
-----------------------------------------------------------------------------------------------------  
0   101 0   java.io.FilePermission  ALL FILES   execute 2   282  
0   100 0   java.io.FilePermission  ALL FILES   execute 2   262  
0   54  0   java.io.FilePermission  ALL FILES   execute 2   242  
  
--Note that the GRANTEE# correlates to the sys.user$.USER# column and we can see   
-- that user 101 and 100 both had execute on ALL FILE   
-- ,but these users no longer exist in sys.user$ and still the privs are recorded in sys.java$policy$ ~which could be very useful info..  
  
SQL> select name from sys.user$ where user# in (101, 100);  
  
no rows selected  
  
SQL> select name from sys.user$ where user# in (54);  
  
NAME  
------------------------------  
SCOTT  
  
--Of course a clever attacker that had gained SYS could delete from the base table..  
  
SQL> delete from sys.java$policy$ where key='242';  
  
1 row deleted.  
  
SQL> commit;  
  
Commit complete.  
  
--And the evidence of the escalation has gone.. How would an investigator see that the table had been modified in this case?   
--Tracing back further it would hopefully be possible to use the dba_tab_modifications view:  
  
SQL> SELECT * FROM dba_tab_modifications WHERE table_name='JAVA$POLICY$'  
  2  ;  
  
no rows selected  
--This empty result set is due to the fact that MONITORING is not set on this table by default. This could be rectified as follows in this example.  
SQL> exec dbms_stats.gather_table_stats ('SYS','JAVA$POLICY$');  
  
PL/SQL procedure successfully completed.  
  
SQL> delete from sys.java$policy$ where key='282';  
  
0 rows deleted.  
  
SQL>  delete from sys.java$policy$ where key='242';  
  
1 row deleted.  
  
SQL> execute dbms_stats.flush_database_monitoring_info;  
  
PL/SQL procedure successfully completed.  
  
SQL> SELECT * FROM dba_tab_modifications WHERE table_name='JAVA$POLICY$';  
  
TABLE_OWNER     TABLE_NAME      PARTITION_NAME      SUBPARTITION_NAME  INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS  
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------   
SYS                 JAVA$POLICY$                                           0          0          1        31-MAR-10 NO              0  
  
--It is unlikely that a DB will have had this default setting changed beforehand though..   
--So what else can be done to see if the DBA_JAVA_POLICY table has been changed recently?  
  
SQL> select scn_to_timestamp(Max(ora_rowscn)) from sys.java$policy$;  
  
SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))  
-----------------------------------------------------  
30-MAR-10 06.10.06.000000000 PM  

Nota: esta consulta normalmente trabajará hasta 7 días después de que el cambio haya ocurrido, por lo que si obtiene este error ORA-08181 la tabla probablemente no ha tenido comandos de manipulación de datos (DML) en él durante ese tiempo.

ORA-08181: specified number is not a valid system change number

¿Y tú de quien eres?: SQL para mostrar consultas jerárquicas en Oracle

En algunas ocasiones, es necesario recuperar los datos de una tabla cuya relación es recursiva, para mostrar pertenencias y/o jerarquías (por ejemplo litados de reportes de organizaciones, desglosadas por departamentos, direcciones, etc). Tales jerarquías generalmente son representadas a través de estructuras tipo árbol, en donde la raíz (o nodo principal) tiene ramas (nodos descendientes) y cada una de estas ramas puede a su vez generar más ramas, hasta llegar a un punto final en donde no hay mas divisiones. Esos nodos terminales son pues llamados también hojas.

Para el ejemplo, supongamos que se tiene una tabla donde se tiene almacenados los datos familiares de personas en una tabla como la siguiente:

idPersona	Nombres	hijoDe
1	Juan Carlos
2	Luis	5
3	Pedro	5
4	Jaime Alberto	9
5	Olga María	1
6	Marta Edith	8
7	Lucía	9
8	Nelly	1
9	Felipe	8
10	Edgar Nelson	8

La anterior tabla, almacena los datos acerca de los padres de cada persona. De esta manera, es posible construir un árbol como el siguiente:

Desde la versión 9i, el lenguaje sql de oracle provee la posibilidad de recuperar los datos de una tabla, vista, o tabla externa, simulando el recorrido de un árbol, el cual puede hacerse comenzando por la raía hasta llegar a todas las hojas, o desde las hojas hasta llegar a la raíz.

Por lo tanto, La consulta:

SELECT nombres, LEVEL

FROM persona

START WITH nombres='Juan Carlos'

CONNECT BY PRIOR idPersona = hijode;

Retornará el nombre de todas las personas y el id del respectivo padre; empezando por el nombre Juan Carlos. La Pseudocoluma LEVEL indica el nivel en el que se encuentra el registro, tras haber construido el arbol. START WITH indica el registro raiz; es decir, donde empieza el arbol. CONNECT BY PRIOR indica las columnas en las cuales existe la relación padre-hijo

Salida:

Si desea recuperar la descendencia a partir de “Felipe”, la consulta sería:

Obsérvese que en la anterior consulta el valor de LEVEL para “Felipe” es igual a 1, ya en el árbol que construye esta consulta, el nodo “Felipe” se encuentra en el primer nivel.

Si por el contrario, se desean conocer los padres de “Felipe”, la consulta sería:

Obsérvese en el anterior ejemplo, que el recorrido se hizo de abajo hacia arriba (en el árbol). El orden de las columnas en la clausula CONNECT BY PRIOR, especifica el orden del recorrido. En el primer ejemplo se tenía:CONNECT BY PRIOR idPersona=hijoDe y se tuvo el recorrido del árbol de arriba a abajo. En el ultimo ejemplo se tuvo CONNECT BY PRIOR hijoDe =idPersona, y el recorrido fue de abajo hacia arriba.

De igual manera, desde la versión 9i, fue incluida la función SYS_CONNECT_BY_PATH(), que concatena los valores de las ramas del árbol en el recorrido.

El siguiente es un ejemplo de su utilización:

Es muy fácil entonces hacer consultas jerárquicas con la utilización de la cláusula STARA WITH y CONNECT BY y la función SYS_CONNECT_BY_PATH.

NBA y COALESCE

La función COALESCE recibe varios argumentos y retorna la primer expresión distinta de nulo de la lista de dichos argumentos.

• Sintaxis

COALESCE ( expression [ ,...n ] )

• Argumentos

expression: Puede ser una expresión de cualquier tipo.

• Tipos de valor devueltos

Devuelve el tipo de datos de expression con la precedencia de tipo de datos más alta.
Nota: Si todos los argumentos son NULL, COALESCE devuelve NULL.
Al menos uno de los valores NULL debe ser NULL con tipo.

COALESCE(expression1,...n) es equivalente a esta función CASE:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

ISNULL y COALESCE, aunque son equivalentes, pueden comportarse de forma diferente.
Una expresión que contenga ISNULL con parámetros diferentes de NULL se considera que es NOT NULL, mientras que las expresiones que contienen COALESCE con parámetros que son diferentes de NULL se consideran NULL.

En este ejemplo, la tabla NBA_TEAM_TICKETS_SOLDS incluye tres columnas con información acerca del precio de entradas vendidas por cada equipo de la NBA.
Para determinar el importe total por cada equipo, utilice la función COALESCE para obtener sólo los valores no NULL encontrados en hourly_wage, price y commission.

CREATE TABLE NBA_TEAM_TICKETS_SOLDS

(

party_day_wage number NULL,

price number NULL,

commission number NULL,

num_sales int NULL 

);

/

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(10.00, NULL, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(20.00, NULL, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(30.00, NULL, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(40.00, NULL, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, 10000.00, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, 20000.00, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, 30000.00, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, 40000.00, NULL, NULL);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, NULL, 15000, 3);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, NULL, 25000, 2);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, NULL, 20000, 6);

INSERT NBA_TEAM_TICKETS_SOLDS VALUES(NULL, NULL, 14000, 4);

/

SELECT CAST(COALESCE(party_day_wage * 40 * 52, price, commission * num_sales) AS money) AS 'Total sales' FROM NBA_TEAM_TICKETS_SOLDS;
/

Total Sales

------------

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

Detección de licencias Oracle Database Server

Nota:

La concesión de licencias de Oracle es un asunto complicado. Las notas aquí son sólo una guía. Siempre se debe hablar de su licencia con Oracle License Management Services.

Esta pequeña entrada del blog mostrará algunos comandos SQL que pueden ser ejecutados para mostrar que componentes de tu base de datos han de ser licenciados con Oracle Inc.

Poniéndonos en situación

Imaginemos que tenemos una auditoria de licencias de Oracle en enero, por lo que habrá que echar un vistazo a todo lo que tenemos para comprobar qué características que estamos utilizando.

Mi primer consejo seria mantener un repositorio de información, con toda esta información inventariada, a modo de pequeña CMDB, como aconseja las buenas practicas de ITIL

El lado de la base de datos es bastante fácil porque tenemos licencias FTE para Enterprise Edition, Diagnostics y Tuning Pack y Partitioning.

• Numero de usuarios, CPU/Procesadores:

select * from v$license;

• ¿Que versión tengo instalada?:

select banner from v$version where BANNER like '%Edition%';

¿Está instalada la opción de particonamiento de tablas y tablespaces?:

select decode(count(*), 0, 'No', 'Yes') Partitioning

from ( select 1 

       from dba_part_tables

       where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM')

         and rownum = 1 )

/

• ¿Está Oracle Spatial instalado?:

select decode(count(*), 0, 'No', 'Yes') Spatial

from ( select 1

       from all_sdo_geom_metadata 

       where rownum = 1 )

/

• ¿Está Oracle RAC instalado?:

select decode(count(*), 0, 'No', 'Yes') RAC

from ( select 1 

       from v$active_instances 

       where rownum = 1 );

/

• ¿Que características están siendo usadas y cuáles no?: 

(solo para versiones Oracle  9i)

Col name  format a50 heading "Option"

Col value format a5  heading "?"      justify center wrap

Break on value dup skip 1

Spool option

Select parameter name, value

from v$option 

order by 2 desc, 1

/

• ¿Que características están siendo usadas y cuáles no?: 

(solo para versiones Oracle 10g)

Set feedback off

Set linesize 122

Col name             format a45     heading "Feature"

Col version          format a10     heading "Version"

Col detected_usages  format 999,990 heading "Detected|usages"

Col currently_used   format a06     heading "Curr.|used?"

Col first_usage_date format a10     heading "First use"

Col last_usage_date  format a10     heading "Last use"

Col nop noprint

Break on nop skip 1 on name

Select decode(detected_usages,0,2,1) nop,

       name, version, detected_usages, currently_used,

       to_char(first_usage_date,'DD/MM/YYYY') first_usage_date, 

       to_char(last_usage_date,'DD/MM/YYYY') last_usage_date

from dba_feature_usage_statistics

order by nop, 1, 2

/

¿Que características están siendo usadas y cuáles no?: 

(para versiones Oracle 11g en adelante)


En realidad bastante simple para ver qué características están siendo utilizados en la base de datos. Oracle proporciona la vista DBA_FEATURE_USAGE_STATISTICS para ese fin.

SQL> 
SQL> DESC dba_feature_usage_statistics
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------
 DBID                                                  NOT NULL NUMBER
 NAME                                                  NOT NULL VARCHAR2(64)
 VERSION                                               NOT NULL VARCHAR2(17)
 DETECTED_USAGES                                       NOT NULL NUMBER
 TOTAL_SAMPLES                                         NOT NULL NUMBER
 CURRENTLY_USED                                                 VARCHAR2(5)
 FIRST_USAGE_DATE                                               DATE
 LAST_USAGE_DATE                                                DATE
 AUX_COUNT                                                      NUMBER
 FEATURE_INFO                                                   CLOB
 LAST_SAMPLE_DATE                                               DATE
 LAST_SAMPLE_PERIOD                                             NUMBER
 SAMPLE_INTERVAL                                                NUMBER
 DESCRIPTION                                                    VARCHAR2(128)


La salida se muestra corresponde a una base de datos Oracle 12c de prueba.
COLUMN name  FORMAT A60
COLUMN detected_usages FORMAT 999999999999

SELECT u1.name,
       u1.detected_usages,
       u1.currently_used,
       u1.version
FROM   dba_feature_usage_statistics u1
WHERE  u1.version = (SELECT MAX(u2.version)
                     FROM   dba_feature_usage_statistics u2
                     WHERE  u2.name = u1.name)
AND    u1.detected_usages > 0
AND    u1.dbid = (SELECT dbid FROM v$database)
ORDER BY name;

NAME                                                         DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
Adaptive Plans                                                             1 TRUE  12.1.0.2.0
Automatic Maintenance - Optimizer Statistics Gathering                     1 TRUE  12.1.0.2.0
Automatic Maintenance - SQL Tuning Advisor                                 1 TRUE  12.1.0.2.0
Automatic Maintenance - Space Advisor                                      1 TRUE  12.1.0.2.0
Automatic Reoptimization                                                   1 TRUE  12.1.0.2.0
Automatic SGA Tuning                                                       1 TRUE  12.1.0.2.0
Automatic SQL Execution Memory                                             1 TRUE  12.1.0.2.0
Automatic Segment Space Management (system)                                1 TRUE  12.1.0.2.0
Automatic Undo Management                                                  1 TRUE  12.1.0.2.0
Backup Rollforward                                                         1 TRUE  12.1.0.2.0
Backup and Restore of plugged database                                     1 TRUE  12.1.0.2.0

NAME                                                         DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
Character Set                                                              1 TRUE  12.1.0.2.0
Deferred Segment Creation                                                  1 TRUE  12.1.0.2.0
Flashback Database                                                         1 TRUE  12.1.0.2.0
Job Scheduler                                                              1 TRUE  12.1.0.2.0
LOB                                                                        1 TRUE  12.1.0.2.0
Locally Managed Tablespaces (system)                                       1 TRUE  12.1.0.2.0
Locally Managed Tablespaces (user)                                         1 TRUE  12.1.0.2.0
Logfile Multiplexing                                                       1 TRUE  12.1.0.2.0
Oracle Java Virtual Machine (system)                                       1 TRUE  12.1.0.2.0
Oracle Managed Files                                                       1 TRUE  12.1.0.2.0
Oracle Multitenant                                                         2 TRUE  12.1.0.1.0

NAME                                                         DETECTED_USAGES CURRE VERSION
------------------------------------------------------------ --------------- ----- -----------------
Oracle Pluggable Databases                                                 1 TRUE  12.1.0.2.0
Parallel SQL Query Execution                                               1 TRUE  12.1.0.2.0
Partitioning (system)                                                      1 TRUE  12.1.0.2.0
Recovery Area                                                              1 TRUE  12.1.0.2.0
Result Cache                                                               1 TRUE  12.1.0.2.0
SQL Plan Directive                                                         1 TRUE  12.1.0.2.0
SecureFiles (system)                                                       1 TRUE  12.1.0.2.0
SecureFiles (user)                                                         1 TRUE  12.1.0.2.0
Server Parameter File                                                      1 TRUE  12.1.0.2.0
Traditional Audit                                                          1 TRUE  12.1.0.2.0
Unified Audit                                                              1 TRUE  12.1.0.2.0

33 rows selected.

Por defecto esta vista se actualiza una vez por semana, pero se puede realizar una tarea para tenerla mas actualizada.

Oracle 12cR1 novedades (Parte II); Columnas Identidad

Oracle 12c Nueva característica – Columnas Identidad

Viendo las nuevas características de la versión 12c, encontré que ahora podemos definir una columna con el  tipo de dato Identity, tal como lo tiene desde hace tiempo IBM DB2, en concreto desde la versión 8.0, de la siguiente manera:

SQL>CREATE TABLE table (col1 INT,col2 DOUBLE,col3 INT NOT NULL GENERATED ALWAYS AS IDENTITY;
(START WITH 100, INCREMENT BY 5))

Que significa una columna Identity?
Es incorporar a una columna la propiedad de ser auto numérica.


COLUMN_NAME  GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]AS                                  
  IDENTITY  [ ( identity_options ) ]

Donde

• ALWAYS      Indica que no será necesario introducir un valor para esta columna en una sentencia insert. Por el contrario indicar un valor aunque sea Null, da un error ORA-32795.
• BY DEFAULT  Le permite utilizar Identity, si la columna no se hace  referencia en la una sentencia insert, pero si se hace referencia a la columna, el valor especificado se utiliza en lugar de la  identidad. El intento de especificar el valor NULL en este caso se traduce en un error, ya que las columnas de identidad son siempre NOT NULL. 
• BY DEFAULT [ON NULL]: Si en una sentencia INSERT se mete un valor NULL, esta se generará un nuevo valor, si en cambio si mete un valor para esta columna, se insertará el valor de la sentencia.

Contras al implementar esta característica

1. Dado a que una columna definida como IDENTITY está vinculada a una secuencia, le ocurre lo mismo que si la secuencia se define por fuera, por lo cual si una sentencia INSERT abortara, los números de la secuencia se perderían.
2. En el caso de hacer una copia de seguridad de una tabla, se exportaría con todos sus valores, la querer importar esa tabla. 

• Solución: Deberíamos tener en cuenta que la columna IDENTITY  este en by default porque en caso contrario daría error.

Conclusión 

La inclusión de las columnas IDENTITY, es un importante avance, ya que encapsula en una sentencia INSERT la obtención del nextval de la secuencia. Como ocurre con IBM DB2 o M$ SQL Server.

Por otro lado la inclusión de las opciones By Default y By Default On Null, permiten que los valores puedan ser internos o externos. Lo más importante es que esto lo hacemos casi sin que perdamos performance.

Mi opinion

Esta es una característica poco aseada que se ha introducido unos 20 años tarde. En mi opinión, que fácilmente podría haber sido implementado en Oracle 6.0. Podría ser útil para nuevas aplicaciones, aunque la cantidad de tiempo y esfuerzo ahorrado es insignificante. Para las aplicaciones existentes de la relevancia de esta nueva característica es más cuestionable;´Nadir va a mezclar estilos de codificación y modificar código funcional, por una característica que apenas aporta nada.

Oracle 12cR1 novedades (Parte I); Columnas Invisibles

Oracle 12cR1 – Columnas Invisibles

La primera de las Nuevas Características de Oracle 12c que vamos a traer a este blog son las columnas invisibles. Es una de las opciones menos controvertidas y mas sencillas de explicar.

La idea básica de las columnas básicas es que ellos permiten ser ocultadas desde las aplicaciones.  Las columnas se pueden hacer invisibles en la sentencia CREATE TABLE o posteriormente utilizando una sentencia ALTER TABLE. Por defecto columnas son visibles. Columnas invisibles pueden hacerse visibles de nuevo utilizando una sentencia ALTER TABLE.

¿Como funciona todo esto?

El siguiente ejemplo crea una tabla con una columna invisible:

SQL> CREATE TABLE ejemplo1
(
  c1 NUMBER,
  c2 NUMBER INVISIBLE,
  c3 NUMBER,
  c4 NUMBER
);

SQL> DESC ejemplo1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 C1                                                 NUMBER
 C3                                                 NUMBER
 C4                                                 NUMBER

SQL*Plus puede opcionalmente mostrar columnas implementando la opción COLINVISIBLE.

SQL> SET COLINVISIBLE ON

SQL> DESC ejemplo1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 C1                                                 NUMBER
 C3                                                 NUMBER
 C4                                                 NUMBER
 C2 (INVISIBLE)                                     NUMBER

A pesar de que la columna es invisible, en esa columna todavía se pueden visualizar y modificar. Por ejemplo:

SQL> INSERT INTO ejemplo1 (c1,c2,c3,c4) VALUES (11,12,13,14);

1 row created.
SQL> SELECT c1,c2,c3,c4 FROM ejemplo1;

        C1         C2         C3         C4
---------- ---------- ---------- ----------
        11         12         13         14

Mirad, que en el ejemplo superior, todas las columnas son listadas tanto en las sentencias INSERT y SELECT. Que ocurre si omitimos los nombres de las columnas en la sentencia INSERT?

INSERT INTO ejemplo1 VALUES (21,22,23,24)
            *
ERROR at line 1:
ORA-00913: too many values

La sentencia INSERT falla por que estamos intentando meter ina columna invisible en la columna (C2). Si nosotros lo intentamos otra vez omitiendo para C2 la sentencia INSERT será correcta:

SQL> INSERT INTO ejemplo1 VALUES (21,22,24);

1 row created.

SQL> SELECT c1,c2,c3,c4 FROM ejemplo1;

        C1         C2         C3         C4
---------- ---------- ---------- ----------
        11         12         13         14
        21                    22         24

Observa que el valor para C2 es NULL para cada fila encontrada. SELECT * no devuelve las columnas invisibles. Por ejemplo:

SQL> SELECT * FROM ejemplo1;

        C1         C3         C4
---------- ---------- ----------
        11         12         14
        21         22         24

Una columna invisible puede ser visible de nuevo, por ejemplo:

ALTER TABLE ejemplo1 MODIFY c2 VISIBLE;

Una columna visible puede ser invisible, por ejemplo:

ALTER TABLE ejemplo1 MODIFY c2 INVISIBLE;

Monitorización en tiempo Real de SQL utilizando DBMS_SQLTUNE

Los informes de Real-Time SQL Monitoring se pueden encontrar dentro de las herramientas de Oracle en:

• Enterprise Manager – Haz Click en la pestaña "Performance", entonces aparece el link  "SQL Monitoring" en la parte inferior derecho  de la página para dar paso a la ventana "Monitored SQL Executions". Haz click en las SQL_ID que te interesen para mostrar los informes de monitorización de SQL.
• SQL Developer – disponible en el menu de  "Tools > Monitor SQL".
• El paquete DBMS_SQLTUNE.

En este pequeño artículo voy a demostrar el uso del paquete DBMS_SQLTUNE para mostrar los informes de seguimiento de SQL sin utilizar las herramientas Enterprise Manager o SQL Developer. Este artículo ha incluido la funcionalidad adicional introducida en Oracle 12c.

SQL monitoring require que

• El parámetro STATISTICS_LEVEL esté inicializado a 'TYPICAL' o 'ALL'
• El parámetro CONTROL_MANAGEMENT_PACK_ACCESS esté inicializado a 'DIAGNOSTIC+TUNING'.

SQL> CONN / AS SYSDBA Connected. SQL> SHOW PARAMETER statistics_level NAME                                                                      TYPE       VALUE ------------------------------------                                         -----------    ------------------------------ statistics_level                                                         string     TYPICAL SQL> SHOW PARAMETER control_management_pack_access NAME                                                                      TYPE       VALUE ------------------------------------                                          ----------- ------------------------------ control_management_pack_access                              string     DIAGNOSTIC+TUNING SQL>

MONITOR Hint

El hint MONITOR habilita la monitorización SQL para sentencias, que de otra manera no las iniciaría.

SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees FROM   emp e JOIN dept d  ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname;

REPORT_SQL_MONITOR

La función puede aceptar muchos parámetros opcionales, aquí puedes revisarlos, pero la mayoría que vas a usar están descritos como sigue: