-. Blog Fernando Valenzuela .-

Cambia Puerto http del EM de Oracle XE - (XE: Changing the default http port )

2009-10-28T13:50:00.002-03:00

Determinas la configuracion actual setea en tu Oracle XE. Se accede mediante SQLPLUS con usuario SYSTEM (o cualquier otro con privilegios de DBA):


C:\WINDOWS\system32>sqlplus system@xe

SQL*Plus: Release 10.1.0.2.0 - Production on Mi Jan 25 11:44:33 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> -- get current status
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
           , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
    8080          0

Tu puedes cambiar el puerto HTTP y el puerto de FTP cuando quieras solo ten algunas precausiones tales como

Tener en cuenta que se necesita privilegios especiales para los puertos <1024>
debes chequear los puertos utilizados (netstat -ano)
Que el parametro del httpport define el puerto que correra el EM.


SQL> -- set http port and ftp port
SQL> begin
2    dbms_xdb.sethttpport('80');  --El valor 80 es el nuevo parametro
3    dbms_xdb.setftpport('2100'); --El valor 80 es el nuevo parametro
4  end;
5  /

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport as "HTTP-Port"
           , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
      80       2100

Si sólo deseas utilizar la base de datos sin permitir el acceso a través de HTTP o FTP, puedes deshabilitar estas opciones de la siguiente manera:


SQL> -- disable http and ftp access
SQL> begin
2    dbms_xdb.sethttpport('0');
3    dbms_xdb.setftpport('0');
4  end;
5  /

PL/SQL procedure successfully completed.

SQL> -- get current status
SQL> select dbms_xdb.gethttpport as "HTTP-Port"
           , dbms_xdb.getftpport as "FTP-Port" from dual;

HTTP-Port   FTP-Port
---------- ----------
       0          0

BULK COLLECT con miles de registros y la cláusula LIMIT

2009-10-27T08:52:00.000-03:00

Veo muchísimas veces, que cuando se utiliza BULK COLLECT en los códigos PL/SQL, no se coloca la cláusula LIMIT. Cuando no utilizamos ésta cláusula, lo único que ganamos es arruinar la memoria del proceso.
La cláusula LIMIT nos permite definir la cantidad de 'datos' que vamos a colocar en memoria. Cuando utilizamos LIMIT, lo ideal es definirlo en un valor entre 100 a 500. Personalmente, elijo el valor 100 porque en base a mi experiencia suele ser el mejor valor. Pero porque elijo un valor tan chico y no 1000 o 5000 por ejemplo? Bueno, por la simple razón que manejar gran cantidad de datos en memoria es más costoso que manejar poca cantidad.
Si elegimos un valor alto en el LIMIT, puede darse 3 casos:
- Que nuestro código se ejecute más rápidamente (improbable).
- Que nuestro código se ejecute en igual tiempo (improbable).
- Que nuestro código se ejecute más lentamente (probable).

Veamos un ejemplo para entender mejor las consecuencia de no utilizar la cláusula LIMIT.

Primero creamos una tabla TEST con 10.000 registros (para ver la diferencia del uso de la cláusula LIMIT, no hace falta realizar el ejemplo con millones de registros. Con unos miles de registros nos alcanza para entender el tema) y una tabla TEST_2 con la estructura de la tabla TEST pero sin registros:


SQL_9iR2> CREATE TABLE test AS
 2  SELECT level id , 'oracle_'||level texto
 3  FROM dual
 4  CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE test_2 AS
 2  SELECT *
 3  FROM test
 4  WHERE 1 = 2 ;

Table created.

Antes de ejecutar el primer código, voy a liberar la memoria que ya no se está utilizando en mi sesión actual para poder ver claramente la diferencia de las estadísticas tomadas en cada ejecución.


SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ejecutamos un código PL/SQL con Bulk Collect pero SIN la cláusula LIMIT:


SQL_9iR2> DECLARE
 2  TYPE t_array_number   IS TABLE OF NUMBER ;
 3  TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
 4  t_array_id     t_array_number ;
 5  t_array_texto  t_array_varchar2 ;
 6  CURSOR cur IS
 7      SELECT * FROM test ;
 8  BEGIN
 9      OPEN cur ;
10      LOOP
11
12      FETCH cur BULK COLLECT INTO t_array_id , t_array_texto ;
13
14      FORALL i IN 1 .. t_array_id.COUNT
15          INSERT INTO test_2
16          VALUES (t_array_id(i) , t_array_texto(i)) ;
17
18      EXIT WHEN cur%NOTFOUND ;
19
20      END LOOP ;
21      COMMIT ;
22      CLOSE cur ;
23  END ;
24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Antes de ejecutar el segundo código, voy a liberar nuevamente la memoria que ya no se está utilizando en mi sesión actual.


SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ahora ejecutamos el mismo código PL/SQL con Bulk Collect pero CON la cláusula LIMIT:


SQL_9iR2> DECLARE
 2  TYPE t_array_number   IS TABLE OF NUMBER ;
 3  TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
 4  t_array_id     t_array_number ;
 5  t_array_texto  t_array_varchar2 ;
 6  CURSOR cur IS
 7      SELECT * FROM test ;
 8  BEGIN
 9      OPEN cur ;
10      LOOP
11
12      FETCH cur BULK COLLECT INTO t_array_id , t_array_texto LIMIT 100 ;
13
14      FORALL i IN 1 .. t_array_id.COUNT
15          INSERT INTO test_2
16          VALUES (t_array_id(i) , t_array_texto(i)) ;
17
18      EXIT WHEN cur%NOTFOUND ;
19
20      END LOOP ;
21      COMMIT ;
22      CLOSE cur ;
23  END ;
24  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Podemos ver que en la segunda ejecución, estoy cargando en memoria 100 registros en cada Fetch que realizo. A diferencia de la primer ejecución, que carga todos los datos de una vez.

Veamos las estadísticas obtenidas de las 2 ejecuciones anteriores:


Nombre                         Ejecución_1 Ejecución_2  Diferencia
------------------------------ ----------- ----------- -----------
LATCH.kwqit: protect wakeup ti           1           0          -1
LATCH.simulator lru latch                1           0          -1
LATCH.spilled msgs queues list           1           0          -1
LATCH.transaction allocation             3           0          -3
LATCH.session timer                      5           0          -5
LATCH.multiblock read objects            8           0          -8
LATCH.channel operations paren          11           0         -11
LATCH.child cursor hash table           20           8         -12
LATCH.Consistent RBA                    56           6         -50
LATCH.lgwr LWN SCN                      56           6         -50
LATCH.mostly latch-free SCN             56           6         -50
LATCH.active checkpoint queue           63           7         -56
LATCH.session idle bit                 183          45        -138
LATCH.enqueues                         219          49        -170
LATCH.redo writing                     241          25        -216
LATCH.SQL memory manager worka         337           0        -337
LATCH.messages                         427          42        -385
LATCH.simulator hash latch             844           4        -840
LATCH.dml lock allocation            1,428         154      -1,274
LATCH.shared pool                    1,586         271      -1,315
LATCH.row cache enqueue latch        1,648         194      -1,454
LATCH.cache buffers lru chain        1,521           5      -1,516
LATCH.library cache pin alloca       2,900         362      -2,538
LATCH.row cache objects              4,438         502      -3,936
LATCH.enqueue hash chains            4,841         508      -4,333
LATCH.checkpoint queue latch         6,296         521      -5,775
LATCH.session allocation            19,333       1,893     -17,440
LATCH.undo global data              30,208       2,945     -27,263
LATCH.redo allocation               30,716       3,231     -27,485
LATCH.sequence cache                42,506       4,124     -38,382
LATCH.library cache pin             60,580       6,241     -54,339
LATCH.library cache                 76,555       7,911     -68,644

LATCHES:

Ejecución_1   Ejecución_2   Diferencia   Porcentaje
    670,187        67,521     -602,666      992.56%

Una de las cosas que me interesa mostrarles acerca de éstas ejecuciones son los LATCHES (loqueos). Si bien la ejecución de los 2 códigos PL/SQL demoraron exactamente lo mismo en ejecutarse (en éste ejemplo procesando solamente 10.000 registros), las estadísticas nos muestran que estamos empleados muchísimos más loqueos en la primer ejecución que en la segunda. Pero porqué sucede ésto? Recordemos que en la segunda ejecución, lo único que modificamos en el código fue el agregado de la cláusula LIMIT. Bien, como dijimos en el comienzo, manejar gran cantidad de memoria es más costoso que manejar poca cantidad, por lo que Oracle tiene que emplear mayor cantidad de loqueos para manejar los 10.000 registros que subimos a memoria en la primer ejecución, que manejar solamente 100 registros en la segunda ejecución.
Este ejemplo lo realizamos con un sólo usuario concurrente.... pero imagínese qué sucedería si tenemos muchos usuarios concurrentes realizando lo mismo que nosotros... y por lo tanto, generando gran cantidad de loqueos....

NOTA: Tenemos que evitar los loqueos a toda costa ya que los loqueos afectan la performance del sistema. Mientras mayor sea la cantidad de loqueos, nuestro sistema se vuelve cada vez menos escalable; y como consecuencia, cada vez soporta menor cantidad de usuarios concurrentes.

Tablas Particionadas - Comando ' EXCHANGE '

2009-10-27T08:50:00.001-03:00

Exchange Partition

Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.
En resumen, lo que hace la sentencia Exchange Partition es modificar el diccionario de datos y simular que los datos que ya tenemos cargados en una tabla, corresponden a una partición determinada de otra tabla.

Veamos un ejemplo muy sencillo para entender mejor éste tema:


SQL_10gR2> CREATE TABLE datos_1 AS
2  SELECT level id, timestamp'2000-11-02 09:00:00' fecha
3  FROM dual
4  CONNECT BY level <= 100000 ;  Table created.  Elapsed: 00:00:01.06  SQL_10gR2> CREATE TABLE datos_2 AS
2  SELECT level id, timestamp'2001-09-10 13:00:00' fecha
3  FROM dual
4  CONNECT BY level <= 100000 ;  Table created.

Lo que hicimos fue crear 2 tablas con distintas fechas en cada una de ellas.

Ahora creamos solamente la estructura de la tabla particionada en donde vamos a cargar los datos:


SQL_10gR2> CREATE TABLE test
2  ( id, fecha )
3  PARTITION BY RANGE ( fecha )
4  (
5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
7  )
8  AS
9  SELECT 1, timestamp'2000-11-02 09:00:00'
10  FROM dual
11  WHERE 1 = 0 ;

Vamos a realizar un alter para modificar el diccionario de datos y relacionar cada una de las 2 tablas que creamos con la respectiva partición de la tabla TEST...


SQL_10gR2> ALTER TABLE test
2  EXCHANGE PARTITION year_2000
3  WITH table datos_1
4  WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.03

SQL_10gR2> ALTER TABLE test
2  EXCHANGE PARTITION year_2001
3  WITH table datos_2
4  WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.02

SQL_10gR2> SELECT count(*)
2  FROM test ;

COUNT(*)
----------
  200000

1 row selected.

SQL_10gR2> SELECT count(*)
2  FROM datos_1 ;

COUNT(*)
----------
       0

1 row selected.

SQL_10gR2> SELECT count(*)
2  FROM datos_2 ;

COUNT(*)
----------
       0

1 row selected.

Como podemos ver, con el Exchange Partition no tardamos casi nada en cargar los datos en la tabla particionada ya que en realidad no estamos cargando los datos, simplemente se modifica el diccionario de datos.

Pueden notar que agregué la sentencia WITHOUT VALIDATION. Que es ésto? WITHOUT VALIDATION suele ser una operación rápida porque sólo realiza modificaciones en el diccionario de datos. Si la tabla o tabla particionada que colocamos en el Exchange Partition tiene una primary key o unique constraint habilitado, entonces el Exchange Partition se realiza como WITH VALIDATION para mantener la integridad de las constraints.

Vamos a ejecutar nuevamente los 2 alter anteriores sin la sentencia WITHOUT VALIDATION...


SQL_10gR2> ALTER TABLE test
2  EXCHANGE PARTITION year_2000
3  WITH table datos_1 ;

Table altered.

Elapsed: 00:00:01.00

SQL_10gR2> ALTER TABLE test
2  EXCHANGE PARTITION year_2001
3  WITH table datos_2 ;

Table altered.

Elapsed: 00:00:01.05

Si hubiera ejecutado esos alter con un Trace, el reporte del Trace me mostraría, entre otras sentencias, las siguientes...


select 1
from
"DATOS_1" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          0           0
Fetch        1      0.04       0.04          0         65          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         66          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    0  TABLE ACCESS FULL DATOS_1 (cr=65 pr=0 pw=0 time=44582 us)


select 1
from
"DATOS_2" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          0           0
Fetch        1      0.04       0.04          0         65          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0         66          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
    0  TABLE ACCESS FULL DATOS_2 (cr=65 pr=0 pw=0 time=46957 us)

Antes que nada, notamos que los alter se ejecutaron en mayor tiempo, cierto? De las consultas que observamos del Trace, vemos que se está realizando un FULL SCAN de las tablas y que se está ejecutando una función en el WHERE de cada consulta. Imagínense si tenemos que realizar ésta clase de procesos en ambientes con gran volumen de datos y en donde el sistema se encuentra saturado por el I/O a disco.

Qué sucede si no tenemos los datos separados por año en distintas tablas, y en cambio, tenemos todos los datos en una misma tabla? Bueno, tomando como ejemplo la tabla TEST que acabamos de cargar, podríamos realizar lo siguiente...


SQL_10gR2> CREATE TABLE test_2
2  ( id, fecha )
3  PARTITION BY RANGE ( fecha )
4  (
5      PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
6      PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
7  )
8  AS
9  SELECT *
10  FROM test ;

Table created.

Elapsed: 00:00:05.04

SQL_10gR2> DROP TABLE test ;

Table dropped.

SQL_10gR2> ALTER TABLE test_2 RENAME TO test ;

Table altered.

SQL_10gR2> SELECT count(*)
2  FROM test ;

COUNT(*)
----------
  200000

1 row selected.

Oracle 11g R2 --- Liberado

2009-09-16T16:10:00.001-04:00

Despues de varios meses de pruebas, se libero el dia de hoy la version final del Oracle 11g R2 (11.2.0.1) para linux (32 y 64 bit), esta version trae mas de 200 nuevas mejoras destacando el nuevo ASM FileSystem, mejoras en comprension y mucho mas. Uds. podran encontrar mas informacion y bajar el software en:

http://www.oracle.com/database/index.html

Un gran saludos a todos,

Copiar una base de datos en Windows

2009-08-31T12:21:00.000-04:00

La petición textual era la creación de una base de datos igual que otra, pero con otro nombre. En algunos casos se trataba de replicar el entorno de producción para usarlo como test. En otros, simplemente, testear que el backup permitía recuperar la base de datos sobre otra máquina y dejar esa base de datos operativa como entorno auxiliar.

Cuando la base de datos puede conservar el mismo nombre y mismo SID, es tan fácil como copiar datafiles, redolog, controlfiles y archivos ORA sobre los mismos directorios originales en una máquina con un servidor Oracle instalado*.
* Por supuesto, misma plataforma O.S., versión y release de Oracle.

No obstante, en los casos en los que el nombre debe ser diferente, hay que ser muy preciso con los pasos a seguir. Incluyo dos pasos previos como opcionales por si son de utilidad.

En este ejemplo se asume que disponemos de la posibilidad de hacer un backup en frío de la base de datos.

Pasos preliminares (recomendado):

- Backup frío, backup caliente, export, Transport Tablespaces, backup del backup, backup de todo lo posible.
- Reducción de tamaño de los datafiles. (Tom Kyte tiene un fabuloso script para ello). Y otra vez backup.

Pasos a seguir:

1.- Backup en frío de la BBDD original
2.- Generación del pfile para la nueva BBDD
3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
4.- Definir el nuevo ORACLE_SID
5.- Crear el nuevo servicio.
6.- Creación del fichero de passwords (sólo si el pfile tiene REMOTE_LOGIN_PASSWORDFILE=exclusive)
7.- Conexión a sqlplus como SYSDBA
8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
9.- Backup del controlfile de la BBDD original a traza
10.- Recreación del controlfile con la cláusula SET NAME.
11.- Abrir la base de datos con modo OPEN RESETLOGS.

1.- Backup en frío de la BBDD original.
Restauración del backup sobre nueva ubicación.

-- Ejecutar el resultado de la siguiente sentencia como script.
-- NOTA: cuidado con los nombres duplicados de fichero.
-------------------------------------------------------------
select 'shutdown immediate;' from dual
union all
select 'host copy '||name||' &&directorio_destino' from v$controlfile
union all
select 'host copy '||member||' &directorio_destino' from v$logfile
union all
select 'host copy '||name||' &directorio_destino' from v$datafile
union all
select 'startup' from dual;

-- copia de los ficheros a los directorios destino
---------------------------------------------------

2.- Generación del pfile para la nueva base de datos

SQL> create pfile='?\admin\sid\pfile\inittest.ora' from spfile;

Archivo creado.

3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...

4.- Definir el nuevo ORACLE_SID

c:\>set ORACLE_SID=test

5.- Crear el nuevo servicio.

c:\>oradim -NEW -SRVC OracleServicetest -startmode auto

6.- Creación del fichero de passwords

c:\>orapwd file=C:\orant\ora92\database\PWDtest.ora password=xxxxxxxxx

7.- Conexión a sqlplus como SYSDBA

C:\>sqlplus

SQL*Plus: Release 9.2.0.6.0 - Production on Vie Jul 29 16:41:22 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Introduzca el nombre de usuario: sys/xxxxxxxxxx as sysdba
Conectado a una instancia inactiva.

8.- Arranque de la instancia y creación del fichero de parámetros SPFILE

SQL> STARTUP NOMOUNT PFILE='C:\DIRECTORIO_DESTINO\inittest.ora'
Instancia ORACLE iniciada.

Total System Global Area 1074866044 bytes
Fixed Size 456572 bytes
Variable Size 905969664 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
SQL> create spfile from pfile='C:\DIRECTORIO_DESTINO\inittest.ora';

Archivo creado.

9.- Backup del controlfile de PROD a trace.

SQL> alter database backup controlfile to trace;

Base de datos modificada.

10.- A partir de la traza del fichero de control.
Recreación del controlfile con el SET NAME al nuevo nombre.

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG REUSE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 133
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 2 'C:\DATA\TEST\REDO\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\DATA\TEST\REDO\REDO03.LOG' SIZE 100M,
GROUP 4 'C:\DATA\TEST\REDO\REDO04.LOG' SIZE 150M,
GROUP 5 'C:\DATA\TEST\REDO\REDO05.LOG' SIZE 150M,
GROUP 6 'C:\DATA\TEST\REDO\REDO06.LOG' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
(... ficheros...)
'C:\DATA\TEST\SYSTEM\SYSTEM01.DBF',
'C:\DATA\TEST\DATA\DATA01.DBF'
CHARACTER SET WE8MSWIN1252
;

Archivo de control creado.

11.- Abrir la base de datos con modo OPEN RESETLOGS.

SQL> alter database open resetlogs;

Base de datos modificada.

12.- Verificación.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
test

SQL> select name from v$database;

NAME
---------
TEST

SQL> select status from v$thread;

STATUS
------
OPEN

12.- Shutdown y Open de la base de datos.

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.

SQL> startup
Instancia ORACLE iniciada.

Total System Global Area 1074866044 bytes
Fixed Size 456572 bytes
Variable Size 905969664 bytes
Database Buffers 167772160 bytes
Redo Buffers 667648 bytes
Base de datos montada.
Base de datos abierta.

Uso de logminer

2009-08-31T11:52:00.001-04:00

En alguna ocasión he necesitado recuperar una transacción validada con COMMIT, pero no deseada o errónea. Pongo un ejemplo:

- Usuario borra una fila.
- Por accidente.
- No sabe de qué fila se trata, pero 'algo' ha dicho "registro borrado".
- (...borrado y validado).
- Ni idea de qué datos tenía la fila.
- Ni él, ni nadie.
- Desde ese momento, la fila X ha desaparecido sin rastro.

Con variaciones, éste caso es bastante frecuente, y si el usuario que ha borrado el registro es lo suficientemente rápido, existe una posibilidad de recuperar los datos perdidos.

Generalmente, todas las transacciones convencionales procesan la información pasando por las áreas de redo log. En estos archivos está toda la información correspondiente al nuevo cambio. Oracle basa toda su actividad en el buen funcionamiento del registro de redo log y, por tanto, en esta área únicamente almacena los datos físicos elementales para rehacer la operación. Por tanto, no se almacena información sobre los valores anteriores.

Lo cual sí sucede en el área de rollback, y ¡la gestión de rollback también pasa por redolog!.

Oracle dispone de un paquete, el LOG MINER que permite rastrear los redo logs (online y archivados) junto con la información de rollback disponible también en éstos. Una vez ejecutado el paquete que crea el fichero de diccionario de objetos, Oracle es capaz de interpretar esos redologs y mostrar información tan válida como el SQL generado y el SQL inverso para "reparar" la acción.

SQL> connect / as sysdba
Conectado.
SQL> show parameters utl

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string c:\oraclefiles

SQL> exec DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME =>'dictionary.ora', DICTIONARY_LOCATION => 'c:\oraclefiles');

Procedimiento PL/SQL terminado correctamente.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\REDO04A.LOG
C:\ORACLE\ORADATA\ORCL\REDO04B.LOG
C:\ORACLE\ORADATA\ORCL\REDO05A.LOG
C:\ORACLE\ORADATA\ORCL\REDO05B.LOG
C:\ORACLE\ORADATA\ORCL\REDO06A.LOG
C:\ORACLE\ORADATA\ORCL\REDO06B.LOG

6 filas seleccionadas.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO04A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO05A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO06A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'c:\oraclefiles\dictionary.ora');

Procedimiento PL/SQL terminado correctamente.

SQL> set pages 100
SQL> set lines 120
SQL> column sql_redo format a50
SQL> column sql_undo format a50

SQL> select sql_redo, sql_undo from v$logmnr_contents where rownum<10;

SQL_REDO SQL_UNDO
-------------------------------------------------- --------------------------------------------------
insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '170,01' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '170,01' and "Importe MIN" = '
,"Detalle Vuelo") values ('170,01','1','170,01','1 170,01' and "Importe AVG" = '170,01' and "Ciudad"
70,01','170,01','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52149'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52149' and ROWID = 'AAALV
mAAPAAAAn8AAA';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '112,95' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '112,95' and "Importe MIN" = '
,"Detalle Vuelo") values ('112,95','1','112,95','1 112,95' and "Importe AVG" = '112,95' and "Ciudad"
12,95','112,95','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52206'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52206' and ROWID = 'AAALV
mAAPAAAAn8AAB';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '123,29' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '123,29' and "Importe MIN" = '
,"Detalle Vuelo") values ('123,29','1','123,29','1 123,29' and "Importe AVG" = '123,29' and "Ciudad"
23,29','123,29','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52331'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52331' and ROWID = 'AAALV
mAAPAAAAn8AAC';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '178,55' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '178,55' and "Importe MIN" = '
,"Detalle Vuelo") values ('178,55','1','178,55','1 178,55' and "Importe AVG" = '178,55' and "Ciudad"
78,55','178,55','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52419'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52419' and ROWID = 'AAALV
mAAPAAAAn8AAD';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '107,36' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '107,36' and "Importe MIN" = '
,"Detalle Vuelo") values ('107,36','1','107,36','1 107,36' and "Importe AVG" = '107,36' and "Ciudad"
07,36','107,36','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52488'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52488' and ROWID = 'AAALV
mAAPAAAAn8AAE';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '120,5' and "Importe COUNT" = '1' a
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" nd "Importe MAX" = '120,5' and "Importe MIN" = '12
,"Detalle Vuelo") values ('120,5','1','120,5','120 0,5' and "Importe AVG" = '120,5' and "Ciudad" = 'M
,5','120,5','Madeira','Portugal','Mayores de 50 a± adeira' and "Pais" = 'Portugal' and "Rango Edad" =
os','Mujer','VUELO52561'); 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and "De
talle Vuelo" = 'VUELO52561' and ROWID = 'AAALVmAAP
AAAAn8AAF';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '109,01' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '109,01' and "Importe MIN" = '
,"Detalle Vuelo") values ('109,01','1','109,01','1 109,01' and "Importe AVG" = '109,01' and "Ciudad"
09,01','109,01','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52690'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52690' and ROWID = 'AAALV
mAAPAAAAn8AAG';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '174,91' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '174,91' and "Importe MIN" = '
,"Detalle Vuelo") values ('174,91','1','174,91','1 174,91' and "Importe AVG" = '174,91' and "Ciudad"
74,91','174,91','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52693'); d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
"Detalle Vuelo" = 'VUELO52693' and ROWID = 'AAALV
mAAPAAAAn8AAH';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '131,5' and "Importe COUNT" = '1' a
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" nd "Importe MAX" = '131,5' and "Importe MIN" = '13
,"Detalle Vuelo") values ('131,5','1','131,5','131 1,5' and "Importe AVG" = '131,5' and "Ciudad" = 'M
,5','131,5','Madeira','Portugal','Mayores de 50 a± adeira' and "Pais" = 'Portugal' and "Rango Edad" =
os','Mujer','VUELO52951'); 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and "De
talle Vuelo" = 'VUELO52951' and ROWID = 'AAALVmAAP
AAAAn8AAI';

9 filas seleccionadas.

Acceso a una bbdd MSAccess desde Oracle.

2009-08-31T11:46:00.001-04:00

Es posible acceder a bases de datos no oracle de forma transparente con Oracle Transparent Gateways.

Esta funcionalidad permite configurar una entrada DNS de ODBC como si se tratara de un servicio Oracle cualquiera. De este modo, mediante un dblink, un usuario podría acceder a una tabla de una base de datos access (o SQLServer o MySQL, por ejemplo) desde una query o desde PL/SQL.

La arquitectura de servicios heterogeneos se basa en la siguiente configuración:

Se especifica una entrada de TNS en el fichero tnsnames.ora que resuelva una conexión al host, puerto y nombre de servicio determinado, con el parámetro HS=OK (Oracle Heterogeneous Services).

Por otro lado, el listener destino tiene definido para ese servicio la invocación a un programa llamado "hsodbc" que, mediante un fichero de parámetros llamado init.ora accede al DNS asociado a la base de datos MSAccess concreta.

De este modo, y creando un dblink, cualquier usuario podría hacer una select que cruzara datos de su esquema Oracle y de una bbdd Access.

PASOS A SEGUIR
1.- Instalar en el diccionario los servicios heterogeneos.
2.- Configurar el tnsnames.ora
3.- Configurar el listener.
4.- Comprobar que el listener y el tnsnames.ora funcionan correctamente.
5.- Configurar el ODBC para la bbdd access.
6.- Crear el DBLINK hacia la bbdd access.
7.- Crear el fichero init.ora
8.- Ejecutar una consulta mediante ese dblink.

1.- Instalar en el diccionario los servicios heterogeneos
@?/rdbms/admin/caths.sql

NOTA: puede que estas vistas estén instaladas ya en la bbdd.

2.- Configurar el tnsnames.ora
bbdd_access =
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)
(HOST=192.168.2.4)
(PORT=1521)
)
(CONNECT_DATA =
(SERVICE_NAME=odbc_access)
)
(HS = OK)
)

3.- Configurar el listener. (la entrada en negrita es la que hay que añadir)...

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = orcl)
)
(SID_DESC =
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = odbc_access)
(PROGRAM = hsodbc)
)
)

4.- Comprobar que el listener y el tnsnames.ora funciona correctamente.

C:\Documents and Settings\Administrador>tnsping bbdd_access

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 08-SEP-2006 18:19:42

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Archivos de parßmetros utilizados:
D:\oracle\ora92\network\admin\sqlnet.ora

Adaptador TNSNAMES utilizado para resolver el alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.4) (PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=odbc_access)) (HS = OK))
Realizado correctamente (50 mseg)

5.- Configurar el ODBC para la bbdd access.
La base de datos access utilizada en el ejemplo tiene una única tabla con cuatro filas de ejemplo...

create table ejemplo
(id counter,
valor text)

Es preciso crear un ODBC para acceder a la bbdd access determinada. Desde Windows esta opción se configura en Panel de control, Herramientas administrativas, Administrador ODBC y la pestaña DNS de sistema.

desde ahí se crea el driver odbc_access vinculado a la base de datos que uso: "test_hs.mdb" con esta tabla de ejemplo.

NOTA: esta bbdd access tiene password "tesths"

6.- Crear el DBLINK hacia la bbdd access.

SQL> CREATE DATABASE LINK bbdd_access
2 CONNECT TO ADMIN IDENTIFIED BY TESTHS
3 USING 'bbdd_access';

Enlace con la base de datos creado.

7.- Crear el fichero init.ora en el directorio de ORACLE_HOME /hs/admin/

-- FICHERO initODBC_ACCESS.ora
#
# HS init parameters
#

HS_FDS_CONNECT_INFO = odbc_access
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = odbc_access.trc
HS_AUTOREGISTER = TRUE

8.- Lanzar la query via ese dblink

SQL> column "valor" format a40
SQL> set pages 1000
SQL> set lines 120
SQL> select * from ejemplo@bbdd_access;

id valor
---------- ----------------------------------------
1 dato1
2 dato2
3 dato3
4 dato4

Optimizacion de Consultas SQL en Oracle

2009-08-31T11:39:00.001-04:00

La siguiente consulta tardaba 11 horas en ejecutarse.

SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A,
BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03 B,
BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
AND B.NODO_B_ID = C.NODO_ID;

con el siguiente plan de ejecución:
SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5554 | 124K| 23 |
| 1 | SORT UNIQUE | | 5554 | 124K| 23 |
| 2 | NESTED LOOPS | | 5554 | 124K| 2 |
| 3 | MERGE JOIN CARTESIAN| | 5985M| 61G| 2 |
| 4 | TABLE ACCESS FULL | TMP_NODOS_OK_EST_BIT_03 | 1327 | 6635 | 2 |
| 5 | BUFFER SORT | | 4510K| 25M| |
| 6 | INDEX FULL SCAN | PK_R_NODOS_BIT_03 | 4510K| 25M| |
|* 7 | INDEX RANGE SCAN | IDX_NODO_CONEX_EXTERNA | 1 | 12 | |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("B"."NODO_B_ID"="C"."NODO_ID" AND "A"."NODO_ID"="B"."NODO_A_ID")

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Otro producto cartesiano. En este caso el cartesiano sabe muy bien lo que hace. Cruza casi 6.000 millones de filas (en total 61 gigas de información) y un coste mínimo. Vaya paradoja.

Un detalle para entender esta decisión: no existen restricciones de Primary Key, ni
Foreign Key, ni índices únicos, ni restricciones de Not Null.

A causa de ello, Oracle encuentra práctico combinar todos los resultados de una tabla (4,5 millones) sobre las 1400 filas de la otra tabla, en un "todos con todos".

No está mal. No obstante, hay información que Oracle, por mucho que analice las tablas, no va a poder obtener a priori. Únicamente nos interesan valores únicos de la tabla de relación, que existan en sus respectivas tablas relacionadas, pero, como digo, no existe nada que aporte a Oracle esa información.

Cambio algunos detalles de la consulta y la dejo así:

SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A,
(select distinct nodo_a_id, nodo_b_id from BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03) B,
BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
AND B.NODO_B_ID = C.NODO_ID;

Informo de dos cosas: que tengo interés en obtener los códigos distintos de la tabla de relación, y que además existan en las otras dos tablas.

El plan de ejecución cambia totalmente para ejecutarse tal como lo he dicho de otra forma. Ahora Oracle realiza este otro plan de ejecución.

SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5554 | 200K| | 13626 |
| 1 | NESTED LOOPS | | 5554 | 200K| | 13626 |
| 2 | NESTED LOOPS | | 5554 | 168K| | 13626 |
| 3 | VIEW | | 7159K| 177M| | 13626 |
| 4 | SORT UNIQUE | | 7159K| 81M| 273M| 13626 |
| 5 | TABLE ACCESS FULL| RE_R_CONEX_EXTERNAS_BIT_03 | 7159K| 81M| | 2589 |
|* 6 | INDEX UNIQUE SCAN | PK_TMP_MANEL_03 | 1 | 5 | | |
|* 7 | INDEX UNIQUE SCAN | PK_R_NODOS_BIT_03 | 1 | 6 | | |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("B"."NODO_B_ID"="C"."NODO_ID")
7 - access("A"."NODO_ID"="B"."NODO_A_ID")

El coste ahora parece haberse disparado por completo. 13626 unidades de coste. Respecto al coste anterior, de sólo 23. Pero ahora no aparece el cartesiano y parece que la ejecución es más fiel a lo que queremos.

Acabo de lanzar la ejecución: un minuto con veinte segundos.
Bien.

Oracle Tips : Cambiar parametro character set en la base de datos

2009-08-31T11:25:00.001-04:00

1. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT *
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

Parameter Value
NLS_CHARACTERSET AL32UTF8

If you want to change NLS_CHARACTERSET from AL32UTF8 to TH8TISASCII

2. Connect to user SYS

3. Update value

UPDATE PROPS$
SET VALUE$ = 'TH8TISASCII'
WHERE NAME = 'NLS_CHARACTERSET';

COMMIT;

4. Restart database

SHUTDOWN IMMEDIATE;

STARTUP;

5. Check NLS_CHARACTERSET
Connect to user SYS or SYSTEM

SELECT * FROM
V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';

Parameter Value
NLS_CHARACTERSET TH8TISASCII

Optimizacion de Consultas SQL en Oracle

2009-08-31T11:20:00.000-04:00

La siguiente consulta se ha cancelado tras 15 horas y 37 minutos de ejecución.

SQL> select count(*) from (SELECT BITOWN03.BS_C_CLIENTES_BIT_03.DNICIF_DE,
2 BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID,
3 HSCONTRATOS_ESTADOS.ESTADO_DE,
4 BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT,
5 BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID
6 FROM
7 BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
8 BITOWN03.LK_C_ESTADOS_BIT_03 HSCONTRATOS_ESTADOS,
9 BITOWN03.BS_C_CLIENTES_BIT_03,
10 BITOWN03.BS_C_CONTRATOS_BIT_03 CONTRATOS
11 WHERE BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID = CONTRATOS.ABONADO_ID
12 AND BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID = CONTRATOS.CONTRATO_ID
13 AND BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID = HSCONTRATOS_ESTADOS.ESTADO_ID
14 AND BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT
15 BETWEEN to_date('01-06-2005 00:00:00','DD-MM-YYYY HH24:MI:SS')
16 AND to_date('30-06-2005 23:59:59','DD-MM-YYYY HH24:MI:SS')
17 AND BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID = 'BA');
BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
*
ERROR at line 7:
ORA-00028: your session has been killed

Elapsed: 15:37:01.66

con el siguiente plan de ejecución:
SQL> @C:\ORACLE\ORA92\RDBMS\ADMIN\UTLXPLS

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 461 | 24433 | 16 | | |
| 1 | NESTED LOOPS | | 461 | 24433 | 16 | | |
| 2 | MERGE JOIN CARTESIAN | | 1448M| 58G| 16 | | |
| 3 | NESTED LOOPS | | 971 | 35927 | 16 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | LK_C_ESTADOS_BIT_03 | 1 | 20 | 1 | | |
|* 5 | INDEX UNIQUE SCAN | PK_C_ESTADOS_BIT_03 | 1 | | | | |
|* 6 | INDEX RANGE SCAN | IDX_FECHA_ESTADO_CONT_JAVIER | 971 | 16507 | 15 | | |
| 7 | BUFFER SORT | | 1491K| 8739K| 1 | | |
| 8 | INDEX FULL SCAN | PK_C_CLIENTES_BIT_03 | 1491K| 8739K| | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| BS_C_CONTRATOS_BIT_03 | 1 | 10 | | ROWID | ROW L |
|* 10 | INDEX UNIQUE SCAN | PK_C_CONTRATOS_BIT_03 | 1 | | | | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("HSCONTRATOS_ESTADOS"."ESTADO_ID"='BA')
6 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT">=TO_DATE('2005-06-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA' AND
"HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT"<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
filter("HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"="HSCONTRATOS_ESTADOS"."ESTADO_ID" AND
"HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA')
9 - filter("BS_C_CLIENTES_BIT_03"."ABONADO_ID"="CONTRATOS"."ABONADO_ID")
10 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."CONTRATO_ID"="CONTRATOS"."CONTRATO_ID")

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Las tablas contienen estadísticas fieles. El producto cartesiano tampoco engaña: cruzará 58 Gb. para atender nuestra petición. De todas maneras, algo falla. Ese plan no es óptimo.

Las estadísticas se generan mediante la siguiente llamada a DBMS_STATS.

SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>usuario,
3 TABNAME=>tabla,
4 ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
5 METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1',
6 DEGREE=>4);
7 end;
8 /

PL/SQL procedure successfully completed.

Es preciso no únicamente analizar las tablas, sino también los índices. Algunos de ellos están analizados con el procedimiento DBMS_STATS.GATHER_INDEX_STATS, pero una de las tablas está particionada. Lo mejor y más simple es incluir el parámetro CASCADE=>TRUE en la llamada a DBMS_STATS.

Es cierto que el coste de análisis de estas tablas incrementará. Además, el parámetro CASCADE afecta, no sólo a índices, sino también a vistas materializadas, particiones, etc. (es decir, a todas sus dependencias).

Con las tablas analizadas de este modo, el nuevo plan de ejecución es éste:

SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 471 | 30615 | 405 |
|* 1 | HASH JOIN | | 471 | 30615 | 405 |
|* 2 | INDEX RANGE SCAN | IDX_FECHA_ESTADO_CONT_JAVIER | 8740 | 145K| 75 |
| 3 | NESTED LOOPS | | 771K| 35M| 257 |
| 4 | NESTED LOOPS | | 1626K| 46M| 257 |
| 5 | TABLE ACCESS BY INDEX ROWID| LK_C_ESTADOS_BIT_03 | 1 | 20 | 1 |
|* 6 | INDEX UNIQUE SCAN | PK_C_ESTADOS_BIT_03 | 1 | | |
| 7 | INDEX FAST FULL SCAN | IDX_ABONADO_CONTRATO | 1626K| 15M| 256 |
| 8 | TABLE ACCESS BY INDEX ROWID | BS_C_CLIENTES_BIT_03 | 1 | 18 | |
|* 9 | INDEX UNIQUE SCAN | PK_C_CLIENTES_BIT_03 | 1 | | |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."CONTRATO_ID"="CONTRATOS"."CONTRATO_ID" AND
"HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"="HSCONTRATOS_ESTADOS"."ESTADO_ID")
2 - access("HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT">=TO_DATE('2005-06-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA' AND
"HS_C_ESTADOS_CONTRATO_BIT_03"."FEC_ESTADO_DT"<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd
hh24:mi:ss'))
filter("HS_C_ESTADOS_CONTRATO_BIT_03"."ESTADO_ID"='BA')
6 - access("HSCONTRATOS_ESTADOS"."ESTADO_ID"='BA')
9 - access("BS_C_CLIENTES_BIT_03"."ABONADO_ID"="CONTRATOS"."ABONADO_ID")

Note: cpu costing is off

30 rows selected.

La ejecución ahora sólo tarda: 3 segundos.
El coste de análisis aumenta de 26 minutos a 32 minutos. La ganancia justifica el coste.

Muy bien. :-)

# posted by Javier Morales @ 6:13 PM 0 comments

lunes, octubre 17, 2005

NOT IN vs. NOT EXISTS.

Para Fernando, por la que le espera. :-P

La siguiente consulta se ha cancelado tras 5 horas y 11 minutos en ejecutarse.

SELECT count(CLI.COD_ABONADO)
FROM BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
BITOWN02.TE_ERRORES_BIT_02 TE
WHERE CLI.ROWID = TE.FILA_ID (+)
AND 'TM_C_CLIENTES_SAC_02' = TE.TABLA_DE (+)
AND TE.ERROR_ID IS NULL
AND CLI.COD_ABONADO NOT IN (
SELECT CU.ABONADO_id
FROM BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, BITOWN03.BS_C_CUENTAS_BIT_03 CU
WHERE CU.CUENTA_ID=CONT.CUENTA_ID
);

con el siguiente plan de ejecución:
SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 3343 | | |
| 1 | SORT AGGREGATE | | 1 | 44 | | | |
|* 2 | FILTER | | | | | | |
|* 3 | FILTER | | | | | | |
|* 4 | HASH JOIN OUTER | | | | | | |
| 5 | TABLE ACCESS FULL | TM_C_CLIENTES_SAC_02 | 74323 | 870K| 2407 | | |
| 6 | TABLE ACCESS FULL | TE_M_ERRORES_BIT_01 | 1 | 32 | 2 | 13 | 13 |
| 7 | NESTED LOOPS | | 1640K| 50M| 922 | | |
| 8 | PARTITION LIST ALL | | | | | 1 | 7 |
| 9 | TABLE ACCESS FULL | BS_C_CONTRATOS_BIT_03 | 1640K| 20M| 922 | 1 | 7 |
|* 10 | TABLE ACCESS BY INDEX ROWID| BS_C_CUENTAS_BIT_03 | 1 | 19 | | | |
|* 11 | INDEX UNIQUE SCAN | PK_C_CUENTAS_BIT_03 | 1 | | | | |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "BITOWN03"."BS_C_CUENTAS_BIT_03"
"CU","BITOWN03"."BS_C_CONTRATOS_BIT_03" "CONT" WHERE "CU"."CUENTA_ID"="CONT"."CUENTA_ID" AND
LNNVL("CU"."ABONADO_ID"<>:B1)))
3 - filter("TE_M_ERRORES_BIT_01"."ERROR_ID" IS NULL)
4 - access("SYS_ALIAS_1".ROWID="TE_M_ERRORES_BIT_01"."FILA_ID"(+))
10 - filter(LNNVL("CU"."ABONADO_ID"<>:B1))
11 - access("CU"."CUENTA_ID"="CONT"."CUENTA_ID")

Note: cpu costing is off

30 rows selected.

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

En este caso, la consulta con IN tiene un coste aceptable. No obstante, después de cinco horas de ejecución, es de sospechar que la cosa no va muy bien. La estrategia de ejecución es realizar dos FILTER: el primero para el OuterJoin de Clientes sin errores y el segundo para combinarlo (en un pesadísimo NestedLoops) con las cuentas con contratos.

Como la subconsulta está resultando más pesada incluso que la principal, es posible que sustituir IN por la cláusula EXISTS sea una buena estrategia.

Sí, también tengo cuidado que no haya códigos con valor NULL para resolver la consulta, ya que NOT IN y NOT EXISTS no son lo mismo.

Sustituyo NOT IN por NOT EXISTS y la consulta queda de este modo:

explain plan for
SELECT count(CLI.COD_ABONADO)
FROM BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
BITOWN02.TE_ERRORES_BIT_02 TE
WHERE CLI.ROWID = TE.FILA_ID (+)
AND 'TM_C_CLIENTES_SAC_02' = TE.TABLA_DE (+)
AND TE.ERROR_ID IS NULL
AND not exists (
SELECT null
FROM BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, BITOWN03.BS_C_CUENTAS_BIT_03 CU
WHERE CLI.COD_ABONADO=CU.ABONADO_ID AND
CU.CUENTA_ID=CONT.CUENTA_ID);

El plan de ejecución resultante parece ser similar al anterior, incluso su coste parece peor.

SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | | 4946 | | |
| 1 | SORT AGGREGATE | | 1 | 57 | | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | HASH JOIN OUTER | | | | | | | |
|* 4 | HASH JOIN ANTI | | 1486K| 35M| 34M| 4269 | | |
| 5 | TABLE ACCESS FULL | TM_C_CLIENTES_SAC_02 | 1486K| 17M| | 2407 | | |
| 6 | VIEW | VW_SQ_1 | 1640K| 20M| | 922 | | |
| 7 | NESTED LOOPS | | 1640K| 50M| | 922 | | |
| 8 | PARTITION LIST ALL | | | | | | 1 | 7 |
| 9 | TABLE ACCESS FULL | BS_C_CONTRATOS_BIT_03 | 1640K| 20M| | 922 | 1 | 7 |
| 10 | TABLE ACCESS BY INDEX ROWID| BS_C_CUENTAS_BIT_03 | 1 | 19 | | | | |
|* 11 | INDEX UNIQUE SCAN | PK_C_CUENTAS_BIT_03 | 1 | | | | | |
| 12 | TABLE ACCESS FULL | TE_M_ERRORES_BIT_01 | 1 | 32 | | 2 | 13 | 13 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("TE_M_ERRORES_BIT_01"."ERROR_ID" IS NULL)
3 - access("CLI".ROWID="TE_M_ERRORES_BIT_01"."FILA_ID"(+))
4 - access("CLI"."COD_ABONADO"="VW_SQ_1"."ABONADO_ID")
11 - access("CU"."CUENTA_ID"="CONT"."CUENTA_ID")

Note: cpu costing is off

Acabo de lanzar la ejecución: 28 segundos.
Très bien. :-)

# posted by Javier Morales @ 6:12 PM 0 comments

jueves, octubre 13, 2005

Dílo de otra forma.

Para Manel Moreno, que no me ha dado ningún beso por ésto. :P

La siguiente consulta tardaba 11 horas en ejecutarse.

SELECT DISTINCT A.NODO_ID, B.NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A,
BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03 B,
BITOWN03.TMP_NODOS_OK_EST_BIT_03 C
WHERE A.NODO_ID = B.NODO_A_ID
AND B.NODO_B_ID = C.NODO_ID;

con el siguiente plan de ejecución:
SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5554 | 124K| 23 |
| 1 | SORT UNIQUE | | 5554 | 124K| 23 |
| 2 | NESTED LOOPS | | 5554 | 124K| 2 |
| 3 | MERGE JOIN CARTESIAN| | 5985M| 61G| 2 |
| 4 | TABLE ACCESS FULL | TMP_NODOS_OK_EST_BIT_03 | 1327 | 6635 | 2 |
| 5 | BUFFER SORT | | 4510K| 25M| |
| 6 | INDEX FULL SCAN | PK_R_NODOS_BIT_03 | 4510K| 25M| |
|* 7 | INDEX RANGE SCAN | IDX_NODO_CONEX_EXTERNA | 1 | 12 | |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("B"."NODO_B_ID"="C"."NODO_ID" AND "A"."NODO_ID"="B"."NODO_A_ID")

Note: cpu costing is off

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

# posted by Javier Morales @ 6:11 PM 0 comments

martes, octubre 11, 2005

Mala Cardinalidad

Para Cristina Álvarez, quien confió en mi desde el primer momento y lo disimuló con toda su alma. :)

La siguiente consulta ha sido cancelada tras una hora y veinte minutos de ejecución.

SELECT N2.ENTIDAD_ID,
CLI.rowid row_id, cli.*,
MAX(REL.ABONADO_PADRE_ID) OVER (PARTITION BY ABONADO_HIJO_ID) REL_ABONADO_PADRE_ID
FROM BITOWN03.BS_V_MGEC_NODO_BIT_03 N1,
BITOWN03.BS_V_MGEC_NODO_BIT_03 N2,
BITOWN03.BS_V_MGEC_REL_NODOS_BIT_03 RN,
BITOWN02.TM_C_CLIENTES_SAC_02 CLI,
BITOWN03.RE_C_RELACIONES_ABONADO_BIT_03 REL
WHERE N1.ENTIDAD_ID= CLI.COD_DNICIF
AND N1.NODO_ID=RN.NODO_ID
AND RN.TIPO_RELACION_ID=2 AND RN.FECHA_FIN_DT IS NULL
AND RN.NODO_PADRE_ID=N2.NODO_ID
AND CLI.COD_ABONADO = REL.ABONADO_HIJO_ID (+);

con el siguiente plan de ejecución:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 278 | 2496 |
| 1 | SORT UNIQUE | | 1 | 278 | 2496 |
| 2 | WINDOW SORT | | 1 | 278 | 2496 |
| 3 | NESTED LOOPS | | 1 | 278 | 2479 |
| 4 | NESTED LOOPS | | 1 | 261 | 2478 |
| 5 | NESTED LOOPS OUTER | | 1 | 244 | 2477 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 232 | 2477 |
|* 7 | TABLE ACCESS FULL | BS_V_MGEC_REL_NODOS_BIT_03 | 1 | 22 | 70 |
| 8 | BUFFER SORT | | 1486K| 297M| 2407 |
| 9 | TABLE ACCESS FULL | TM_C_CLIENTES_SAC_02 | 1486K| 297M| 2407 |
|* 10 | INDEX FULL SCAN | PK_C_RELACIONES_ABONADO_BIT_03 | 1 | 12 | |
|* 11 | TABLE ACCESS BY INDEX ROWID| BS_V_MGEC_NODO_BIT_03 | 1 | 17 | 1 |
|* 12 | INDEX UNIQUE SCAN | PK_V_MGEC_NODO_BIT_03 | 1 | | |
| 13 | TABLE ACCESS BY INDEX ROWID | BS_V_MGEC_NODO_BIT_03 | 1 | 17 | 1 |
|* 14 | INDEX UNIQUE SCAN | PK_V_MGEC_NODO_BIT_03 | 1 | | |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - filter("RN"."TIPO_RELACION_ID"=2 AND "RN"."FECHA_FIN_DT" IS NULL)
10 - access("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))
filter("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))
11 - filter("N1"."ENTIDAD_ID"="CLI"."COD_DNICIF")
12 - access("N1"."NODO_ID"="RN"."NODO_ID")
14 - access("RN"."NODO_PADRE_ID"="N2"."NODO_ID")

********************************************************************************
SOLUCIÓN al caso.
********************************************************************************

Omitiendo el detalle que la cláusula distinct sobra. La mantenemos para que los planes resulten de ejecuciones similares.

Parece un plan bastante aceptable. No obstante, un producto cartesiano llama la atención... ¿únicamente se obtiene una fila? en ese caso, el cartesiano no hace daño. Vamos a consultar el total de filas de la tabla, si la tabla tiene generadas estadísticas (la columna num_rows de user_tables puede darnos una pista) y si el filtro de la operación 7 realmente resuelve una única fila.

SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03;

COUNT(*)
----------
118907

SQL> select table_name, num_rows from dba_tables where
2 table_name='BS_V_MGEC_REL_NODOS_BIT_03';

TABLE_NAME NUM_ROWS
------------------------------ ----------
BS_V_MGEC_REL_NODOS_BIT_03 118907

SQL> select count(*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03
2 where TIPO_RELACION_ID=2 AND FECHA_FIN_DT IS NULL; --> FILTRO OPERACION 7

COUNT(*)
----------
12844

Pues parece que no... Se está produciendo un producto cartesiano de 1,5 millones de filas (operación 9 TABLE FULL SCAN) sobre 12844 elementos. Oracle está estimando mal la cardinalidad de las filas. Cree que con el filtro 7 únicamente obtendrá una fila y por eso el producto cartesiano no dispara el coste.

Graso error.

Es preciso analizar CORRECTAMENTE las tablas implicadas, indicando que se analicen también los valores de las columnas implicadas. Los comandos para realizar ese análisis son:

SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'usuario',TABNAME=>'BS_V_MGEC_REL_NODOS_BIT_03',METHOD_OPT=>'for all columns');

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.71
SQL> exec dbms_stats.gather_table_stats(OWNNAME=>'usuario',TABNAME=>'BS_V_MGEC_NODO_BIT_03',METHOD_OPT=>'for all columns');

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.57

De esta forma, las estadísticas tienen también información sobre la cardinalidad de las columnas y el plan de ejecución cambia sustancialmente:

SQL> @c:\oracle\ora92\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 209K| 53M| | 12462 |
| 1 | SORT UNIQUE | | 209K| 53M| 112M| 12462 |
| 2 | WINDOW SORT | | 209K| 53M| 112M| 12462 |
| 3 | NESTED LOOPS OUTER | | 209K| 53M| | 4217 |
|* 4 | HASH JOIN | | 209K| 51M| | 4217 |
|* 5 | HASH JOIN | | 12841 | 589K| | 320 |
|* 6 | HASH JOIN | | 12841 | 388K| | 192 |
|* 7 | TABLE ACCESS FULL| BS_V_MGEC_REL_NODOS_BIT_03 | 12841 | 188K| | 70 |
| 8 | TABLE ACCESS FULL| BS_V_MGEC_NODO_BIT_03 | 128K| 2012K| | 112 |
| 9 | TABLE ACCESS FULL | BS_V_MGEC_NODO_BIT_03 | 128K| 2012K| | 112 |
| 10 | TABLE ACCESS FULL | TM_C_CLIENTES_SAC_02 | 1486K| 297M| | 2407 |
|* 11 | INDEX FULL SCAN | PK_C_RELACIONES_ABONADO_BIT_03 | 1 | 12 | | |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("N1"."ENTIDAD_ID"="CLI"."COD_DNICIF")
5 - access("RN"."NODO_PADRE_ID"="N2"."NODO_ID")
6 - access("N1"."NODO_ID"="RN"."NODO_ID")
7 - filter("RN"."TIPO_RELACION_ID"=2 AND "RN"."FECHA_FIN_DT" IS NULL)
11 - access("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))
filter("CLI"."COD_ABONADO"="REL"."ABONADO_HIJO_ID"(+))

Note: cpu costing is off

Efectivamente, el coste del plan es mayor, pero REAL.
Sólo ha tardado 23 segundos!!

Reparar Bloques corruptos

2009-01-02T14:22:00.000-03:00

Unos de los mas dificiles errores que nos podemos encontrar en una Base de datos es que surjan bloques de datos corruptos.

Si se nos presenta este error experimentaremos un incremento de tiempo en las queries o recibiriamos errores ORA-01498.

Oracle tiene varias herramientas que nos ayudan a identificar y fijar los datos corruptos que se estan dando en una base de datos:

a) ANALYZE TABLE table_name VALIDATE STRUCTURE.
b) DBVERIFY.
c) DB_BLOCK_CHECKING parametro.
d) DBMS_REPAIR

ANALYZE TABLE

Esta sentencia se utiliza para validar la estructura de los objetos. Si esta sentencia devuelva un error significa que existen bloques erroneos correspondientes a esa tabla.

sql> analyze table MiSquema.Mitabla validate structure;

UTILIDAD DBVERIFY

Se utiliza para validar si existen bloques corruptos en un determinado datafile. Es necesario indicarle mediante el pametro blocksize el tamaño del bloque que tenemos configurada en el parametro de inicializacion.

>dbv blocksize=8192 file=MiDatafile.dbf

DB_BLOCK_CHEKING

DB_BLOCK_CHEKING es un parametro de inicializacion que obliga a hacer u testeo de los bloques de un tablespace cada vez que se va a hacer una modificacion.

DBMS_REPAIR

Este paquete permite identificar y fijar los bloques corruptos en tablas e indices. Este paquete se corresponde de varios procesos almacenados:

CHECK_OBJECT: Detecta si existen bloques corruptos en tablas o indices.
FIX_CORRUPT_BLOCKS: Marca aquellos bloques corruptos.
DUMP_ORPHAN_KEYS: indica aquellos indices que eran apuntados por bloques corruptos.
REBUILT_FREELISTS
SEGMENT_FIX_STATUS
SKIP_CORRUPT_BLOCKS: Se debe de utilizar para evitar el ORA-1578 que devolveria tras fijar un bloque corrupto.
ADMIN_TABLES:

Vamos a simular la identificacion y fijado de datos corruptos dentro de una tabla:

sql> connect / as sysdba
sql> analyze table MiSquema.Mitabla validate structure;
SALIDA: ORA-1498
Examinaremos el fichero de trazas para identificar el fichero corrupto:
SALIDA: data_block_dump
=============
nrow=5
Creamos una tabla de recuperación para almacenar la informacion devuelta por este paquete.

dbms_repair.dbms_tables
(table_name=>’REPAIR_TABLE’,
table_type=>dbms_reparir.repair_table,
action=> dbms_repair.create_action,
tablespace=>’USERS’
);

Comprobamos si existen bloques corruptos en la tabla.

DECLARE
rpr_count int;
BEGIN
dbms_repair.check_object
(schema_name =>’MiSquema’,
object_name=>’MiTabla’,
repair_table_name=>’REPAIR_TABLE’,
corrupt_count=>rpr_count);
END

En rpr_count almacena el numero de bloques corruptos que ha encontrado.

La siguiente query muestra la informacion sobre el objeto afectado por el bloque corrupto.

sql>select object_name, block_id, corrupt_type, marked_corrupt, from REPAIR_TABLE;

Antes de marcar el bloque como no utilizable deberia hacerse un copia de seguridad de la tabla.

sql> create table MiTable_back as
select * from MiTable
where dbms_rowid.rowid_block_number(rowid)=5
and dbms_rowid.rowid_to_absolute_fno(rowid,’MiSquema’,'MiTable’)=4

Marcamos el bloque como no utilizable:

DECLARE
fix_block_count int;
BEGIN
fix_block_count:=0;
dbms_repair.fix_corrupt_blocks(
schema_name=>’MiSquema’,
object_name=>’MiTable’,
object_type=> dbms_repair.table_object,
repair_table_name=>’REPAIR_TABLE’,
fix_count=>fix_block_count);
END;

Referencia: http://mogukiller.wordpress.com/2008/12/26/reparar-bloques-corruptos/

Oracle - Control de transacciones

2009-01-02T14:20:00.000-03:00

CONTROL DE TRANSACCIONES

DEFINICION: Conjunto de operaciones dependientes unas de otras que se realizan en la BD. Para que la transacción se ejecute, han de realizarse todas y cada una de las partes u operaciones que la componen; En el caso de que alguna falle se dará por fallida toda la transacción.

El usuario puede definir el comienzo y el fin de una transacción.

EN ORACLE:

.- Comienza la transacción con la 1º orden SQL de la sesión de usuario o con la 1º orden posterior a la finalización de la transacción anterior.

.- Finaliza cuando se ejecuta un comando de control de transacción (commit o rollback) , una orden de definición de datos (DDL) o cuando finaliza la sesión.

Una vez concluida la transacción esta no se puede deshacer.

COMANDOS DE CONTROL DE TRANSACION

.-COMMIT: Da por concluida la transacción haciendo definitivos los cambios efectuados, liberando las filas bloqueadas. Solo después de que se ejecute un commit los demás usuarios tendrán acceso a los datos modificados.

.- ROLLBACK: Da por concluida la transacción deshaciendo los cambios efectuados y liberando las filas bloqueadas.

.-ROLLBACK IMPLICITOS: Cuando un programa falla y no se controla la excepción que produjo el fallo. Se deshacen todas las operaciones excepto aquellas que dentro del programa hayan sido confirmadas.

.-SAVEPOINT : Se usa con “rollback to” para marcar puntos de salvaguarda a la hora de procesar transacciones. Esto permitirá deshacer parte de una transacción.

.-ROLLBACK TO : Deshace el trabajo realizado sobre la BD después del punto de salvaguarda. No obstante tampoco confirma el trabajo hecho hasta el punto de salvaguarda. La transacción en realidad no se confirma o rechaza hasta que aparece un commit o un rollback.

I1

I2

SAVEPOINT P1

I3

I4

SAVEPOINT P2

I5

I6

ROLLBACK TO P1

COMMIT/ROLLBACK

Los ámbitos de p1, p2 dependen del a transacción.

· TRANSACCIÓN AUTONOMA

Son las que se pueden confirmar o rechazar con independencia de lo que ocurre en la transacción en curso. Y viceversa, lo que ocurra con la transacción en curso no afecta a la autónoma.

Se usan en pequeños programas o bloques.

Se declaran en la sesión declarativa como:

PRAGMA AUTONOMOUS_TRANSACTION; (se crea la transacción autónoma)

…………………………….

Commit / rollback;

UPDATE

UPDATE

F(X)à pragma autonomous_transaction i1 i2 i3 i4 commit;

Rollback

UPDATE

UPDATE

· TRANSACIONES DE SOLO LECTURA

Se usan para garantizar la consistencia de los datos recuperados entre distintas consultas frente a posibles cambios que puedan ocurrir entre ellas.

El comienzo de una transacción de solo lectura se establece con SET TRANSACTION READ ONLY. Todas las consultas que se ejecuten después solamente verán aquellos cambios confirmados antes del comienzo de la transacción. (Como si hiciéramos una fotografía de la BD). Se confirma o rechaza con commit o rollback.

Antes del una transacción read only hay que hacer un rollback o commit o algo que finalice la transacción actual.

Rerenfia : http://www.lopezatienza.es/oracle/oracle-control-de-transacciones/

Cambio en el comportamiento del GROUP BY in Oracle 10g

2009-01-02T14:17:00.000-03:00

Si usted recientemente actualizo su versión de base de datos oracle a la versión 10g, habrá notado que las consultas que agrupan (GROUP BY) le entrega los resultados en cualquier orden.

Pues bien, desde la versión oracle 10g el comportamiento de esta clausula ha cambiado con respecto a sus predecesores. Ahora esta usando el nuevo mecanismo HASH GROUP BY, el cual no garantiza que el resultado este en ningún orden a menos que utiliza la clausula “ORDER BY”.

Realicemos una prueba para verificar este comportamiento

En Oracle 9i

SQL> select owner,count(1) from dba_segments group by owner;

OWNER COUNT(1)
CTXSYS 76
HR 25
MDSYS 53
ODM 82
ODM_MTR 12
OE 46
OLAPSYS 149
ORDSYS 7
OUTLN 6

Plan de Ejecución

SELECT STATEMENT CHOOSE
50 SORT GROUP BY
49 VIEW SYS.SYS_DBA_SEGS
48 UNION-ALL
……

En Oracle 10g

SQL> select owner,count(1) from dba_segments group by owner;

NEURONET 9
HR2 34
HABITAT 6
MDSYS 125
CTA_CTE1 392
RMAN 132
TSMSYS 4
DMSYS 4
DESIGNER6I 1902
DESIGNERTEST 1903

Plan de Ejecución

SELECT STATEMENT ALL_ROWSCost: 1,689 Bytes: 23,562 Cardinality: 1,386
45 HASH GROUP BY Cost: 1,689 Bytes: 23,562 Cardinality: 1,386
44 VIEW VIEW SYS.SYS_DBA_SEGS Cost: 1,688 Bytes: 23,562 Cardinality: 1,386
43 UNION-ALL
……

Como podrá observarse, el primer resultado (oracle 9i) es entregado ordenado ascendentemente, mientras que el segundo (oracle 10g) resultado no tiene orden alguno

Se puede apreciar en los planes de ejecución que utilizan mecanismos distintos para resolver la consulta, mientras en la primera utiliza “SORT GROUP BY”, la segunda utiliza el nuevo mecanismo “HASH GROUP BY”.

Deshabilitando el “HASH GROUP BY” en oracle 10g

Existen 2 maneras de deshabilitar este comportamiento del group by:
Una es a nivel de session y otra a nivel de base de datos, aunque siempre es posible agregar el order by en las consultas que asi lo requieran.

A nivel de sesion se puede configurar el parametro oculto _gby_hash_aggregation_enabled

alter session set “_gby_hash_aggregation_enabled” = FALSE; para versión oracle 9i

A nivel de base de datos se puede configurar el parametro optimizer_features_enabled

alter system set optimizer_features_enable=’9.2.0′;

alter system set optimizer_features_enable=’8.1.7′;

Estas ultimas configuracion, no requiere el reinicio de la base de datos.

Referencias:

Nota Metalink : 345048.1
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1251893,00.html

Scripts de creación de Base de Datos Oracle 9i y 10g

2009-01-02T14:15:00.001-03:00

En ciertas circunstancias un DBA debe ser capaz de crear una base de datos Oracle sin ayuda de los utilitarios gráficos disponibles en Oracle (DBCA).

Es bueno experimentar con la creación manual de bases de datos Oracle, ya que entrega una experiencia que permite entender y decantar muchos conceptos en relación al funcionamiento del motor Oracle.

Este artículo describe las tareas que el DBA debe realizar para crear una base de datos en ambiente (Linux-Unix) haciendo uso solo de la clásica consola de administración sqlplus.

Pasos para crear una base de datos Oracle:

Decidir nombre único para la instancia, nombre de base de datos, tamaño del bloque Oracle, set de caracteres, número máximo de archivos de datos, y número máximo de archivos de redolog.
Decidir la estructura de almacenamiento físico de la base de datos (ASM, File System, Raw Devices) . En el ejemplo se usa almacenamiento por file system, y se han definido y creado los puntos de montaje de acuerdo al modelo OFA de Oracle.
Copiar y editar el archivo de parámetros (init.ora) que permite inicializar la instancia Oracle.
Configurar las variables apropiadas del sistema operativo (ORACLE_SID), otras variables tales como ORACLE_HOME, ORACLE_BASE deberian estar previamente definidas.
Crear archivo de password (según el valor que se haya definido para el parámetro REMOTE_LOGIN_PASSWORDFILE)
Invocar el SQLPLUS y conectarse a la base como sysdba.
Iniciar la instancia en estado NOMOUNT. En este estado se crea una base de datos nueva.
Crear la base de datos (ejecutar script de creación de la base de datos)

Ejemplo de archivo de parámetro inittest.ora

*.audit_file_dest=’/u01/app/oracle/admin/test/adump’
*.background_dump_dest=’/u01/app/oracle/admin/test/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’/u02/oradata/test/control01.ctl’, ‘/u02/oradata/test/control02.ctl’,'/u02/oradata/test/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/test/cdump’
*.db_block_size=8192
*.db_domain=’midominio.cl’
*.db_file_multiblock_read_count=16
*.db_name=’test’
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=92274688
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=277872640
*.undo_management=’AUTO’
*.undo_tablespace=’TS_UNDO’
*.user_dump_dest=’/u01/app/oracle/admin/test/udump’

Ejemplo de Creación Manual de Base de Datos Oracle 9i

En los ejemplos que vienen a continuación se ha preparado un archivo llamado inittest.ora que contiene los parámetros de la instancia llamada test.

u01/app/oracle/database/oracle9iR2/dbs >sqlplus “/ as sysdba”

SQL*Plus: Release 9.2.0.2.0 - Production on Wed Apr 05 14:08:37 2006

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/inittest.ora
ORACLE instance started.

Total System Global Area 160925320 bytes
Fixed Size 730760 bytes
Variable Size 109051904 bytes
Database Buffers 50331648 bytes
Redo Buffers 811008 bytes

SQL> CREATE DATABASE test
LOGFILE group 1 (’/u01/oradata/test/redolog1a.dbf’,
‘/u02/oradata/test/redolog1b.dbf’ ) SIZE 10M,
group 2 (’/u01/oradata/test/redolog2a.dbf’,
‘/u02/oradata/test/redolog2b.dbf’ ) SIZE 10M,
group 3 (’/u01/oradata/test/redolog3a.dbf’,
‘/u02/oradata /test/redolog3b.dbf’ ) SIZE 10M
DATAFILE ‘/u02/oradata/test/system01.dbf’ SIZE 200M
CHARACTER SET WE8ISO8859P1
national character set utf8
EXTENT MANAGEMENT LOCAL
undo tablespace ts_undo
datafile ‘/u02/oradata/test/undo01.dbf’
size 50M
default temporary tablespace ts_temp
tempfile ‘/u02/oradata/test/temp01.dbf’
size 50M autoextend on next 50M maxsize 300M;

Si se genera un error ORA-01031: insufficient privileges, eso significa que lo más probable, es que el usuario actual no está en el grupo dba (en unix), o en el grupo ORA_DBA (Windows).

Si el archivo init.ora no está en su ubicación por defecto o no se ha encontrado con el atributo pfile, se genera un error ORA-01078: failure in processing system parameters y se emite un error LRM-00109: could not open parameter file ‘/u01/app/oracle/product/database/9.2.0/db_1/dbs/inittest.ora’

Ejemplo de Creación Manual de Base de Datos Oracle 10g

/u01/app/oracle/product/database/10.2.0/db_1/dbs >sqlplus “/ as sysdba”

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 05 14:08:37 2007

Connected to an idle instance.

SQL> startup nomount pfile=?/dbs/inittest.ora
ORACLE instance started.

Total System Global Area 160925320 bytes
Fixed Size 730760 bytes
Variable Size 109051904 bytes
Database Buffers 50331648 bytes
Redo Buffers 811008 bytes

SQL> CREATE DATABASE test
LOGFILE group 1 (’/u01/oradata/test/redolog1a.dbf’,
‘/u02/oradata/test/redolog1b.dbf’ ) SIZE 10M,
group 2 (’/u01/oradata/test/redolog2a.dbf’,
‘/u02/oradata/test/redolog2b.dbf’ ) SIZE 10M,
group 3 (’/u01/oradata/test/redolog3a.dbf’,
‘/u02/oradata /test/redolog3b.dbf’ ) SIZE 10M
DATAFILE ‘/u02/oradata/test/system01.dbf’ SIZE 200M
CHARACTER SET WE8ISO8859P1
national character set utf8
EXTENT MANAGEMENT LOCAL
sysaux datafile ‘/u02/oradata/test/sysaux01.dbf’
size 300M
autoextend on
next 10M
maxsize unlimited
undo tablespace ts_undo
datafile ‘/u02/oradata/test/undo01.dbf’
size 50M
default temporary tablespace ts_temp
tempfile ‘/u02/oradata/test/temp01.dbf’
size 50M autoextend on next 50M maxsize 300M;

El comando create database también ejecuta un archivo cuyo nombre es determinado por el parámetro de inicio (oculto) _init_sql_file. Después de la creación de la base de datos, ésta puede ser montada y abierta para su uso.

Una vez creada la base de datos cruda, se deben completar algunas tareas adicionales:

Tareas de post creación de la base de datos

Una vez que se ha creado la base datos con el comando create database, ésta debe ser complementada con la ejecución de algunos scripts para crear el catálogo y la opción procedural que permite ejecutar programas PL/SQL.

Ejecute como SYS
· ?/rdbms/admin/catalog.sql
· ?/rdbms/admin/catproc.sql y

Donde ? representa un shortcut para el valor de la variable de ambiente $ORACLE_HOME (solo en sqlplus)

catalog.sql llama, por ejemplo, a catexp.sql que es un requisito para el utilitario exp que permite crear respaldos lógicos o dbmsstdx.sql que es un requisito para crear triggers.

El usuario system también puede ejecutar ?/sqlplus/admin/pupbld.sql. pupbld.sql crea una tabla que permite bloquear a alguien más el uso de sqlplus.

Mejorando el desempeño de consultas con QUERY REWRITE y Vistas Materializadas

2009-01-02T14:14:00.002-03:00

Desde Oracle 8i existe una funcionalidad de la base de datos Oracle llamada QUERY REWRITE, en la cual en ciertas circunstancias de configuración de parámetros y objetos de base de datos, un SQL emitido por un usuario es re-escrito por otro equivalente que tiene mejor costo. En consecuencia esta funcionalidad tiene como requisito que el optimizador esté configurado como CBO.

Un caso de aplicación frecuente en el uso de QUERY REWRITE se da en ambientes de datawarehouse, donde consultas que usan funciones de grupo sobre una fact table de millones de registros, pueden ser resueltas sobre una tabla de resumen (de cardinalidad mucho menor que la fact table) que contenga la misma información pero con mucho menos lecturas en disco.

Las tablas de resúmenes indicadas se deben implementar con Vistas Materializadas para hacer uso de la característica de QUERY REWRITE.

En un sistema de gestión de base de datos que siga el modelo relacional, una vista es una tabla virtual, que representa el resultado de una consulta. Siempre que se consulta o se actualiza una vista normal, el RDBMS convierte estas operaciones en consultas o actualizaciones de las tablas usadas para definir la vista.

Una vista materializada utiliza una aproximación diferente: el resultado de la consulta se almacena en una tabla cache real, que será actualizada de forma periódica a partir de las tablas originales en las cuales se basa la vista materializada. Esto proporciona un acceso mucho más eficiente, a costa de un incremento en el tamaño de la base de datos y a una posible falta de sincronización, es decir, que los datos de la vista pueden estar potencialmente desfasados con respecto a los datos reales, lo cual en ambientes de gestión, que se usan para la toma de decisiones estratégicas y tácticas es un tema irrelevante.

Ejemplo:

A continuación se creará una tabla objetos que hace el papel de fact table. Se crea a partir de una vista de catalogo Oracle que contiene un volumen importante de registros.

REM Preparacion de tabla con volumen grande de registros

sqlplus scott/tiger
set echo on
set termout off

drop table objetos;

create table objetos
nologging
as
select * from all_objects
union all
select * from all_objects
union all
select * from all_objects
/

REM Se agregan mas datos a la tabla objetos para hacerla más voluminosa

REM Se usa el mecanismo de DIRECT PATH para acelerar el proceso de poblamiento

insert /*+ APPEND */ into objetos
select * from objetos;
commit;
insert /*+ APPEND */ into objetos
select * from objetos;
commit;
insert /*+ APPEND */ into objetos
select * from objetos;
commit;

analyze table objetos compute statistics;
select count(*) from objetos;

REM Se hace una consulta con funciones de grupo sobre la tabla objetos.

set autotrace on
set timing on
select owner, count(*) from objetos group by owner;

OWNER COUNT(*)
—————————— ———-
CTXSYS 6264
ELAN 1272
HR 816
MDSYS 5640
ODM 9768
…

28 rows selected.

Elapsed: 00:00:07.06

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=2719 Card=28 Bytes=140)
1 0 SORT (GROUP BY) (Cost=2719 Card=28 Bytes=140)
2 1 TABLE ACCESS (FULL) OF ‘OBJETOS’
(Cost=1226 Card=708456 Bytes=3542280)

Se observa que el costo de este primer plan de ejecución es alto debido a que la consulta requiere hacer un full table scan sobre una tabla de siete millones de registros, para solo recuperar finalmente 28 registros. ¿Qué se puede hacer? Crear una vista materializada basada en la consulta y habilitar query rewrite.

REM Se le entrega privilegio de query rewrite al usuario SCOTT con el cual se va a trabajar

sqlplus / as sysdba

grant query rewrite to scott;

REM Se activa para la sesión actual la capacidad de query rewrite

sqlplus scott/tiger
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

REM Notese que la vista materializada permite query rewrite

create materialized view vm_resumen_objetos
build immediate
refresh on commit
enable query rewrite
as
select owner, count(*)
from objetos
group by owner
/

REM Se generan estadísticas para la vista materializada (requisito de CBO)

analyze table vm_resumen_objetos compute statistics;

REM Ahora se ve en acción el efecto de la VM con la misma consulta en la que se basa la VM

set autotrace traceonly

select owner, count(*)
from objetos
group by owner;
set autotrace off
set timing off

28 rows selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=2 Card=28 Bytes=252)
1 0 TABLE ACCESS (FULL) OF ‘ VM_RESUMEN_OBJETOS ‘
(Cost=2 Card=28 Bytes=252)

Se observa que el costo de este segundo plan de ejecución es mucho menor que el costo que tenía el plan de ejecución original lo cual es resultado del gran impacto que tiene en el desempeño esta segunda consulta. Esta es la idea fundamental del QUERY REWRITE.

Storing Passwords In The Database 10G

2008-09-23T19:01:00.001-04:00

DBMS_CRYPTO

The DBMS_CRYPTO package is a replacement for the DBMS_OBFUSCATION_TOOLKIT package available in Oracle 8i and 9i. The new package is easier to use and contains more cryptographic algorithms:

Source

{ORACLE_HOME}/rdbms/admin/dbmsobtk.sql

Cryptographic algorithms - DES, 3DES, AES, RC4, 3DES_2KEY
Padding forms - PKCS5, zeroes
Block cipher chaining modes - CBC, CFB, ECB, OFB
Cryptographic hash algorithms - MD5, SHA-1, MD4
Keyed hash (MAC) algorithms - HMAC_MD5, HMAC_SH1
Cryptographic pseudo-random number generator - RAW, NUMBER, BINARY_INTEGER
Database types - RAW, CLOB, BLOB

A simple example of it's usage is:

SET SERVEROUTPUT ON
DECLARE
l_credit_card_no    VARCHAR2(19) := '1234 5678 9012 3456';
l_ccn_raw           RAW(128) := UTL_RAW.cast_to_raw(l_credit_card_no);
l_key               RAW(128) := UTL_RAW.cast_to_raw('abcdefgh');

l_encrypted_raw     RAW(2048);
l_decrypted_raw     RAW(2048);
BEGIN
DBMS_OUTPUT.put_line('Original  : ' || l_credit_card_no);

l_encrypted_raw := DBMS_CRYPTO.encrypt(src => l_ccn_raw,
                                       typ => DBMS_CRYPTO.des_cbc_pkcs5,
                                       key => l_key);

DBMS_OUTPUT.put_line('Encrypted : ' ||
                           RAWTOHEX(UTL_RAW.cast_to_raw(l_encrypted_raw)));

l_decrypted_raw := DBMS_CRYPTO.decrypt(src => l_encrypted_raw,
                                       typ => DBMS_CRYPTO.des_cbc_pkcs5,
                                       key => l_key);

DBMS_OUTPUT.put_line('Decrypted : ' ||
                          UTL_RAW.cast_to_varchar2(l_decrypted_raw));
END;
/
Original  : 1234 5678 9012 3456
Encrypted : 3041423134363932354234374545463631304337384433354443433736323331354244454237324635314545
Decrypted : 1234 5678 9012 3456

PL/SQL procedure successfully completed.

Storing Passwords In The Database 8i to 9iR2

2008-09-23T18:44:00.003-04:00

When security is managed within applications there is often a need to store passwords in database tables. This in itself can lead to security issues since people with appropriate privileges can read the contents of the security tables. A common approach to solving this is to encrypt the password before storing it. The problem with encryption is that it implies a possible decryption mechanism that could expose a hole in your security. A safer alternative is to store a hash of the username and password. In this article I'll present a simple example of this process using the DBMS_OBFUSCATION_TOOLKIT package that is available in Oracle8i and Oracle9i:

Security Table

First we must build a table to hold the security information:

CREATE TABLE app_users (
id        NUMBER(10)    NOT NULL,
username  VARCHAR2(30)  NOT NULL,
password  VARCHAR2(16)  NOT NULL
)
/

ALTER TABLE app_users ADD (
CONSTRAINT app_users_pk PRIMARY KEY (id)
)
/

ALTER TABLE app_users ADD (
CONSTRAINT app_users_uk UNIQUE (username)
)
/

CREATE SEQUENCE app_users_seq
/

Security Package

Next we create the package that contains the specification of the security code:

CREATE OR REPLACE PACKAGE app_user_security AS

FUNCTION get_hash (p_username  IN  VARCHAR2,
                   p_password  IN  VARCHAR2)
  RETURN VARCHAR2;

PROCEDURE add_user (p_username  IN  VARCHAR2,
                    p_password  IN  VARCHAR2);

PROCEDURE change_password (p_username      IN  VARCHAR2,
                           p_old_password  IN  VARCHAR2,
                           p_new_password  IN  VARCHAR2);

PROCEDURE valid_user (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2);

FUNCTION valid_user (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
  RETURN BOOLEAN;

END;
/

We then create the package body to define the actual operations:

CREATE OR REPLACE PACKAGE BODY app_user_security AS

FUNCTION get_hash (p_username  IN  VARCHAR2,
                   p_password  IN  VARCHAR2)
  RETURN VARCHAR2 AS
BEGIN
  RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
    input_string => UPPER(p_username) || '/' || UPPER(p_password));
END;

PROCEDURE add_user (p_username  IN  VARCHAR2,
                    p_password  IN  VARCHAR2) AS
BEGIN
  INSERT INTO app_users (
    id,
    username,
    password
  )
  VALUES (
    app_users_seq.NEXTVAL,
    UPPER(p_username),
    get_hash(p_username, p_password)
  );

  COMMIT;
END;

PROCEDURE change_password (p_username      IN  VARCHAR2,
                           p_old_password  IN  VARCHAR2,
                           p_new_password  IN  VARCHAR2) AS
  v_rowid  ROWID;
BEGIN
  SELECT rowid
  INTO   v_rowid
  FROM   app_users
  WHERE  username = UPPER(p_username)
  AND    password = get_hash(p_username, p_old_password)
  FOR UPDATE;

  UPDATE app_users
  SET    password = get_hash(p_username, p_new_password)
  WHERE  rowid    = v_rowid;

  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;

PROCEDURE valid_user (p_username  IN  VARCHAR2,
                      p_password  IN  VARCHAR2) AS
  v_dummy  VARCHAR2(1);
BEGIN
  SELECT '1'
  INTO   v_dummy
  FROM   app_users
  WHERE  username = UPPER(p_username)
  AND    password = get_hash(p_username, p_password);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20000, 'Invalid username/password.');
END;

FUNCTION valid_user (p_username  IN  VARCHAR2,
                     p_password  IN  VARCHAR2)
  RETURN BOOLEAN AS
BEGIN
  valid_user(p_username, p_password);
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;

END;
/

The overloads of VALID_USER allow the security check to be performed in a different manner.

The GET_HASH function is used to hash the combination of the username and password. It always returns a VARCHAR2(16) regardless of the length of the input parameters. This level of compression means that the hash value may not be unique, hence the unique constraint on the USERNAME column.

The DBMS_UTILITY.GET_HASH_VALUE function could be used to replace the DBMS_OBFUSCATION_TOOLKIT.MD5 function, but the hashing algorithm of the former is not garaunteed to stay constant between database versions.

Testing

First we create a new user:

SQL> exec app_user_security.add_user('fernandov','mypass');

PL/SQL procedure successfully completed.

SQL> select * from app_users;

      ID USERNAME                       PASSWORD
---------- ------------------------------ ----------------
       1 fernandov                      f>~âÝ♀?£åeÍ?+▄}

Next we check the VALID_USER procedure:

SQL> EXEC app_user_security.valid_user('fernandov','mypass');

PL/SQL procedure successfully completed.

SQL> EXEC app_user_security.valid_user('fernandov','abcd');

*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "FVALENZUELA.APP_USER_SECURITY", line 37
ORA-06512: at line 1

Next we check the VALID_USER function:

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2    IF app_user_security.valid_user('fernandov','mypass') THEN
3      DBMS_OUTPUT.PUT_LINE('TRUE');
4    ELSE
5      DBMS_OUTPUT.PUT_LINE('FALSE');
6    END IF;
7  END;
8  /
TRUE

PL/SQL procedure successfully completed.

SQL> BEGIN
2    IF app_user_security.valid_user('fernandov','abcd') THEN
3      DBMS_OUTPUT.PUT_LINE('TRUE');
4    ELSE
5      DBMS_OUTPUT.PUT_LINE('FALSE');
6    END IF;
7  END;
8  /
FALSE

PL/SQL procedure successfully completed.

SQL>

Finally we check the CHANGE_PASSWORD procedure:

SQL> exec app_user_security.change_password('fernandov','mypass','password2');

PL/SQL procedure successfully completed.

SQL> exec app_user_security.change_password('fernandov','abcd','abcd1');
BEGIN app_user_security.change_password('tim','abcd','abcd1'); END;

*
ERROR at line 1:
ORA-20000: Invalid username/password.
ORA-06512: at "W2K1.APP_USER_SECURITY", line 47
ORA-06512: at line 1


PD:article original http://www.oracle-base.com/articles/9i/StoringPasswordsInTheDatabase9i.php

The Power of ASH (Active Session History)

2008-09-22T13:36:00.000-04:00

Oracle 10g, brings many new features through which one can easily tune the bad sqls or also can diagnose the database performance issues.

Using database metrics, active session history and time model views.

Following query fetchs top sqls spent more on cpu/wait/io. (Thanks to Kyle Hailey for this script):

select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by ash.SQL_ID;

SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
bqts5m5y267ct 0 0 20 20
4gd6b1r53yt88 0 16 1 17
35rqnp0hn3p3j 0 13 0 13
3shtm7x3a54qu 0 0 8 8
0hf43mhpx086p 0 0 4 4

Use any of v$sql to get the sql_text for one of the above sql_id.

SELECT sql_text FROM v$sqlarea WHERE sql_id = 'bqts5m5y267ct';

dbms_xplan.display_awr can be used to extract the sql plan for this sql_id.

SELECT * FROM table(dbms_xplan.display_awr('bqts5m5y267ct');

The above scinario was done in Oracle 10g Re.2 on SunSolaris

PD: article original http://jaffardba.blogspot.com/2006/11/power-of-ash-active-session-history.html

Oracle enable row movement tips

2008-09-22T12:50:00.000-04:00

The "enable row movement" feature of Oracle is somewhat confusing to beginners and this notes describes some times when "enable row movement" is used. For complete details, see my book "Oracle Tuning: The Definitive Reference"

Oracle has several commands to reclaim unused disk space for objects (tables and indexes). Using the "alter table xxx shrink space compact" command also has the benefit of making full-table scans run faster, as less block accesses are required. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

SQL> alter table mytable enable row movement;
Table altered

SQL> alter table mytable shrink space;
Table altered

Finding tables and indexes for shrinking

The Oracle 10g segment advisor will recommend tables that will benefit from shrinking and indexes that require rebuilding (to reclaim space).

When you add the clause "enable row movement" to a create table statement, you are giving Oracle permission to change the ROWID's. This allows Oracle to condense table rows and make it easier to reorganize tables. The enable row movement clause in used within these features:

Alter table xxx shrink space compact - When using Automatic Segment Storage Management (ASSM, a.k.a. bitmap freelists) you can issue the "alter table xxx shrink" command. to compress the table rows into less data blocks, and Oracle moves down the high water mark to release the space. This makes full-table scans run faster.

Flashback table - Using the flashback table features requires "enable row movement".

Table reorganization - An Oracle10g database can reclaim space within data segments online without affecting the ability of end users to access their data. The only thing that must be ensured before using online segment reorganization capability is that the tablespaces have the Automatic Segment Space Management (ASSM) and row movement features enabled. Oracle10g introduces the ability to reclaim space from a segment by shrinking of the segment. Shrinking a segment will make unused space available to other segments in the tablespace and may improve the performance of queries and DML operations.

The segment shrink is an online operation where the table being shrunk is open to queries and DML while the segment is being shrunk. Additionally, segment shrink is performed in-place. This is a key advantage over performing Online Table Redefinition for compaction and reclaiming space.

Finding opportunities for enable row movement

With the introduction of the alter table xxx shrink space compact syntax, the DBA gets a powerful tool for effective and easy database space management. However, the DBA needs to know what data segments experience high space waste in order to reclaim free space to the database and shrink segments. This page shows scripts that can be run to identify opportunities for using the "alter table shrink compact" command and you can use the awr_list_seg_block_space.sql script to report percentages of free space for data segments.

Caveat: Using enable row movement can corrupt any Oracle features that rely on ROWID, such as nested tables, and they should be used with caution.

PD: article original : http://www.dba-oracle.com/t_enable_row_movement.htm

Saber las Versiones de los Software que utilizas

2008-09-22T12:45:00.000-04:00

Of Versions and Figuring Them Out

One of the things that you come across quite frequently when you work with oracle support is to need to tell them the product versions or the platform versions which run your oracle applications.
since it is possible for Oracle to simulate your environment completely or not aat all in most cases its vital that you feed them with as accurate information about your environment as possible.

The current post focuses on getting the version information which are commonly asked by during a service request.

I am categorizing this post into three sections

Oracle Applications Components
Oracle Database Components
Operating System and Utilities

Oracle Applications Components

Oracle Applications version.
You may never need this but in case you are looking at a new environment then the simplest way to figure out your application version is by navigating to
Help-> About Oracle Applications from any of the forms sessions.

Version of a Oracle Applications Form (fmx) or report
To find the version of any oracle applications files
strings -a ICQTYED.fmx | grep Header
alternatively you can also use the adident command

Version of a Java class File
To find the version of a java class file
$ strings | grep '$Header'

JDBC version
In your middle tier, edit the jserv.properties file located in the iAS_ ORACLE_HOME/Apache/Jserv/etc directory
- Locate the wrapper.classpath that is pointing to the jdbc zip file
/u01/applsam/samcomn/java/jdbc14.zip

How to find the Apache version?
Go to the $iAS/Apache/Apache/bin directory and enter the following command:
$ httpd -version

Version Of The OA Framework
To find out the version of your Oracle appplication Framework
http://[host].[domain]:[portnumber]/OA_HTML/OAInfo.jsp
OA Framework Version Information
OA Framework Version 11.5.10.2CU.
MDS Version 9.0.5.4.81 (build 481)
UIX Version 2.2.18
BC4J Version 9.0.3.13.51

Oracle Application Product Version or Patch Set Level.
One of the most common things you will asked by your support engineer is the version or commonly known as the patch set level of your Oracle Applications product, while or after raising your service request. You can query this by logging on to your application database as the apss user.
select patch_level from fnd_product_installations where patch_level like '%AD%';

OJSP Version
Log in to the application server as the applmgr user
cd $OA_HTML
edit the jtflogin.jsp file to add the following line
OJSP Version: <%= application.getAttribute("oracle.jsp.versionNumber") %>
clear your cache and bounce your apache server
soruce the jtflogin.jsp from your browser
http://hcslnx03.satyam.com:8002/OA_HTML/jtflogin.jsp
OJSP Version: 1.1.3.5.2

XML Parser Version
You can find out the version of your XML Parser using the following query
SQL> select WF_EVENT_XML.XMLVersion() XML_VERSION
2 from sys.dual;
XML_VERSION
--------------------------------------------------------------------------------
Oracle XDK Java 9.0.4.0.0 Production

XML Publisher Version
To check weather XML publisher is installed or not you can query the FND_PRODUCT_INSTALLATIONS table or you can lookup the
reports in Oracle Applications Manager. You can find out the version for your XML publisher from the output of your report or from MetaInfo.class file.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.

WorkFlow Version
You can find out the version of your workflow using the following query
SQL> select TEXT Version from WF_RESOURCES
2 where TYPE = 'WFTKN' and NAME = 'WF_VERSION';
VERSION
--------------------------------------------------------------------------------
2.6.0

Oracle Database Components

Oracle RDBMS Version
You can query the version of your database user the dynamic view V$VERSION
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

Version of OPATCH
$ perl $ORACLE_HOME/OPatch/opatch.pl version
/oracle/product/v9.2.0.6_doeb10s/OPatch/opatch.pl version: 1.0.0.0.51

Listener Version
$ lsnrctl version
LSNRCTL for Solaris: Version 9.2.0.6.0 - Production on 06-JUL-2007 09:34:53
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=samsolx)(PORT=1527))
TNSLSNR for Solaris: Version 9.2.0.6.0 - Production
TNS for Solaris: Version 9.2.0.6.0 - Production
Unix Domain Socket IPC NT Protocol Adaptor for Solaris: Version 9.2.0.6.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 9.2.0.6.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 9.2.0.6.0 - Production,,
The command completed successfully

Operating Systems and Utilities

Sun Solaris Version
To check the version of you Solaris you can use the following file.
$ cat /etc/release
Solaris 8 2/02 Fujitsu_3 s28s_u7fjsv3wos_04 SPARC
Copyright 2002 Sun Microsystems, Inc. All Rights Reserved.
Assembled 08 December 2002

RedHat Linux Version
You can check the version and release of Linux from the following file
view /etc/redhat-release
Red Hat Enterprise Linux AS release 4 (Nahant Update 2)

Perl Version
You can use the perl -v or the perl - version command to find out the version of perl on your environment.
$ perl -version
This is perl, version 5.005_03 built for sun4-solaris
Copyright 1987-1999, Larry Wall

Java Version
To fine the version of Java used
$ java -version
java version "1.4.2_04"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_04-b05)
Java HotSpot(TM) Client VM (build 1.4.2_04-b05, mixed mode)

Version of Installed packages on Solaris
To find the version of the packages on Solaris
$ pkginfo -i|grep perl
application MCperl584 PERL 5.8.4 with Modules 64bit with Threads
optional VRTSperl Perl 5.8.6 for VERITAS

Version of Installed packages on Linux
To find the version of the packages on linux
rpm -qa|grep

Kernel Version of Unix
You can find the version of your kernel on UNIX by the following command
uname -a

Bit of Operating System
You can check the bit size of your OS by using the following command
$ isainfo -b
64

Bit of your Oracle Software
To check if your Oracle Binary is 32 bit or 64 bit you can use the file command on any of the oracle executables like
$ file $ORACLE_HOME/bin/oracle
/oracle/product/v9.2.0.6_doeb10s/bin/oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped