OBJETIVO
Cómo bloquear desde el sqlnet.ora el acceso a la base de datos desde una ip concreta.
BLOQUEAR EL ACCESO A ORACLE DESDE UNA IP
En caso de no tener un firewall para bloquear el acceso de ciertas ips a una base de datos lo podemos realizar a través del sqlnet.ora.
El “secreto” para bloquear o restringir el acceso por IP a la base de datos se realiza en el archivo sqlnet.ora. Este archivo lo podemos encontrar en el directorio $ORACLE_HOME/network/admin junto con los archivos tnsnames.ora y listener.ora
Editamos el archivo sqlnet.ora y añadimos las siguientes líneas:
tcp.validnode_checking = yes
Con esto conseguimos chequear los listeners que tengamos activos.
A continuación escribimos lo siguiente:
tcp.invited_nodes = (hostnameA, hostnameB)
tcp.excluded_nodes = (192.168.2.15)
Con tcp.invited_nodes puedo especificar qué máquinas quiero que su conexión sea aceptada por las base de datos.
Con tcp.excluded_nodes excluimos las máquinas que no queremos que se conecten a las bases de datos.
La idea de este mecanismo es realizar una lista de las máquinas que queremos que se conecten o realizar una lista de las máquinas que no queremos que se conecten a nuestra base de datos.
A pesar de este mecanismo de seguridad, no podemos decir que estemos totalmente exentos de recibir ataques.
Algunas reglas a tener en cuenta para generar la lista de IPS / hostnames invitados o excluidos pueden ser la siguiente:
• Poner todos los nodos excluidos en una única línea.
• Poner todos los nodos invitados en una única línea.
• Se debería incluir en el listado de nodos invitados localhost.
Después de introducir estas reglas en nuestro sqlnet.ora debemos de reiniciar los listeners de la máquina.
$ lsnrctl stop nb_listener
$ lsnrctl start nb_listener
Siendo nb_listener el nombre del listener que tengamos iniciado en la máquina y que podemos comprobar en el archivo listener.ora.
Con nuestro usuario oracle realizamos lo siguiente:
viernes, 26 de septiembre de 2008
Privilegios SYSOPER y SYSDBA
OBJETIVOS
Breve articulo para referenciar a los usuarios con privilegios sysoper,sysdba el archivo de contraseñas de oracle y el rol DBA.
INTRODUCCIÓN
Cuando nos conectamos a oracle, podemos diferenciar entre dos grupos de usuarios:grupo 1: Usuarios que sólo se pueden conectar a la base de datos cuando está abierta. grupo 2: Usuarios que se pueden conectar a la base de datos tanto cuando esté abierto como cuando esté cerrada.
Para saber sobre tipos de arranque de base de datos
Los usuarios del Grupo 2 son aquellos que tiene el privilegio SYSOPER y/o el SYSDBA.Estos dos privilegios caracterizan a los usuarios de oracle puesto que pueden hacer operaciones de "seguridad" como arranque y parada de base de datos
Para poder conectarte con estos privilegios sysdba y sysoper tienes que conectarte indicándo que quieres conectarte con esos privilegios.
Breve articulo para referenciar a los usuarios con privilegios sysoper,sysdba el archivo de contraseñas de oracle y el rol DBA.
INTRODUCCIÓN
Cuando nos conectamos a oracle, podemos diferenciar entre dos grupos de usuarios:grupo 1: Usuarios que sólo se pueden conectar a la base de datos cuando está abierta. grupo 2: Usuarios que se pueden conectar a la base de datos tanto cuando esté abierto como cuando esté cerrada.
Para saber sobre tipos de arranque de base de datos
Los usuarios del Grupo 2 son aquellos que tiene el privilegio SYSOPER y/o el SYSDBA.Estos dos privilegios caracterizan a los usuarios de oracle puesto que pueden hacer operaciones de "seguridad" como arranque y parada de base de datos
Para poder conectarte con estos privilegios sysdba y sysoper tienes que conectarte indicándo que quieres conectarte con esos privilegios.
- “sys as sysdba”
- “sys as sysoper
Archivo o fichero de contraseñas
En oracle existe un fichero de contraseñas donde se limita cual es el número de usuarios que se pueden crear en la base de datos con estos privilegios.Este fichero de oracle contiene las contraseñas de los usuarios que tienen los privilegios de SYSDBA y/o SYSOPER. Este archivo se crea cuando creamos la base de datos, sin embargo también puede ser recreado mediante la utilidad ORAPWD.Para utilizar este comando y recrear el archivo de contraseñas tenemos en cuenta lo siguiente:
El ejecutable orapwd se encuentra en $ORACLE_HOME/bin/orapwd
Ejemplo:
$orapwd file=$ORACLE_HOME/dbs/orapwdorasite.ora password=oracle entries=12
File= nombre del fichero de contraseñas.
Password= contraseña para SYS AS SYSDBA.
Entries= número máximo de DBA.
Los argumentos file y password son obligatorios.
Como vemos mediante entries limitamos el número de usuarios máximos que pueden tener ese privilegio en la base de datos...
ROL DBA
Existe un rol predefinido llamado DBA que se crea con cada base de datos Oracle. Este rol contiene TODOS los privilegios del sistema. Sólo debe concederse a los que vayan a ser superusuarios ("administradores de base de datos").
Los usuarios con este privilegio son los siguientes:
- SYS
- SYSTEM
SYS: Cuando instalamos por primera vez oracle ( versión 9i ) en caso de que el instalador no te permita cambiar las clave de este usuario la clave por defecto es CHANGE_ON_INSTALL. Con este usuario se gestion el diccionario de datos
SYSTEM: Cuando instalamos por primera vez oracle ( versión 9i ) en caso de que el instalador no te permita cambiar las clave de este usuario la clave por defecto es MANAGER. Bajo este usuario se suelen instalar las tablas de las aplicaciones, como Forms, reports .. etc
Por seguridad es conveniente cambiar estas claves
jueves, 25 de septiembre de 2008
Group by VS. Distinct
Mucho se habla sobre la diferencia entre group by y Distinct, la verdad es que en oracle parece no tener diferencia.Hice algunas pruebas para poder decir que son prácticamente lo mismo.Empecé con los siguientes:
SQL> explain plan for
2 SELECT DISTINCT campo1 FROM prueba;
Explicado.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 643035693
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 4 (25) 00:00:01
1 HASH UNIQUE 1 5 4 (25) 00:00:01
2 TABLE ACCESS FULL PRUEBA 1 5 3 (0) 00:00:01
-----------------------------------------------------------------------------
SQL> explain plan for
2 SELECT campo1 FROM prueba GROUP BY campo1;
Explicado.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 287650557
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 4 (25) 00:00:01
1 HASH GROUP BY 1 5 4 (25) 00:00:01
2 TABLE ACCESS FULL PRUEBA 1 5 3 (0) 00:00:01
-----------------------------------------------------------------------------
Como se puede ver lo único que cambia es el "HASH UNIQUE" por "HASH GROUP", el resto del plan parece ser igual.
Al ser una función hash, decidí incrementar la prueba a un set de datos más grande (suficiente para que mi PGA se quedara corta y se tuviera que pasar la tabla de hash a disco). Decidí también poner un trace nivel 10104 al proceso para ver la creación de la tabla de hash.
Las sentencias SQL son las siguientes:
WITH registros AS
(SELECT /*+MATERIALIZE*/
owner,
object_type
FROM dba_objects)
SELECT COUNT(*)
FROM
(SELECT owner,
object_type rownum
FROM
(SELECT a.owner,
b.object_type
FROM registros a,
registros b
WHERE rownum < 1000000)
GROUP BY owner,
object_type rownum);
WITH registros AS
(SELECT /*+MATERIALIZE*/
owner,
object_type
FROM dba_objects)
SELECT COUNT(*)
FROM
(SELECT DISTINCT owner,
object_type rownum
FROM
(SELECT a.owner,
b.object_type
FROM registros a,
registros b
WHERE rownum < 1000000));
Las pruebas que realicé fueron las siguientes:
SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10104 trace name context forever, level 12;
Sentencia procesada.
SQL> WITH registros AS
2 (SELECT /*+MATERIALIZE*/
...
15 WHERE rownum < 1000000));
COUNT(*)
----------
999999
SQL> oradebug tracefile_name
c:\oracle\product\admin\orcl\udump\orcl_ora_5272.trc
En ambas situaciones, las tablas de hash fueron exactamente las mismas...
SQL> explain plan for
2 SELECT DISTINCT campo1 FROM prueba;
Explicado.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 643035693
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 4 (25) 00:00:01
1 HASH UNIQUE 1 5 4 (25) 00:00:01
2 TABLE ACCESS FULL PRUEBA 1 5 3 (0) 00:00:01
-----------------------------------------------------------------------------
SQL> explain plan for
2 SELECT campo1 FROM prueba GROUP BY campo1;
Explicado.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 287650557
-----------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
-----------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 4 (25) 00:00:01
1 HASH GROUP BY 1 5 4 (25) 00:00:01
2 TABLE ACCESS FULL PRUEBA 1 5 3 (0) 00:00:01
-----------------------------------------------------------------------------
Como se puede ver lo único que cambia es el "HASH UNIQUE" por "HASH GROUP", el resto del plan parece ser igual.
Al ser una función hash, decidí incrementar la prueba a un set de datos más grande (suficiente para que mi PGA se quedara corta y se tuviera que pasar la tabla de hash a disco). Decidí también poner un trace nivel 10104 al proceso para ver la creación de la tabla de hash.
Las sentencias SQL son las siguientes:
WITH registros AS
(SELECT /*+MATERIALIZE*/
owner,
object_type
FROM dba_objects)
SELECT COUNT(*)
FROM
(SELECT owner,
object_type rownum
FROM
(SELECT a.owner,
b.object_type
FROM registros a,
registros b
WHERE rownum < 1000000)
GROUP BY owner,
object_type rownum);
WITH registros AS
(SELECT /*+MATERIALIZE*/
owner,
object_type
FROM dba_objects)
SELECT COUNT(*)
FROM
(SELECT DISTINCT owner,
object_type rownum
FROM
(SELECT a.owner,
b.object_type
FROM registros a,
registros b
WHERE rownum < 1000000));
Las pruebas que realicé fueron las siguientes:
SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10104 trace name context forever, level 12;
Sentencia procesada.
SQL> WITH registros AS
2 (SELECT /*+MATERIALIZE*/
...
15 WHERE rownum < 1000000));
COUNT(*)
----------
999999
SQL> oradebug tracefile_name
c:\oracle\product\admin\orcl\udump\orcl_ora_5272.trc
En ambas situaciones, las tablas de hash fueron exactamente las mismas...
lunes, 22 de septiembre de 2008
Mejora de desempeño con Oracle Text
Existen muchos casos de sentencias SQL que lo que intentan a partir de varios campos, saber qué registros cumplen con una palabra "clave". Imaginemos lo siguiente, llega una persona a generar una factura en un grupo que tiene 1 millón de clientes, y el dato que podemos dar para buscar el cliente a nombre de quién se factura es el nombre "Hugo", pero la definición de la tabla tiene nombre, segundo_nombre, primer_apellido, segundo_apellido y razon_social.
Imaginemos también que no se tiene ningún tipo de constraint para evitar el uso indistinto de Mayúsculas/Minúsculas.
Así que aquí tenemos un ejemplo:
SQL> create table catalogo
2 (nombre varchar2(30),
3 segundo_nombre varchar2(30),
4 primer_apellido varchar2(30),
5 segundo_apellido varchar2(30),
6 razon_social varchar2(100));
Tabla creada.
SQL> insert into CATALOGO
2 with registros as (
3 select OWNER, OBJECT_NAME, OBJECT_TYPE
4 from dba_objects
5 )
6 select
7 t2.owner,
8 substr(t2.OBJECT_NAME,mod(rownum,10),5),
9 substr(t2.OBJECT_TYPE,1,mod(rownum,10)),
10 substr(t2.OBJECT_NAME,1,mod(rownum,10)),
11 t2.owner' 't2.object_type' 't2.object_name
12 from
13 registros t1,
14 registros t2
15 where rownum <>
999999 filas creadas.
SQL> insert into catalogo values('Hugo','Enrique','Contreras','Gamiño',null);
1 fila creada.SQL> commit;
Ahora ya tenemos 1 millón de registros, y podemos hacer uso de nuestra palabra clave "ENRIQUE" que sabemos de antemano que sólo nos regresará un registro.
SQL> set timing on
SQL> set autot traceonly stat
SQL> select * from catalogo
2 where upper
(nombresegundo_nombreprimer_apellidosegundo_apellidorazon_social)
3 like '%ENRIQUE%';
Transcurrido: 00:00:14.67
Estadisticas
----------------------------------------------------------
285 recursive calls
0 db block gets
10087 consistent gets
10031 physical reads
116 redo size
712 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed>
Cómo se puede ver, hay varias situaciones aquí, la primera es que el desarrollador tuvo que concatenar todos los campos descriptivos y además se aplicó la función "UPPER". La segunda es que no importa qué índice de tipo B-TREE se cree, se terminará haciendo un Full Table Scan o Full Index Scan.
La solución qu eparece ser más óptima es con el uso de Oracle Text.
Se eligió para este ejemplo un índice de tipo "Context". Como primer paso se debe de crear un datastore de multicolumna que englobe los campos descriptivos de la tabla que queremos indexar.
SQL> begin
2 ctx_ddl.create_preference('mi_datastore', 'multi_column_datastore');
3 ctx_ddl.set_attribute('mi_datastore', 'columns',
4 'nombre,segundo_nombre,primer_apellido,segundo_apellido,razon_social');
5 end;
6 /Procedimiento PL/SQL terminado correctamente.
Una vez creado el datastore, se puede crear el índice.
SQL> create index cat_texto_idx on catalogo(nombre)
2 indextype is ctxsys.context
3 parameters('datastore mi_datastore');
Indice creado.
El índice es creado sobre la columns "nombre" y cada registro es tomado encuenta como un documento, por lo cual, oracle text nos permite buscar por el campo nombre y hacer referencia a el datastore múltiple.
SQL> select * from catalogo
2 where contains(nombre,'enrique',1)>0;
Transcurrido: 00:00:00.02
Estadisticas
----------------------------------------------------------
11 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
712 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
En este ejemplo observamos que se usa la función "Contains" de oracle text para buscar sobre un campo indexado (que en nuestro ejemplo es nombre). Se puede observar también que para este ejemplo, es indistinto el uso de mayúsculas o minúsculas. y que en lugar de leer más de 10,000 bloques de datos, sólo se leen 21 bloques. en lugar de tardar más de 10 segundos, sólo se tardó el query 0.020 segundos.
Hay algo que se debe de tomar en cuenta, un índice de contexto no es transaccional por default, es decir, a medida que los datos se van modificando (Cualquier DML) el índice queda fuera de sincronía.
SQL> insert into catalogo values('Diego','Armando','Maradona',null,null);
1 fila creada.SQL> commit;
Confirmacion terminada.
SQL> select * from catalogo
2 where contains(nombre,'maradona',1)>0;
ninguna fila seleccionada.
Para mantener la sincronía, existe el siguiente comando
SQL> begin
2 CTX_DDL.SYNC_INDEX('CAT_TEXTO_IDX','50K');
3 end;
4 /Procedimiento PL/SQL terminado correctamente.
SQL> select * from catalogo
2 where contains(nombre,'maradona',1)>0;
NOMBRE SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Diego Armando Maradona
Ya que esto puede ser inaceptable para muchos modelos de sistemas, oracle tiene una propiedad (que no es default) para poder sincronizar los índices.
SQL> drop index cat_texto_idx;Indice borrado.
SQL> create index cat_texto_idx on catalogo(nombre)
2 indextype is ctxsys.context
3 parameters('datastore mi_datastore sync (on commit)');
Indice creado.
SQL> insert into catalogo values('Eric','Daniel','Cantona',null,null);
1 fila creada.
SQL> select * from catalogo where contains(nombre,'cantona',1)>0;
ninguna fila seleccionada
SQL> commit;
Confirmacion terminada.
SQL> select * from catalogo where contains(nombre,'cantona',1)>0;
NOMBRE SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Eric Daniel Cantona
El índice quedará sincronizado cada que nosotros hagamos commit;
Todo esto es sólo un ejemplo práctico que me ayudó a resolver un problema de desempeño en un sistema, sin embargo es muy limitado en cuanto al uso de Oracle text, por eso recomiendo leer el Manual de referencia de oracle text.
Imaginemos también que no se tiene ningún tipo de constraint para evitar el uso indistinto de Mayúsculas/Minúsculas.
Así que aquí tenemos un ejemplo:
SQL> create table catalogo
2 (nombre varchar2(30),
3 segundo_nombre varchar2(30),
4 primer_apellido varchar2(30),
5 segundo_apellido varchar2(30),
6 razon_social varchar2(100));
Tabla creada.
SQL> insert into CATALOGO
2 with registros as (
3 select OWNER, OBJECT_NAME, OBJECT_TYPE
4 from dba_objects
5 )
6 select
7 t2.owner,
8 substr(t2.OBJECT_NAME,mod(rownum,10),5),
9 substr(t2.OBJECT_TYPE,1,mod(rownum,10)),
10 substr(t2.OBJECT_NAME,1,mod(rownum,10)),
11 t2.owner' 't2.object_type' 't2.object_name
12 from
13 registros t1,
14 registros t2
15 where rownum <>
999999 filas creadas.
SQL> insert into catalogo values('Hugo','Enrique','Contreras','Gamiño',null);
1 fila creada.SQL> commit;
Ahora ya tenemos 1 millón de registros, y podemos hacer uso de nuestra palabra clave "ENRIQUE" que sabemos de antemano que sólo nos regresará un registro.
SQL> set timing on
SQL> set autot traceonly stat
SQL> select * from catalogo
2 where upper
(nombresegundo_nombreprimer_apellidosegundo_apellidorazon_social)
3 like '%ENRIQUE%';
Transcurrido: 00:00:14.67
Estadisticas
----------------------------------------------------------
285 recursive calls
0 db block gets
10087 consistent gets
10031 physical reads
116 redo size
712 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed>
Cómo se puede ver, hay varias situaciones aquí, la primera es que el desarrollador tuvo que concatenar todos los campos descriptivos y además se aplicó la función "UPPER". La segunda es que no importa qué índice de tipo B-TREE se cree, se terminará haciendo un Full Table Scan o Full Index Scan.
La solución qu eparece ser más óptima es con el uso de Oracle Text.
Se eligió para este ejemplo un índice de tipo "Context". Como primer paso se debe de crear un datastore de multicolumna que englobe los campos descriptivos de la tabla que queremos indexar.
SQL> begin
2 ctx_ddl.create_preference('mi_datastore', 'multi_column_datastore');
3 ctx_ddl.set_attribute('mi_datastore', 'columns',
4 'nombre,segundo_nombre,primer_apellido,segundo_apellido,razon_social');
5 end;
6 /Procedimiento PL/SQL terminado correctamente.
Una vez creado el datastore, se puede crear el índice.
SQL> create index cat_texto_idx on catalogo(nombre)
2 indextype is ctxsys.context
3 parameters('datastore mi_datastore');
Indice creado.
El índice es creado sobre la columns "nombre" y cada registro es tomado encuenta como un documento, por lo cual, oracle text nos permite buscar por el campo nombre y hacer referencia a el datastore múltiple.
SQL> select * from catalogo
2 where contains(nombre,'enrique',1)>0;
Transcurrido: 00:00:00.02
Estadisticas
----------------------------------------------------------
11 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
712 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
En este ejemplo observamos que se usa la función "Contains" de oracle text para buscar sobre un campo indexado (que en nuestro ejemplo es nombre). Se puede observar también que para este ejemplo, es indistinto el uso de mayúsculas o minúsculas. y que en lugar de leer más de 10,000 bloques de datos, sólo se leen 21 bloques. en lugar de tardar más de 10 segundos, sólo se tardó el query 0.020 segundos.
Hay algo que se debe de tomar en cuenta, un índice de contexto no es transaccional por default, es decir, a medida que los datos se van modificando (Cualquier DML) el índice queda fuera de sincronía.
SQL> insert into catalogo values('Diego','Armando','Maradona',null,null);
1 fila creada.SQL> commit;
Confirmacion terminada.
SQL> select * from catalogo
2 where contains(nombre,'maradona',1)>0;
ninguna fila seleccionada.
Para mantener la sincronía, existe el siguiente comando
SQL> begin
2 CTX_DDL.SYNC_INDEX('CAT_TEXTO_IDX','50K');
3 end;
4 /Procedimiento PL/SQL terminado correctamente.
SQL> select * from catalogo
2 where contains(nombre,'maradona',1)>0;
NOMBRE SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Diego Armando Maradona
Ya que esto puede ser inaceptable para muchos modelos de sistemas, oracle tiene una propiedad (que no es default) para poder sincronizar los índices.
SQL> drop index cat_texto_idx;Indice borrado.
SQL> create index cat_texto_idx on catalogo(nombre)
2 indextype is ctxsys.context
3 parameters('datastore mi_datastore sync (on commit)');
Indice creado.
SQL> insert into catalogo values('Eric','Daniel','Cantona',null,null);
1 fila creada.
SQL> select * from catalogo where contains(nombre,'cantona',1)>0;
ninguna fila seleccionada
SQL> commit;
Confirmacion terminada.
SQL> select * from catalogo where contains(nombre,'cantona',1)>0;
NOMBRE SEGUNDO_NO PRIMER_APE SEGUNDO_AP RAZON_SOCI
---------- ---------- ---------- ---------- ----------
Eric Daniel Cantona
El índice quedará sincronizado cada que nosotros hagamos commit;
Todo esto es sólo un ejemplo práctico que me ayudó a resolver un problema de desempeño en un sistema, sin embargo es muy limitado en cuanto al uso de Oracle text, por eso recomiendo leer el Manual de referencia de oracle text.
sábado, 20 de septiembre de 2008
Formatos de Fecha en ORACLE
La función TO_CHAR(,) traduce una fecha/hora (o parte de ella) a una cadena de caracteres, y TO_DATE(,) transforma una cadena de caracteres a una fecha, hora o combinación fecha/hora.
Por ejemplo, TO_DATE('02/08/2002', 'DD/MM/YYYY') daría la fecha 2 de agosto de 2002. Si suponemos que un atributo FECHA almacena esa misma fecha a las 3 de la tarde, TO_CHAR(FECHA, 'DD-MON-YYYY HH:MI AM') devolvería el string 02-AGO-2002 03:00 PM. El formato es una cadena de caracteres en la que se indica el formato siguiendo las claves que se muestran en la Tabla 2.1. Esta tabla no incluye absolutamente todos los formatos, pero sí un buen número de ellos, que deberían ser más que suficientes para un uso normal.
Tabla 2.1: Formatos de fecha en Oracle
Siglos y años
CC
Siglo
SCC
Siglo. Si es AC (Antes de Cristo), lleva un signo
AÑO
YYYY
Año, formato de 4 dígitos
SYYY
Año, formato de 4 dígitos. Si es AC lleva un signo
YY
Año, formato de 2 dígitos
YEAR
Año, escrito en letras y en inglés (por ejemplo, 'TWO THOUSAND TWO')
SYEAR
Ídem, pero si es AC lleva el signo
BC
Antes o Después de Cristo (AC o DC) para usar con los anteriores, por ejemplo YYYY BC
Meses
Q
Trimestre: Ene-Mar=1, Abr-Jun=2, Jul-Sep=3, Oct-Dic=4
MM
Número de mes (1-12)
RM
Número de mes en números romanos (I-XII)
MONTH
Nombre del mes completo rellenado con espacios hasta 10 espacios (SEPTIEMBRE)
FMMONTH
Nombre del mes completo, sin espacios adicionales
MON
Tres primeras letras del mes: ENE, FEB,...
Semanas
WW
Semana del año (1-52)
W
Semana del mes (1-5)
Días
DDD
Día del año (1-366)
DD
Día del mes (1-31)
D
Día de la semana (1-7)
DAY
Nombre del día de la semana rellenado a 9 espacios (MIÉRCOLES)
FMDAY
Nombre del día de la semana, sin espacios
DY
Tres primeras letras del nombre del día de la semana
DDTH
Día (ordinal): 7TH
DDSPTH
Día ordinal en palabra, en inglés: SEVENTH
horas
HH
Hora del día (1-12)
HH12
Hora del día (1-12)
HH24
Hora del día (1-24)
SPHH
Hora del día, en palabra, inglés: SEVEN
AM
am o pm, para usar con HH, como 'HH:MI am'
PM
am o pm
A.M.
a.m. o p.m.
P.M.
a.m. o p.m.
Minutos y segundos
MI
Minutos (0-59)
SS
Segundos (0-59)
SSSS
Segundos después de medianoche (0-86399)
Además de estas palabras clave, el formato puede incluir espacios y los signos de puntuación -/,.;:. Cualquier otro carácter debe ir "entre comillas dobles".
Finalmente, se debe indicar que los formatos de fecha que indican un periodo en palabras, como el nombre del mes o del día de la semana, seguirán el lenguaje de la instalación (de Oracle o del sistema operativo), por lo que no se garantiza que sea en español. Además, el uso de mayúsculas o minúsculas es significativo. Así, para el mes de agosto, MONTH produce AGOSTO , Month, Agosto , y month, agosto , y para el lunes, DY produce LUN y fmday, lunes (o su equivalente en inglés si así está configurado el idioma).
Por ejemplo:
SQL> select ename,
2 to_char(hiredate,'dd "de " fmmonth " de " yyyy') AS "Fecha de contrato"
3* from emp
ENAME Fecha de contrato
---------- ---------------------------
SMITH 17 de diciembre de 1980
ALLEN 20 de febrero de 1981
WARD 22 de febrero de 1981
JONES 02 de abril de 1981
MARTIN 28 de septiembre de 1981
BLAKE 01 de mayo de 1981
CLARK 09 de junio de 1981
SCOTT 09 de diciembre de 1982
KING 17 de noviembre de 1981
TURNER 08 de septiembre de 1981
ADAMS 12 de enero de 1983
JAMES 03 de diciembre de 1981
FORD 03 de diciembre de 1981
MILLER 23 de enero de 1982
14 filas seleccionadas.
Por ejemplo, TO_DATE('02/08/2002', 'DD/MM/YYYY') daría la fecha 2 de agosto de 2002. Si suponemos que un atributo FECHA almacena esa misma fecha a las 3 de la tarde, TO_CHAR(FECHA, 'DD-MON-YYYY HH:MI AM') devolvería el string 02-AGO-2002 03:00 PM. El formato es una cadena de caracteres en la que se indica el formato siguiendo las claves que se muestran en la Tabla 2.1. Esta tabla no incluye absolutamente todos los formatos, pero sí un buen número de ellos, que deberían ser más que suficientes para un uso normal.
Tabla 2.1: Formatos de fecha en Oracle
Siglos y años
CC
Siglo
SCC
Siglo. Si es AC (Antes de Cristo), lleva un signo
AÑO
YYYY
Año, formato de 4 dígitos
SYYY
Año, formato de 4 dígitos. Si es AC lleva un signo
YY
Año, formato de 2 dígitos
YEAR
Año, escrito en letras y en inglés (por ejemplo, 'TWO THOUSAND TWO')
SYEAR
Ídem, pero si es AC lleva el signo
BC
Antes o Después de Cristo (AC o DC) para usar con los anteriores, por ejemplo YYYY BC
Meses
Q
Trimestre: Ene-Mar=1, Abr-Jun=2, Jul-Sep=3, Oct-Dic=4
MM
Número de mes (1-12)
RM
Número de mes en números romanos (I-XII)
MONTH
Nombre del mes completo rellenado con espacios hasta 10 espacios (SEPTIEMBRE)
FMMONTH
Nombre del mes completo, sin espacios adicionales
MON
Tres primeras letras del mes: ENE, FEB,...
Semanas
WW
Semana del año (1-52)
W
Semana del mes (1-5)
Días
DDD
Día del año (1-366)
DD
Día del mes (1-31)
D
Día de la semana (1-7)
DAY
Nombre del día de la semana rellenado a 9 espacios (MIÉRCOLES)
FMDAY
Nombre del día de la semana, sin espacios
DY
Tres primeras letras del nombre del día de la semana
DDTH
Día (ordinal): 7TH
DDSPTH
Día ordinal en palabra, en inglés: SEVENTH
horas
HH
Hora del día (1-12)
HH12
Hora del día (1-12)
HH24
Hora del día (1-24)
SPHH
Hora del día, en palabra, inglés: SEVEN
AM
am o pm, para usar con HH, como 'HH:MI am'
PM
am o pm
A.M.
a.m. o p.m.
P.M.
a.m. o p.m.
Minutos y segundos
MI
Minutos (0-59)
SS
Segundos (0-59)
SSSS
Segundos después de medianoche (0-86399)
Además de estas palabras clave, el formato puede incluir espacios y los signos de puntuación -/,.;:. Cualquier otro carácter debe ir "entre comillas dobles".
Finalmente, se debe indicar que los formatos de fecha que indican un periodo en palabras, como el nombre del mes o del día de la semana, seguirán el lenguaje de la instalación (de Oracle o del sistema operativo), por lo que no se garantiza que sea en español. Además, el uso de mayúsculas o minúsculas es significativo. Así, para el mes de agosto, MONTH produce AGOSTO , Month, Agosto , y month, agosto , y para el lunes, DY produce LUN y fmday, lunes (o su equivalente en inglés si así está configurado el idioma).
Por ejemplo:
SQL> select ename,
2 to_char(hiredate,'dd "de " fmmonth " de " yyyy') AS "Fecha de contrato"
3* from emp
ENAME Fecha de contrato
---------- ---------------------------
SMITH 17 de diciembre de 1980
ALLEN 20 de febrero de 1981
WARD 22 de febrero de 1981
JONES 02 de abril de 1981
MARTIN 28 de septiembre de 1981
BLAKE 01 de mayo de 1981
CLARK 09 de junio de 1981
SCOTT 09 de diciembre de 1982
KING 17 de noviembre de 1981
TURNER 08 de septiembre de 1981
ADAMS 12 de enero de 1983
JAMES 03 de diciembre de 1981
FORD 03 de diciembre de 1981
MILLER 23 de enero de 1982
14 filas seleccionadas.
viernes, 19 de septiembre de 2008
Carga del sistema y excesivos context switches
Muchas veces creemos que todos los problemas de desempeño en Oracle se encuentran dentro de la instancia, es decir, algún query mal afinado, una estructura de memoria mal definida, etc...
En alguna ocasión, tras haber realizado un upgrade de 9.2.0.6 a 10.2.0.2 en un servidor Linux, sucedió algo que no se esperaba y que en las "Pruebas de estrés" (que fueron casi nulas) no se detectó.
El problema era el siguiente, la carga del servidor, al empezar a recibir múltiples conexiones de oracle se elevaba a un 80% de consumo de CPU de systema, el encolamiento en CPU llegaba a 120 puntos de carga y el sistema de forma global se sentía completamente lento.
La configuración era la siguiente
Base de datos: 10.2.0.2 con CPUs aplicados.
Servidor: Red Hat 4, 40gb de memoria 32-bit
SGA: 16gb con INDIRECT_DATA_BUFFERS sobre ramfs
Se pudo comprobar que la carga del equipo se elevaba si se generaban conexiones de forma simultánea con los siguientes scripts.
prueba.sql
select * from dual;
exit;
conexiones.sh
#!/usr/bin/ksh
export ORACLE_HOME=/u01/oracle/product/10.2.0
export ORACLE_SID=ORCL
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
Previo a enviar el script conexiones.sh, la carga del sistema era similar a esta
oracle@server1:~> w
12:36:53 up 13 days, 20:09, 5 users, load average: 0.64, 0.45, 0.39
Una vez ejecutado el script
oracle@server1:~> nohup ./conexiones.sh
oracle@server1:~> nohup: appending output to `nohup.out'
oracle@server1:~> w
12:36:53 up 13 days, 20:09, 5 users, load average: 110.22, 30.57, 4.31
Una vez que teníamos nuestro test case, lo intentamos reproducir en algún otro equipo similar con menor SGA (sin uso de INDIRECT_DATA_BUFFERS) y la carga del servidor no incrementaba drásticamente.
Se hizo una prueba en producción y se disminuyó el SGA para poder usar la memoria sin necesidad de ramfs. La prueba del script funcionó correctamente, y de esta forma utilizamos el Workaround, pero el
db_block_buffers de 8gb se redujo a menos de 1gb
en el db_cache_size, y por lo mismo sólo podía ser aceptado como un workaround.
Se mandaron varios "strace" para ver en qué perdía el tiempo o generaba carga de sistema la conexión de oracle, y el resultante fueron dos funciones a nivel sistema operativo.
mmap y remap_file_pages
La parte de remap_file_pages se solucionó con un parche a nivel RDBMS, pero la mejoría fue muy poca, digamos que el sistema mejoró en un 5% su desempeño.
La parte de mmap, se solucionó con una variable a nivel sistema operativo
DISABLE_MAP_LOCK=1
Esta variable debe de estar puesta en la sesión que levanta la base de datos Oracle y por supuesto en la sesión que levante el listener. La mejora en este caso fue de un 80%.
El workaround quedó atrás y con esta variable, el sistema volvió a la normalidad. Al parecer este problema es un backport de un bug a la versión 10.2.0.1 y se corrigió nuevamente en la versión 10.2.0.3
Muchas veces los problemas de desempeño van relacionados a bugs, o a configuraciones específicas, esta es una de las razones por las cuales Oracle nos invita a llevar siempre nuestros RDBMSs a las versiones más nuevas.
En alguna ocasión, tras haber realizado un upgrade de 9.2.0.6 a 10.2.0.2 en un servidor Linux, sucedió algo que no se esperaba y que en las "Pruebas de estrés" (que fueron casi nulas) no se detectó.
El problema era el siguiente, la carga del servidor, al empezar a recibir múltiples conexiones de oracle se elevaba a un 80% de consumo de CPU de systema, el encolamiento en CPU llegaba a 120 puntos de carga y el sistema de forma global se sentía completamente lento.
La configuración era la siguiente
Base de datos: 10.2.0.2 con CPUs aplicados.
Servidor: Red Hat 4, 40gb de memoria 32-bit
SGA: 16gb con INDIRECT_DATA_BUFFERS sobre ramfs
Se pudo comprobar que la carga del equipo se elevaba si se generaban conexiones de forma simultánea con los siguientes scripts.
prueba.sql
select * from dual;
exit;
conexiones.sh
#!/usr/bin/ksh
export ORACLE_HOME=/u01/oracle/product/10.2.0
export ORACLE_SID=ORCL
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
sqlplus / as sysdba @prueba.sql &
Previo a enviar el script conexiones.sh, la carga del sistema era similar a esta
oracle@server1:~> w
12:36:53 up 13 days, 20:09, 5 users, load average: 0.64, 0.45, 0.39
Una vez ejecutado el script
oracle@server1:~> nohup ./conexiones.sh
oracle@server1:~> nohup: appending output to `nohup.out'
oracle@server1:~> w
12:36:53 up 13 days, 20:09, 5 users, load average: 110.22, 30.57, 4.31
Una vez que teníamos nuestro test case, lo intentamos reproducir en algún otro equipo similar con menor SGA (sin uso de INDIRECT_DATA_BUFFERS) y la carga del servidor no incrementaba drásticamente.
Se hizo una prueba en producción y se disminuyó el SGA para poder usar la memoria sin necesidad de ramfs. La prueba del script funcionó correctamente, y de esta forma utilizamos el Workaround, pero el
db_block_buffers de 8gb se redujo a menos de 1gb
en el db_cache_size, y por lo mismo sólo podía ser aceptado como un workaround.
Se mandaron varios "strace" para ver en qué perdía el tiempo o generaba carga de sistema la conexión de oracle, y el resultante fueron dos funciones a nivel sistema operativo.
mmap y remap_file_pages
La parte de remap_file_pages se solucionó con un parche a nivel RDBMS, pero la mejoría fue muy poca, digamos que el sistema mejoró en un 5% su desempeño.
La parte de mmap, se solucionó con una variable a nivel sistema operativo
DISABLE_MAP_LOCK=1
Esta variable debe de estar puesta en la sesión que levanta la base de datos Oracle y por supuesto en la sesión que levante el listener. La mejora en este caso fue de un 80%.
El workaround quedó atrás y con esta variable, el sistema volvió a la normalidad. Al parecer este problema es un backport de un bug a la versión 10.2.0.1 y se corrigió nuevamente en la versión 10.2.0.3
Muchas veces los problemas de desempeño van relacionados a bugs, o a configuraciones específicas, esta es una de las razones por las cuales Oracle nos invita a llevar siempre nuestros RDBMSs a las versiones más nuevas.
miércoles, 17 de septiembre de 2008
Generar XML y Archivo Excel con SQL*PLUS
Para cierto proyecto ( el cual no puedo nombrar aun ) desarrollado con Oracle, me pidieron el contenido de unas tablas en formato Excel. A si que me puse a averiguar en los manuales de referencia de Oracle especificamente de SQL*PLUS y mediante el comando spool genera el archivo, por ejemplo:
set markup html on spool on;spool Planilla.xls;
SELECT…… FROM …. ;
spool off;set markup html off spool off;
Lo que hace basicamente genera un archivo HTML estructurado como tablas, segun la estructura de la consulta ( query ) y le da la extension .xls.
Soporta todos los comandos de formateo de SQL*PLUS y se reflejan en el excel.
Ahora para generar el xml solo tenemos que usar el package de oracle para la generacion de xml, por ejemplo:
set long 1000000000 longc 1000000000 head off pages 0 trims on lin
set linesize 30032767 feedback off
spool fichero.xml
SELECT dbms_xmlgen.getxml(’CONSULTA_SQL’) from dual;
spool off
Genera el xml con la funcion del package dbms_xmlgen.getxml que tiene por parametro la consulta select y genera automaticamente en un archivo con la estructura de un archivo xml de la consulta hecha a la base de datos. Cuando se genera el xml hay que abrirlo y borrar el codigo SQL*PLUS, ya que tambien escribe el codigo escrito para generar el xml.
Esta opcion no acepta formateo SQL*PLUS.
Definir bien el largo de linea para conservar la estructura xml, por ejemplo que el largo de linea me aguante bien el largo de la consulta del campo.
Este tip es ahorro de problemas y de tiempo, ahora, para que me puede servir esto?, bueno para muchas cosas, pero en mi caso espcifico me sirvio para migrar los datos de Oracle a MySQL con un xml.
set markup html on spool on;spool Planilla.xls;
SELECT…… FROM …. ;
spool off;set markup html off spool off;
Lo que hace basicamente genera un archivo HTML estructurado como tablas, segun la estructura de la consulta ( query ) y le da la extension .xls.
Soporta todos los comandos de formateo de SQL*PLUS y se reflejan en el excel.
Ahora para generar el xml solo tenemos que usar el package de oracle para la generacion de xml, por ejemplo:
set long 1000000000 longc 1000000000 head off pages 0 trims on lin
set linesize 30032767 feedback off
spool fichero.xml
SELECT dbms_xmlgen.getxml(’CONSULTA_SQL’) from dual;
spool off
Genera el xml con la funcion del package dbms_xmlgen.getxml que tiene por parametro la consulta select y genera automaticamente en un archivo con la estructura de un archivo xml de la consulta hecha a la base de datos. Cuando se genera el xml hay que abrirlo y borrar el codigo SQL*PLUS, ya que tambien escribe el codigo escrito para generar el xml.
Esta opcion no acepta formateo SQL*PLUS.
Definir bien el largo de linea para conservar la estructura xml, por ejemplo que el largo de linea me aguante bien el largo de la consulta del campo.
Este tip es ahorro de problemas y de tiempo, ahora, para que me puede servir esto?, bueno para muchas cosas, pero en mi caso espcifico me sirvio para migrar los datos de Oracle a MySQL con un xml.
Roles en Oracle
Generalmente cuando uno toma el curso de Workshop I de 10g, y llega a la parte de Roles (lección 6), se habla muy poco de cómo se pueden manejar los roles, e incluso no hay un ejemplo claro del mismo.
Se nos dice que un rol, es asignado por default a un usuario. Un rol, puede ser asegurado de manera adicional, que por default, no trae seguridad alguna.
Entonces, partiendo de la teoría, un rol puede ser creado con la siguiente seguridad:
* Ninguna (Default)
* Password
* External
* Global
Empecemos por crear un usuario, solo para no perder la costumbre, crearé un usuario identificado a nivel sistema operativo.
C:\>sqlplus "/ AS SYSDBA"
Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> CREATE USER "OPS$HUGO-WIN\HUGO" IDENTIFIED externally;
Usuario creado.
SQL> GRANT CONNECT TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
Ahora nos conectamos como el usuario creado, identificado de manera externa, y validamos el primer rol asignado "CONNECT"
SQL> conn /Conectado.
SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
Aquí validamos dos cosas, la primera es que el rol de create session viene implícito en el connect, ya que de otra forma no hubieramos podido crear la sesión; y lo segundo es que el rol asignado, está activo por default.
Ahora crearemos un rol identificado por un password
SQL> conn / as sysdba
Conectado.
SQL> CREATE role dba_pass IDENTIFIED BY supersecreto;
Rol creado.
SQL> GRANT DBA TO dba_pass;
Concesión terminada correctamente.
SQL> GRANT dba_pass TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
Ya que se tiene asignado al usuario el nuevo rol, nos conectamos como el usuario
SQL> conn /Conectado.
SQL> show userUSER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
DBA_PASS
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
17 filas seleccionadas.
Como vemos, el rol está asignado por default, y no nos pide el password nunca, lo que debemos hacer si es que queremos que el usuario use el password, es quitarlo del default del usuario.
SQL> conn / as sysdbaConectado.
SQL> ALTER USER "OPS$HUGO-WIN\HUGO"
2 DEFAULT ROLE ALL EXCEPT dba_pass;
Usuario modificado.
SQL> conn /Conectado.
SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
Ahora intentamos usar el rol, validando que se requiere un password.
SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> SET role dba_pass;
SET role dba_pass*
ERROR en línea 1:
ORA-01979:falta la contraseña para el rol 'DBA_PASS' o no es válida
SQL> SET role dba_pass IDENTIFIED BY supersecreto;
Rol definido.
SQL> SELECT COUNT(1)
2 FROM v$session;
COUNT(1)
----------
15
Ahora crearemos un rol identificado a través de un procedimiento.Lo primero es crear el procedimiento.
SQL> conn / as sysdba
Conectado.
SQL> CREATE OR REPLACE PROCEDURE sec_roles
2 authid CURRENT_USER AS
3 usuario VARCHAR2(50);
4 BEGIN
5 usuario := LOWER((sys_context('userenv', 'session_user')));
6 DBMS_OUTPUT.PUT_LINE(usuario);
7 IF UPPER(usuario) = 'OPS$HUGO-WIN\HUGO' THEN
8 dbms_session.set_role('DBA_PROC');
9 ELSE
10 NULL;
11 END IF;
12 END;
13 /
Procedimiento creado.
Es importante el punto de AUTHID, debe estar en current user si no, no funcionaría. Ya que se tiene el procedimiento creado, continuamos con el resto del rol y grants necesarios al usuario.
SQL> GRANT EXECUTE ON sec_roles TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
SQL> CREATE role dba_proc IDENTIFIED USING sys.sec_roles;
Rol creado.
SQL> GRANT DBA TO dba_proc;
Concesión terminada correctamente.
SQL> GRANT EXECUTE ON sys.dbms_session TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
Es necesario que el usario que queremos que use el rol, tenga un grant al procedimiento de sys.sec_roles, y tambien permisos de ejecución en sys.dbms_session.
SQL> conn /
Conectado.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
SQL> exec sys.sec_roles;
ops$hugo-win\hugo
Procedimiento PL/SQL terminado correctamente.
SQL> select * from session_roles;
ROLE
------------------------------
DBA_PROC
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
16 filas seleccionadas.
SQL>Hay que notar que al usuario "ops$hugo-win\hugo" jamás se le dio un grant sobre el ROL, el ROL lo obtiene con la ejecución del procedimiento.¿Alguien tendrá un ejemplo de la autenticación global para compartir?
Se nos dice que un rol, es asignado por default a un usuario. Un rol, puede ser asegurado de manera adicional, que por default, no trae seguridad alguna.
Entonces, partiendo de la teoría, un rol puede ser creado con la siguiente seguridad:
* Ninguna (Default)
* Password
* External
* Global
Empecemos por crear un usuario, solo para no perder la costumbre, crearé un usuario identificado a nivel sistema operativo.
C:\>sqlplus "/ AS SYSDBA"
Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> CREATE USER "OPS$HUGO-WIN\HUGO" IDENTIFIED externally;
Usuario creado.
SQL> GRANT CONNECT TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
Ahora nos conectamos como el usuario creado, identificado de manera externa, y validamos el primer rol asignado "CONNECT"
SQL> conn /Conectado.
SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
Aquí validamos dos cosas, la primera es que el rol de create session viene implícito en el connect, ya que de otra forma no hubieramos podido crear la sesión; y lo segundo es que el rol asignado, está activo por default.
Ahora crearemos un rol identificado por un password
SQL> conn / as sysdba
Conectado.
SQL> CREATE role dba_pass IDENTIFIED BY supersecreto;
Rol creado.
SQL> GRANT DBA TO dba_pass;
Concesión terminada correctamente.
SQL> GRANT dba_pass TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
Ya que se tiene asignado al usuario el nuevo rol, nos conectamos como el usuario
SQL> conn /Conectado.
SQL> show userUSER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
DBA_PASS
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
17 filas seleccionadas.
Como vemos, el rol está asignado por default, y no nos pide el password nunca, lo que debemos hacer si es que queremos que el usuario use el password, es quitarlo del default del usuario.
SQL> conn / as sysdbaConectado.
SQL> ALTER USER "OPS$HUGO-WIN\HUGO"
2 DEFAULT ROLE ALL EXCEPT dba_pass;
Usuario modificado.
SQL> conn /Conectado.
SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
Ahora intentamos usar el rol, validando que se requiere un password.
SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> SET role dba_pass;
SET role dba_pass*
ERROR en línea 1:
ORA-01979:falta la contraseña para el rol 'DBA_PASS' o no es válida
SQL> SET role dba_pass IDENTIFIED BY supersecreto;
Rol definido.
SQL> SELECT COUNT(1)
2 FROM v$session;
COUNT(1)
----------
15
Ahora crearemos un rol identificado a través de un procedimiento.Lo primero es crear el procedimiento.
SQL> conn / as sysdba
Conectado.
SQL> CREATE OR REPLACE PROCEDURE sec_roles
2 authid CURRENT_USER AS
3 usuario VARCHAR2(50);
4 BEGIN
5 usuario := LOWER((sys_context('userenv', 'session_user')));
6 DBMS_OUTPUT.PUT_LINE(usuario);
7 IF UPPER(usuario) = 'OPS$HUGO-WIN\HUGO' THEN
8 dbms_session.set_role('DBA_PROC');
9 ELSE
10 NULL;
11 END IF;
12 END;
13 /
Procedimiento creado.
Es importante el punto de AUTHID, debe estar en current user si no, no funcionaría. Ya que se tiene el procedimiento creado, continuamos con el resto del rol y grants necesarios al usuario.
SQL> GRANT EXECUTE ON sec_roles TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
SQL> CREATE role dba_proc IDENTIFIED USING sys.sec_roles;
Rol creado.
SQL> GRANT DBA TO dba_proc;
Concesión terminada correctamente.
SQL> GRANT EXECUTE ON sys.dbms_session TO "OPS$HUGO-WIN\HUGO";
Concesión terminada correctamente.
Es necesario que el usario que queremos que use el rol, tenga un grant al procedimiento de sys.sec_roles, y tambien permisos de ejecución en sys.dbms_session.
SQL> conn /
Conectado.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
SQL> exec sys.sec_roles;
ops$hugo-win\hugo
Procedimiento PL/SQL terminado correctamente.
SQL> select * from session_roles;
ROLE
------------------------------
DBA_PROC
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA
16 filas seleccionadas.
SQL>Hay que notar que al usuario "ops$hugo-win\hugo" jamás se le dio un grant sobre el ROL, el ROL lo obtiene con la ejecución del procedimiento.¿Alguien tendrá un ejemplo de la autenticación global para compartir?
Hints Globales (Global Hints)
Para los que nunca han usado un hint, no son más que instrucciones adicionales al optimizador de costos, que permiten cambiar un plan de ejecución. En pocas palabras, los hints nos permiten decidir en lugar del optimizador.
Existen diferentes tipos de Hints, oracle los suele categorizar de la siguiente forma:
* Single Table (Index, full)
* Multi Table (use_nl, use_hash, leading)
* Query Block (unnest, nomerge)
* Statement (all_rows, first_rows(n))
Pero en este caso vamos enfocar a una subcategoria, los "Global Hints".Ejemplo:
SELECT MIN(CLP.CAMPO_FECHA)
FROMXX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
SELECT MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
Estas sentencias SQL tenían un pésimo plan de ejecución, ya que la vista "XX_VISTA_1", contenía un inline view, y oracle decidía combinar el inline view con el outer query en una simple sentencia SQL (esto no siempre es una buena idea).
Existían en realidad 6 sentencias SQL similares con el mismo problema.Tras revisar el código de la vista, me di cuenta de que eso era lo que Oracle estaba realizando, y la solución era sencilla... Cambiar el código de la vista para que no hiciera el merge del inline view:
CREATE OR REPLACE FORCE VIEW "APPS"."XX_VISTA_1"
("ROW_ID", CAMPO_FECHA, ...) AS
SELECT /*+ NO_MERGE(msi) */
pdc.ROWID row_id,
msi.FECHA campo_fecha, ...
La realidad es que esta es una de las peores soluciones y realmente no fue lo que recomendé, pero fue lo que el cliente decidió hacer como primer intento.
Internamente para ellos era más fácil modificar 1 vista que modificar 6 programas.
Lo que sucedió fue que a partir de la modificación de la vista, hubo sentencias SQL como la siguiente
SELECT
DECODE (:a1,NULL, 'Normal',(
SELECT SECUENCIA_DSP
FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER = (SELECT MAX (CAMPO_NUMBER)
FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER_2 = :a2
AND CAMPO_TEXTO = 'Active'
AND CAMPO_TEXTO_2 IN ('P', 'O', 'I', 'F'))) )
FROM DUAL;
Que al cambiar el código en la vista empezó a tener un desempeño muy pobre, cambiando su plan de ejecución a varios Full Table Scans.
Entonces, si el código de la vista no debe de ser cambiado, ¿qué es lo que se puede usar?
Global Hints
Los hints que se suelen usar en select, updates y deletes, generalmente van relacionados a una tabla que existe dentro del mismo query block. Y no a tablas relacionadas con vistas internas.
La solución son los Global Hints, es decir, se puede usar un Global Hint para decirle al optimizador de costos cómo esolver una vista que existe en nuestro query block.
En mi ejemplo, podemos decirle a la vista que no haga el merge del inline view desde el query principal, y de esta forma evitar la modificación de la vista.
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
De esta forma se estabilizaron los planes de ejecución de 6 sentencias SQL y no se afectó el desempeño de las demás sentencias que hacían uso de la vista.En el manuel de Oracle de performance tuning, vienen dos ejemplos muy claros sobre el uso de global hints
Ejemplo 1
CREATE OR REPLACE VIEW v AS
SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, ( SELECT * FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT max(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */ *
FROM v;
SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ *
FROM v;
SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ *
FROM v;
Ejemplo 2
CREATE OR REPLACE VIEW v1 AS
SELECT *
FROM employees
WHERE employee_id <>
CREATE OR REPLACE VIEW v2 AS
SELECT v1.employee_id employee_id, departments.department_id department_id
FROM v1, departments
WHERE v1.department_id = departments.department_id;
SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
FULL(v2.departments) */ *
FROM v2
WHERE department_id = 30;
Existen diferentes tipos de Hints, oracle los suele categorizar de la siguiente forma:
* Single Table (Index, full)
* Multi Table (use_nl, use_hash, leading)
* Query Block (unnest, nomerge)
* Statement (all_rows, first_rows(n))
Pero en este caso vamos enfocar a una subcategoria, los "Global Hints".Ejemplo:
SELECT MIN(CLP.CAMPO_FECHA)
FROMXX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
SELECT MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
Estas sentencias SQL tenían un pésimo plan de ejecución, ya que la vista "XX_VISTA_1", contenía un inline view, y oracle decidía combinar el inline view con el outer query en una simple sentencia SQL (esto no siempre es una buena idea).
Existían en realidad 6 sentencias SQL similares con el mismo problema.Tras revisar el código de la vista, me di cuenta de que eso era lo que Oracle estaba realizando, y la solución era sencilla... Cambiar el código de la vista para que no hiciera el merge del inline view:
CREATE OR REPLACE FORCE VIEW "APPS"."XX_VISTA_1"
("ROW_ID", CAMPO_FECHA, ...) AS
SELECT /*+ NO_MERGE(msi) */
pdc.ROWID row_id,
msi.FECHA campo_fecha, ...
La realidad es que esta es una de las peores soluciones y realmente no fue lo que recomendé, pero fue lo que el cliente decidió hacer como primer intento.
Internamente para ellos era más fácil modificar 1 vista que modificar 6 programas.
Lo que sucedió fue que a partir de la modificación de la vista, hubo sentencias SQL como la siguiente
SELECT
DECODE (:a1,NULL, 'Normal',(
SELECT SECUENCIA_DSP
FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER = (SELECT MAX (CAMPO_NUMBER)
FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER_2 = :a2
AND CAMPO_TEXTO = 'Active'
AND CAMPO_TEXTO_2 IN ('P', 'O', 'I', 'F'))) )
FROM DUAL;
Que al cambiar el código en la vista empezó a tener un desempeño muy pobre, cambiando su plan de ejecución a varios Full Table Scans.
Entonces, si el código de la vista no debe de ser cambiado, ¿qué es lo que se puede usar?
Global Hints
Los hints que se suelen usar en select, updates y deletes, generalmente van relacionados a una tabla que existe dentro del mismo query block. Y no a tablas relacionadas con vistas internas.
La solución son los Global Hints, es decir, se puede usar un Global Hint para decirle al optimizador de costos cómo esolver una vista que existe en nuestro query block.
En mi ejemplo, podemos decirle a la vista que no haga el merge del inline view desde el query principal, y de esta forma evitar la modificación de la vista.
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);
De esta forma se estabilizaron los planes de ejecución de 6 sentencias SQL y no se afectó el desempeño de las demás sentencias que hacían uso de la vista.En el manuel de Oracle de performance tuning, vienen dos ejemplos muy claros sobre el uso de global hints
Ejemplo 1
CREATE OR REPLACE VIEW v AS
SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, ( SELECT * FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT max(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */ *
FROM v;
SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ *
FROM v;
SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ *
FROM v;
Ejemplo 2
CREATE OR REPLACE VIEW v1 AS
SELECT *
FROM employees
WHERE employee_id <>
CREATE OR REPLACE VIEW v2 AS
SELECT v1.employee_id employee_id, departments.department_id department_id
FROM v1, departments
WHERE v1.department_id = departments.department_id;
SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
FULL(v2.departments) */ *
FROM v2
WHERE department_id = 30;
Arranque y parada de una base de datos Oracle
Objetivos
Explicar brevemente en qué consiste el arranque y parada de una base de datos ORACLE
Arrancar base de datos
El arranque de una base de datos ORACLE requiere tres etapas
Evita nuevas transacciones y nuevas conexiones. Los usuarios que intentan conectarse o los que ya están conectados al intentar realizar una nueva transacción reciben el mensaje "Shutdown in progress".
El proceso PMON finaliza las sesiones no activas y realiza ROLLBACK de aquellas transacciones que no estén validadas.
Cierra y desmonta la B.D. Cierra la SGA para los procesos background.
No necesita recuperacion al arrancar la base de datos
No se realiza ROLLBACK de las transacciones pendientes.
El proceso PMON finaliza las sesiones no activas y realiza ROLLBACK de aquellas transacciones que no estén validadas.
SI necesita recuperacion al arrancar la base de datos
Explicar brevemente en qué consiste el arranque y parada de una base de datos ORACLE
Arrancar base de datos
El arranque de una base de datos ORACLE requiere tres etapas
- Arrancar la instancia
- Montar la base de datos
- Abrir la base de datos
- Arrancar la base de datos
En esta parte del arranque se generan los procesos background.
Se crea la SGA. Sus dimensiones se basan en el fichero de inicialización "init.ora".
SQLPLUS> connect sys as sysdba
connected
SQLPLUS> startup nomount
Oracle Instance started
2. Montar la base de datos
En esta parte del proceso de arranque se produce la conexión al/los archivo/s de control.
En este estado se puede:
- Cambiar el modo de archivado de la B.D.
- Renombrado de archivos de Redo Log o del asociado al tablespace SYSTEM
- Crear, modificar o suprimir nuevos Redo Log o grupos de Redo Log
Partiendo del anterior estado ( nomount ), montamos la base de datos de la siguiente forma:
SQLPLUS> alter database mount
database mounted
En caso de que queramos iniciar la base de datos en este estado bastaría con hacer lo siguiente:
SQLPLUS> connect sys as sysdba
connected
SQLPLUS> startup mount
Oracle Instance started
Database mounted
3. Abrir base de datos
En esta parte de proceso abren todos los ficheros asociados a los tablespaces y los ficheros de Redo Log.
La B.D. está accesible para todos los usuarios
Si es necesaria una recuperación (por un fallo de luz o CPU), se produce en este momento.
Partiendo del anterio estando ( mount ), abrimos la base de datos de la siguiente forma:
SQLPLUS> alter database open
database opened
En caso de que queramos iniciar la base de datos en este estado bastaría con hacer lo siguiente:
SQLPLUS> connect sys as sysdba
connected
SQLPLUS> startup
Oracle Instance started
Database opened
Mas alternativas para el arranque de base de datos
Arranque solo para usuarios con el privilegio RESTRICTED SESSION
SQLPLUS> startup restrict
SQLPLUS> startup force
Arranque con un fichero de parámetros distinto al habitual o localizado en una situación diferente a donde se encuentra por defecto
SQLPLUS> startup pfile=/oracle/database/init2.ora
Parada base de datos
La parada de una B.D. Oracle se realiza mediante el comando SHUTDOWN desde SQL*DBA después de haber establecido una conexión como SYS AS SYSDBA
Existen tres tipos de shutdown
- shutdown normal
- shutdown immediate
- shutdown abort
- Shutdown normal
Espera a que los usuarios conectados actualmente finalicen TODAS las operaciones.
Evita nuevas conexiones. Los usuarios que intentan conectarse reciben el mensaje "Shutdown in progress".
Cierra y desmonta la B.D. Cierra la SGA para los procesos background.
No necesita recuperacion al arrancar la base de datos
SQLPLUS> connect sys as sysdba
connected
SQLPLUS> shutdown normal
Evita nuevas transacciones y nuevas conexiones. Los usuarios que intentan conectarse o los que ya están conectados al intentar realizar una nueva transacción reciben el mensaje "Shutdown in progress".
El proceso PMON finaliza las sesiones no activas y realiza ROLLBACK de aquellas transacciones que no estén validadas.
Cierra y desmonta la B.D. Cierra la SGA para los procesos background.
No necesita recuperacion al arrancar la base de datos
SQLPLUS> connect sys as sysdba
connected
SQLPLUS> shutdown immediate
3. Shutdown abort
No se realiza ROLLBACK de las transacciones pendientes.
El proceso PMON finaliza las sesiones no activas y realiza ROLLBACK de aquellas transacciones que no estén validadas.
SI necesita recuperacion al arrancar la base de datos
SQLPLUS> connect sys as sysdba
connected
SQLPLUS> shutdown abort
martes, 16 de septiembre de 2008
Cómo activar la auditoría de una base de datos
Activar la auditoria de intento de conexiones fallidas para todos los usuarios.
Miramos que actualmente no está activada la auditoria en la base de datos
SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail NONE
Activamos la auditoría de la base de datos
SQL> alter system set audit_trail = DB scope = spfile;
Reiniciamos la base de datos ( shutdown immediate, startup ) y comprobamos que la auditoría se ha activado.
SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail DB
Activamos la auditoría para ver la conexión y desconexión de los usuarios a la base de datos, se hace con la siguiente sentencia
SQL> audit connect;
Visualizar las tablas de auditoría para comprobar que se insertan datos cuando intentamos conectarnos sin lograrlo.
En el apartado anterior hemos activado la auditoría para ver como se conectan los usuarios a la base de datos, vamos a realizar varias pruebas y mostrar dónde se puede comprobar que los usuarios se han conectado a la base de datos.
Nos conectamos con varios usuarios a la base de datos ( en nuestro caso con system y el usuario user9 que está creado )
SQL> connect user9/user9;
SQL> connect system/system;
Tras habernos conectado a la base de datos miramos la tabla dba_audit_trail para ver que datos contiene.
SQL> select username , action_name , priv_used , returncode from dba_audit_trail ;
"SYSTEM" "LOGON" 1017
"SYSTEM" "LOGON" 1017
"USER9" "LOGON" 1017
"USER9" "LOGON" "CREATE SESSION" 0
"USER9" "LOGON" 1017
"USER9" "LOGON" 1017
Observarmos que en esta tabla se registran los intentos de conexión de los usuarios, por lo tanto podemos saber quien se ha conectado a la base de datos
Activar la auditoria sobre la modificación de tablas del usuario Scott.
Ahora vamos a activar la auditoría sobre la modificación de las tablas sobre el usuario Scott, de esta forma cualquier modificación realizada en una tabla que pertenezca a este usuario será registrada en las tablas y podremos ver quien ha realizado esa modifiación.
SQL>audit insert,update on scott . bonus by access;
SQL>audit insert,update on scott . emp by access;
SQL>audit insert,update on scott .dept by access;
SQL>audit insert,update on scott . salgrade by access;
En este caso estamos auditando cada una de las tablas que pertenencen al usuario scott ( bonus, emp, dept, salgrade ) en caso de que alguien inserte algo en ellas o realice alguna actualización. ( si queremos auditar el borrado o la lectura de alguna fila, solo hay que añadir los permisos de select y delete detrás del comando audit).Al ponerlo by access se guardará un registro en la tabla de auditoría por cada intento de insert o update que se realice sobre cada una de las tablas nombradas. ( exite también el registro by session, en el cual se registra por sesión única el intento de insert o update sobre las tablas ).
Miramos la tabla user_obj_audit_opts ( con el usuario scott )
SQL>select * from user_obj_audit_opts;
"BONUS" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
"DEPT" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
"EMP" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
"SALGRADE" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
Y observamos que es lo que estamos auditando del usuario scott, en este caso se vería que eta activada para cada una de las tablas la auditoría para update e insert.
(A/A) --> activado / por acceso
La prueba que se puede realizar es conectarse con otro usuario que tenga permisos de insert y update sobre estas tablas y realizar una serie de inserciones y actualizaciones en esas tablas. En este caso suponemos que un usuario, user9 que tiene permisos de inserción y actualización sobre las tablas del usuario scott ha realizado una serie de inserciones y actualizaciones sobre estas tablas. La forma de ver si las ha realizado o no ( teniendo activada la auditoría es la siguiente ).
SQL>select * from sys . dba_audit_trail where ( action_name = 'INSERT' ) or ( action_name = 'UPDATE' ) ;
El resultado es el siguiente:
"ERIN-0S2WXM4BDG\Erin" "USER9" "ERIN-0S2WXM4BDG" 19/04/2006 15:38:56 "SCOTT" "BONUS" 2 "INSERT" 267 2 47 0
"ERIN-0S2WXM4BDG\Erin" "USER9" "ERIN-0S2WXM4BDG" 19/04/2006 15:39:09 "SCOTT" "BONUS" 2 "INSERT" 267 3 50 0
"ERIN-0S2WXM4BDG\Erin" "USER9" "ERIN-0S2WXM4BDG" 19/04/2006 15:39:19 "SCOTT" "BONUS" 6 "UPDATE" 267 4 55 0
Observamos que se han registrado los intentos de inserción y de modificación sobre la tabla BONUS.
Miramos que actualmente no está activada la auditoria en la base de datos
SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail NONE
Activamos la auditoría de la base de datos
SQL> alter system set audit_trail = DB scope = spfile;
Reiniciamos la base de datos ( shutdown immediate, startup ) y comprobamos que la auditoría se ha activado.
SQL> select name , value from v$parameter where name like 'audit_trail';
audit_trail DB
Activamos la auditoría para ver la conexión y desconexión de los usuarios a la base de datos, se hace con la siguiente sentencia
SQL> audit connect;
Visualizar las tablas de auditoría para comprobar que se insertan datos cuando intentamos conectarnos sin lograrlo.
En el apartado anterior hemos activado la auditoría para ver como se conectan los usuarios a la base de datos, vamos a realizar varias pruebas y mostrar dónde se puede comprobar que los usuarios se han conectado a la base de datos.
Nos conectamos con varios usuarios a la base de datos ( en nuestro caso con system y el usuario user9 que está creado )
SQL> connect user9/user9;
SQL> connect system/system;
Tras habernos conectado a la base de datos miramos la tabla dba_audit_trail para ver que datos contiene.
SQL> select username , action_name , priv_used , returncode from dba_audit_trail ;
"SYSTEM" "LOGON" 1017
"SYSTEM" "LOGON" 1017
"USER9" "LOGON" 1017
"USER9" "LOGON" "CREATE SESSION" 0
"USER9" "LOGON" 1017
"USER9" "LOGON" 1017
Observarmos que en esta tabla se registran los intentos de conexión de los usuarios, por lo tanto podemos saber quien se ha conectado a la base de datos
Activar la auditoria sobre la modificación de tablas del usuario Scott.
Ahora vamos a activar la auditoría sobre la modificación de las tablas sobre el usuario Scott, de esta forma cualquier modificación realizada en una tabla que pertenezca a este usuario será registrada en las tablas y podremos ver quien ha realizado esa modifiación.
SQL>audit insert,update on scott . bonus by access;
SQL>audit insert,update on scott . emp by access;
SQL>audit insert,update on scott .dept by access;
SQL>audit insert,update on scott . salgrade by access;
En este caso estamos auditando cada una de las tablas que pertenencen al usuario scott ( bonus, emp, dept, salgrade ) en caso de que alguien inserte algo en ellas o realice alguna actualización. ( si queremos auditar el borrado o la lectura de alguna fila, solo hay que añadir los permisos de select y delete detrás del comando audit).Al ponerlo by access se guardará un registro en la tabla de auditoría por cada intento de insert o update que se realice sobre cada una de las tablas nombradas. ( exite también el registro by session, en el cual se registra por sesión única el intento de insert o update sobre las tablas ).
Miramos la tabla user_obj_audit_opts ( con el usuario scott )
SQL>select * from user_obj_audit_opts;
"BONUS" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
"DEPT" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
"EMP" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
"SALGRADE" "TABLE" "-/-" "-/-" "-/-" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "A/A" "-/-" "-/-" "-/-" "-/-" "-/-"
Y observamos que es lo que estamos auditando del usuario scott, en este caso se vería que eta activada para cada una de las tablas la auditoría para update e insert.
(A/A) --> activado / por acceso
La prueba que se puede realizar es conectarse con otro usuario que tenga permisos de insert y update sobre estas tablas y realizar una serie de inserciones y actualizaciones en esas tablas. En este caso suponemos que un usuario, user9 que tiene permisos de inserción y actualización sobre las tablas del usuario scott ha realizado una serie de inserciones y actualizaciones sobre estas tablas. La forma de ver si las ha realizado o no ( teniendo activada la auditoría es la siguiente ).
SQL>select * from sys . dba_audit_trail where ( action_name = 'INSERT' ) or ( action_name = 'UPDATE' ) ;
El resultado es el siguiente:
"ERIN-0S2WXM4BDG\Erin" "USER9" "ERIN-0S2WXM4BDG" 19/04/2006 15:38:56 "SCOTT" "BONUS" 2 "INSERT" 267 2 47 0
"ERIN-0S2WXM4BDG\Erin" "USER9" "ERIN-0S2WXM4BDG" 19/04/2006 15:39:09 "SCOTT" "BONUS" 2 "INSERT" 267 3 50 0
"ERIN-0S2WXM4BDG\Erin" "USER9" "ERIN-0S2WXM4BDG" 19/04/2006 15:39:19 "SCOTT" "BONUS" 6 "UPDATE" 267 4 55 0
Observamos que se han registrado los intentos de inserción y de modificación sobre la tabla BONUS.
CREACIÓN DE USUARIOS
Una de las más básicas tareas de un administrador de base de datos es identificar los usuarios. Cada usuario que conecta en la base de datos debe de tener una cuenta. En las cuentas compartidas son difíciles de aplicar una auditoria.
Para crear un usuario utilizamos la sentencia CREATE USER. Cuando creas una cuenta como mínimo tienes que asignar un único nombre (username) y una contraseña para poder autenticarse.
Para cambiar alguno de los atributos que se le ha añadido al usuario creado se utiliza la sentencia ALTER USER.
AUTENTICACIÓN ORACLE
Cuando uno se conecta con una instancia de una base de datos la cuenta de usuario debe de estar autenticada. ORACLE provee tres métodos de autenticación para nuestra cuenta de usuario.
AUTENTICACIÓN MEDIANTE PASSWORD
Cuando un usuario conecta con una base de datos verifica que este usuario y la contraseña introducida almacenada en la base de datos, sea correcta. Las contraseñas se guardan encriptadas en la base de datos (en el data dictionary).
SQL > CREATE USER david IDENTIFIED BY tititus;
En este caso tititus es la contraseña de david que será guardada encriptada en la base de datos.
AUTENTICACIÓN EXTERNA
Cuando un usuario conecta con la base de datos se verifica que el nombre de usuario es el mismo que el nombre de usuario del sistema operativo para permitir la validación.
No se almacenan las cuentas en la base de datos de ninguna forma. Estas cuentas están siempre referidas con OPS$ .A partir de la versión 10g puedes configurar OS_AUTHENT_PREFIX en el spfile
SQL > CREATE USER ops$david IDENTIFIED BY tititus;
Mediante IDENTIFIED EXTERNALLY decimos a la base de datos que nuestra cuenta es externa y tiene que ser validada con el sistema operativo.
AUTENTICACIÓN GLOBAL
Cuando un usuario se conecta con la base de datos se verifica globalmente cuando la información pasa por una opción avanzada de seguridad ( ADVANCED SECURITY OPTION ) para la autenticación tal como Kerberos, RADIUS ....
Para las cuentas globales no se almacena tampoco nada en la base de datos.
SQL > CREATE USER david IDENTIFIED GLOBALLY AS ‘CN=alumnos,OU=campus .......’
Mediante IDENTIFIED GLOBALLY decimos a la base de datos que nuestra cuenta se autentica globalmente, mediante otra opción de seguridad avanzada.
ASIGNACIONES A LOS USUARIOS
ASIGNACIÓN DE UN USUARIO A UN TABLESPACE ( DEFAULT TABLESPACE )
Mediante esta sentencia asignamos un usuario a un tablespace, este será su tablespace por defecto cuando creamos un usuario.
SQL > CREATE USER david IDENTIFIED BY tititus DEFAULT TABLESPACE users;
Mediante esta sentencia, en caso de tener creado ya el usuario le asignamos un tablespace.
SQL > ALTER USER david DEFAULT TABLESPACE users;
La base de datos toma un tablespace por defecto, en caso de querer cambiar este tablespace utilizamos la siguiente sentencia
SQL > ALTER DATABASE DEFAULT TABLESPACE users;
ASIGNACIÓN DE UN USUARIO A UN TABLESPACE TEMPORAL
Un tablespace temporal se utiliza para almacenar “segmentos” temporales que son creados durante operaciones como ORDER BY,SELECT DISTINCT, MERGE JOIN o CREATE INDEX.A veces a los usuarios se les asocia un tablespace temporal para realizar este tipo de operaciones, cuando estas operaciones finalizan este segmento temporal que se ha creado exclusivamente para la operación desaparece.
SQL > CREATE USER david IDENTIFIED BY tititus DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Mediante TEMPORARY TABLESPACE asignamos como tablespace temporal temp al usuario david.
En caso de que el usuario esté creado si queremos asignarle un tablespace temporal utilizamos ALTER USER
SQL > ALTER USER david TEMPORARY TABLESPACE Temp;
ASIGNACIÓN DE UN PERFIL A UN USUARIO
Al igual que podemos asignar un tablespace a un usuario, también podemos asignarle un perfil (profile). El principal perfil ( profile ) por defecto se denomina default.
Si el usuario no está lo podemos crear de la siguiente forma:
SQL > CREATE USER david IDENTIFIED BY tititus
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE resource_profile;
En caso de que el usuario ya esté creado al igual que en los anteriores casos utilizamos la sentencia ALTER USER.
SQL > ALTER USER david PROFILE resource_profile;
BORRADO DE UN USUARIO
Para borrar un usuario utilizamos la sentencia DROP USER, podemos opcionalmente incluir la opción CASCADE, se utiliza para borrar recursivamente los objetos del usuario que se pretende borrar.
SQL > DROP USER david CASCADE
OTORGANDO PRIVILEGIOS (GRANTING)
A un usuario podemos otorgarle una serie de privilegios. Un privilegio permite a un usuario acceder a ciertos objetos o realizar ciertas acciones:.
- Privilegios sobre Objetos ( Object privileges ) a permisos sobre vistas, tablas, secuencias, procedimientos, paquetes.
- Privilegios del Sistema ( System privileges ) a permisos sobre “niveles de la base de datos” como pueden ser conexión a la base de datos, creación de usuarios, limitar cuentas.
- Privilegios sobre Roles ( Role privileges ) a muchos permisos son otorgados mediante roles agrupando un conjunto de privilegios.
Para otorgar privilegios utilizamos la sentencia GRANT, para quitar un privilegio o permiso a un usuario utilizamos la sentencia REVOKE
EJEMPLOS
Privilegio sobre una tabla:
SQL > GRANT ALL ON tabla_alumnos TO david
Siendo tabla_alumnos una tabla de nuestra base de datos y david un usuario de esta, hemos asignado mediante GRANT ALL,todos los permisos al usuario david sobre esta tabla.
GRANT ALL = permisos SELECT, INSERT, UPDATE, DELETE
Si queremos asignar sólo uno de estos permisos utilizamos la misma sentencia pero con el permiso que queramos otorgar.
SQL > GRANT SELECT ON tabla_alumnos TO david
SQL > GRANT SELECT,INSERT ON tabla_alumnos TO david
Privilegio sobre una vista:
Para el caso de las vistas podemos a un usuario otorgar permisos SELECT, INSERT, UPDATE, DELETE, DEBUG, REFERENCES.
Siendo vista_alumnos una vista de nuestra base de datos y david un usuario de esta:
Otorgamos al usuario david todos los permisos sobre la vista vista_alumnos.
SQL > GRANT ALL ON vista_alumnos TO david
Otorgamos al usuario david algunos permisos sobre la vista_alumnos
SQL > GRANT SELECT ON vista_alumnos TO david
SQL > GRANT SELECT,INSERT ON vista_alumnos TO david
Privilegio sobre una secuencia:
Con las secuencias pasa lo mismo que con los anteriores objetos vistos, para otorgar permisos se utiliza GRANT. Los permisos que podemos otorgar a una secuencia es SELECT o ALTER.
Privilegio sobre un paquete,función o procedimiento
Los permisos que podemos otorgar a las funciones, paquetes o procedimientos almacenados en nuestra base de datos son los siguientes: EXECUTE, DEBUG.
QUITANDO PRIVILEGIOS
Si queremos quitar un privilegio a uno de estos objetos haremos lo mismo que con GRANT pero utilizando la sentencia REVOKE.
SQL > REVOKE ALL ON tabla_usuarios FROM david
Una de las más básicas tareas de un administrador de base de datos es identificar los usuarios. Cada usuario que conecta en la base de datos debe de tener una cuenta. En las cuentas compartidas son difíciles de aplicar una auditoria.
Para crear un usuario utilizamos la sentencia CREATE USER. Cuando creas una cuenta como mínimo tienes que asignar un único nombre (username) y una contraseña para poder autenticarse.
Para cambiar alguno de los atributos que se le ha añadido al usuario creado se utiliza la sentencia ALTER USER.
AUTENTICACIÓN ORACLE
Cuando uno se conecta con una instancia de una base de datos la cuenta de usuario debe de estar autenticada. ORACLE provee tres métodos de autenticación para nuestra cuenta de usuario.
AUTENTICACIÓN MEDIANTE PASSWORD
Cuando un usuario conecta con una base de datos verifica que este usuario y la contraseña introducida almacenada en la base de datos, sea correcta. Las contraseñas se guardan encriptadas en la base de datos (en el data dictionary).
SQL > CREATE USER david IDENTIFIED BY tititus;
En este caso tititus es la contraseña de david que será guardada encriptada en la base de datos.
AUTENTICACIÓN EXTERNA
Cuando un usuario conecta con la base de datos se verifica que el nombre de usuario es el mismo que el nombre de usuario del sistema operativo para permitir la validación.
No se almacenan las cuentas en la base de datos de ninguna forma. Estas cuentas están siempre referidas con OPS$ .A partir de la versión 10g puedes configurar OS_AUTHENT_PREFIX en el spfile
SQL > CREATE USER ops$david IDENTIFIED BY tititus;
Mediante IDENTIFIED EXTERNALLY decimos a la base de datos que nuestra cuenta es externa y tiene que ser validada con el sistema operativo.
AUTENTICACIÓN GLOBAL
Cuando un usuario se conecta con la base de datos se verifica globalmente cuando la información pasa por una opción avanzada de seguridad ( ADVANCED SECURITY OPTION ) para la autenticación tal como Kerberos, RADIUS ....
Para las cuentas globales no se almacena tampoco nada en la base de datos.
SQL > CREATE USER david IDENTIFIED GLOBALLY AS ‘CN=alumnos,OU=campus .......’
Mediante IDENTIFIED GLOBALLY decimos a la base de datos que nuestra cuenta se autentica globalmente, mediante otra opción de seguridad avanzada.
ASIGNACIONES A LOS USUARIOS
ASIGNACIÓN DE UN USUARIO A UN TABLESPACE ( DEFAULT TABLESPACE )
Mediante esta sentencia asignamos un usuario a un tablespace, este será su tablespace por defecto cuando creamos un usuario.
SQL > CREATE USER david IDENTIFIED BY tititus DEFAULT TABLESPACE users;
Mediante esta sentencia, en caso de tener creado ya el usuario le asignamos un tablespace.
SQL > ALTER USER david DEFAULT TABLESPACE users;
La base de datos toma un tablespace por defecto, en caso de querer cambiar este tablespace utilizamos la siguiente sentencia
SQL > ALTER DATABASE DEFAULT TABLESPACE users;
ASIGNACIÓN DE UN USUARIO A UN TABLESPACE TEMPORAL
Un tablespace temporal se utiliza para almacenar “segmentos” temporales que son creados durante operaciones como ORDER BY,SELECT DISTINCT, MERGE JOIN o CREATE INDEX.A veces a los usuarios se les asocia un tablespace temporal para realizar este tipo de operaciones, cuando estas operaciones finalizan este segmento temporal que se ha creado exclusivamente para la operación desaparece.
SQL > CREATE USER david IDENTIFIED BY tititus DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Mediante TEMPORARY TABLESPACE asignamos como tablespace temporal temp al usuario david.
En caso de que el usuario esté creado si queremos asignarle un tablespace temporal utilizamos ALTER USER
SQL > ALTER USER david TEMPORARY TABLESPACE Temp;
ASIGNACIÓN DE UN PERFIL A UN USUARIO
Al igual que podemos asignar un tablespace a un usuario, también podemos asignarle un perfil (profile). El principal perfil ( profile ) por defecto se denomina default.
Si el usuario no está lo podemos crear de la siguiente forma:
SQL > CREATE USER david IDENTIFIED BY tititus
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
PROFILE resource_profile;
En caso de que el usuario ya esté creado al igual que en los anteriores casos utilizamos la sentencia ALTER USER.
SQL > ALTER USER david PROFILE resource_profile;
BORRADO DE UN USUARIO
Para borrar un usuario utilizamos la sentencia DROP USER, podemos opcionalmente incluir la opción CASCADE, se utiliza para borrar recursivamente los objetos del usuario que se pretende borrar.
SQL > DROP USER david CASCADE
OTORGANDO PRIVILEGIOS (GRANTING)
A un usuario podemos otorgarle una serie de privilegios. Un privilegio permite a un usuario acceder a ciertos objetos o realizar ciertas acciones:.
- Privilegios sobre Objetos ( Object privileges ) a permisos sobre vistas, tablas, secuencias, procedimientos, paquetes.
- Privilegios del Sistema ( System privileges ) a permisos sobre “niveles de la base de datos” como pueden ser conexión a la base de datos, creación de usuarios, limitar cuentas.
- Privilegios sobre Roles ( Role privileges ) a muchos permisos son otorgados mediante roles agrupando un conjunto de privilegios.
Para otorgar privilegios utilizamos la sentencia GRANT, para quitar un privilegio o permiso a un usuario utilizamos la sentencia REVOKE
EJEMPLOS
Privilegio sobre una tabla:
SQL > GRANT ALL ON tabla_alumnos TO david
Siendo tabla_alumnos una tabla de nuestra base de datos y david un usuario de esta, hemos asignado mediante GRANT ALL,todos los permisos al usuario david sobre esta tabla.
GRANT ALL = permisos SELECT, INSERT, UPDATE, DELETE
Si queremos asignar sólo uno de estos permisos utilizamos la misma sentencia pero con el permiso que queramos otorgar.
SQL > GRANT SELECT ON tabla_alumnos TO david
SQL > GRANT SELECT,INSERT ON tabla_alumnos TO david
Privilegio sobre una vista:
Para el caso de las vistas podemos a un usuario otorgar permisos SELECT, INSERT, UPDATE, DELETE, DEBUG, REFERENCES.
Siendo vista_alumnos una vista de nuestra base de datos y david un usuario de esta:
Otorgamos al usuario david todos los permisos sobre la vista vista_alumnos.
SQL > GRANT ALL ON vista_alumnos TO david
Otorgamos al usuario david algunos permisos sobre la vista_alumnos
SQL > GRANT SELECT ON vista_alumnos TO david
SQL > GRANT SELECT,INSERT ON vista_alumnos TO david
Privilegio sobre una secuencia:
Con las secuencias pasa lo mismo que con los anteriores objetos vistos, para otorgar permisos se utiliza GRANT. Los permisos que podemos otorgar a una secuencia es SELECT o ALTER.
Privilegio sobre un paquete,función o procedimiento
Los permisos que podemos otorgar a las funciones, paquetes o procedimientos almacenados en nuestra base de datos son los siguientes: EXECUTE, DEBUG.
QUITANDO PRIVILEGIOS
Si queremos quitar un privilegio a uno de estos objetos haremos lo mismo que con GRANT pero utilizando la sentencia REVOKE.
SQL > REVOKE ALL ON tabla_usuarios FROM david
sábado, 13 de septiembre de 2008
Procedimiento para configurar / desconfigurar el modo archivelog de una base de datos Oracle
OBJETIVOS
El objetivo de este documento es exponer el procedimiento para poder realizar el cambio de modo de archivado de una base de datos ORACLE
INTRODUCCIÓN
MODO ARCHIVELOG:
CóMO SABER EN QUé MODO ESTá LA BASE DE DATOS
El comando archive log list nos muestra si la base de datos está en modo archivelog o noarchivelog y si el archivado automático está funcionando :
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle10/product/10.1.3/dbs/arch
Oldest online log sequence 36
Current log sequence 38
SQL>
ACTIVACIÓN DEL MODO ARCHIVELOG
El objetivo de este documento es exponer el procedimiento para poder realizar el cambio de modo de archivado de una base de datos ORACLE
INTRODUCCIÓN
MODO ARCHIVELOG:
El modo archivelog de una base de datos Oracle protege contra la pérdida de datos cuando se produce un fallo en el medio físico.
- Se puede realizar una copia de seguridad mientras la base de datos está on-line.
- Con este modo de base de datos se puede restaurar una copia de seguridad de los archivos dañados utilizando estos archivos para actualizar los archivos mientras están online.
- Se puede recuperar la base de datos en un número de cambio del sistema específico.
- Se puede restaurar la base de datos en un punto específico en el tiempo.
- Teniendo en cuenta estas características básicas del modo archivelog de base de datos ORACLE, vamos a exponer en este documento cómo se activa y cómo se desactiva.
Alguna de las consecuencias que tiene desactivarlo son las siguientes:
- Las copias de seguridad ya no se pueden hacer on-line (habría que aplicar otro tipo de copias de seguridad).
- No se podrá recuperar la base de datos en un tiempo concreto.
Esta decisión tiene que ser aprobada por el DBA (según la política de backup que se adopte).
CóMO SABER EN QUé MODO ESTá LA BASE DE DATOS
El comando archive log list nos muestra si la base de datos está en modo archivelog o noarchivelog y si el archivado automático está funcionando :
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle10/product/10.1.3/dbs/arch
Oldest online log sequence 36
Current log sequence 38
SQL>
ACTIVACIÓN DEL MODO ARCHIVELOG
- Para que el modo archivelog esté activado, el init.ora debe de estar arrancado con los siguientes parámetros.
log_archive_start = true
log_archive_dest_1 = "location=/database/archivelog/bbdd REOPEN=5"
log_archive_format = arch_%t_%s.arc
log_archive_dest_1= es el destino donde vas a archivar los .arc
Si la base de datos está funcionando y esos parámetros están en el init.ora paramos la base de datos con un
> shutdown immediate
Previamente habría que haberse conectado a la base de datos sobre la que se quiere realizar el cambio.
A continuación montamos la base de datos:
> startup mount
Después de haber montado la base de datos ejecutamos el siguiente comando:
> alter database archivelog
Y después abrimos la base de datos:
> alter database open
Para finalizar, activamos el archivado automático:
> alter system archive log start
Con esto ya tendríamos configurado el modo archivelog de una base de datos ORACLE.
DESACTIVACIÓN DEL MODO ARCHIVELOG
Para desactivar el modo archive log de una base de datos (teniendo en cuenta las consecuencias que esto conlleva) seguimos los siguientes pasos:
Nos conectamos a la base de datos en la cual queremos parar el modo de archivado y la paramos mediante el comando:
> shutdown immediate
Montamos la base de datos mediante el comando:
> startup mount
Desactivamos el modo archivelog:
> alter database noarchivelog
Abrimos la base de datos:
> alter database open
Desactivamos el archivado automático:
> alter system archive log stop
Instalación Oracle 10g (10.1.0.3) en Red Hat Advanced 4 Server
SOFTWARE A INSTALAR
La versión que se va a instalar es Oracle 10g Release1 (10.1.0.3).
El software lo dejamos copiado en /software de la máquina donde lo vamos a instalar.
HOST FILE
El fichero /etc/hosts debe de contener lo siguiente :
<> <> <>
PARÁMETROS DEL KERNEL
Añade las siguientes líneas a /etc/sysctl.conf :
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
Lanza el siguiente comando para actualizar los parámetros del kernel :
/sbin/sysctl –p
Añade las siguientes líneas a /etc/security/limits.conf :
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
Añade la siguiente línea a /etc/pam.d/login si no existe :
session required /lib/security/pam_limits.so
* ver bug para pam_limits.so: Bug 115442
CREACIÓN DE USUARIOS
Usuarios que deben existir en el sistema :
- Grupo oinstall à Oracle Inventory group
- Grupo dba à OSDBA group
- Oracle à usuario propietario del software
Por notación vamos a utilizar los usuarios anteriormente especificados acabados en 10g (para remarcar la versión).
groupadd oinstall10g
groupadd dba10g
useradd –g oinstall10g –G dba10g oracle10g
passwd oracle10g
CONFIGURACIÓN DEL .bash_profile
En el .bash_profile del usuario oracle10g creado especificar los parámetros siguientes ( ver ambiente oracle ):
Ejemplo:
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
# Ambiente de Oracle
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.1.0
export ORACLE_SID=unicorn
export ORACLE_TERM=vt100
#export TNS_ADMIN= configurar si sqlnet.ora,
#tnsnames.ora, etc. no estan en
#$ORACLE_HOME/network/admin
export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1;
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
# Configuración del PATH del shell
export LANG=C;
export PATH=$PATH:$ORACLE_HOME/bin
# CLASSPATH:
#CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib
#CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib
#CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
#export CLASSPATH
CREAR Y DAR "PERMISOS" A LOS DIRECTORIOS DE INSTALACION DEL .BASH_PROFILE
$ mkdir /opt/oracle
$ mkdir /opt/oracle/product
$ mkdir /opt/oracle/product/10.1.0
$ chown -R oracle10g.oinstall10g /opt/oracle/
INSTALACIÓN DE JAVA RUN TIME 1.3.1 O SUPERIOR
rpm –i nb_pakete.rpm
wich java (para saber donde se ha instalado)
ln –s /usr/bin/java /bin/java (creacion de enlace simbólico)
APLICAR PARCHES PARA QUE PUEDA SER INSTALADO CON LA VERSIÓN ADVANCE 4 SERVER
Antes de instalar Oracle Database 10g versión 10.1.0.3 en Red Hat 4, hay que seguir los siguientes pasos:
Loguearse en Oracle Metalink (http://metalink.oracle.com/)
Click Patches
Click Simple Search
Seleccionar Patch Number(s) en el campo Search By
Introducir 4153257 en el campo Search By Patch Number(s)
Seleccionar Linux x86 en el campo Platform or Language y hacer click en Go
Hacer click en Download
Descomprimir el fichero p4153257_10103_LINUX.zip en directorio de nuestro sistema. Contiene el fichero oraparam.ini
Ejecutar dicho comando: $ ./runInstaller -paramFile oraparam_path
Al ejecutar este comando puede dar el siguiente error: No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The Java RunTime Environment was not found at à/bin/java. Hence, the Oracle Universal Installer cannot be run.
Please visit http://www.javasoft.com and install JRE version 1.3.1 or higher and try again.
: No such file or directory
La solución que a mí me funcionó para arreglar este problema fue la siguiente:
Copiar en el oraparm.ini original sólo las líneas de validación de la versión Red Hat 4.0 de esta forma las demás rutas de parámetros de inicialización es correcto.
Este error se debe a que alguno de los parámetros del oraparm.ini bajado, sus rutas no apuntan a una ruta existente puesto que se ha podido poner en otro directorio diferente al que te viene por defecto para instalar.
En este caso si colocamos el parche en el mismo sitio donde está el que viene por defecto y ejecutamos. ./runInstaler debería funcionar.
La versión que se va a instalar es Oracle 10g Release1 (10.1.0.3).
El software lo dejamos copiado en /software de la máquina donde lo vamos a instalar.
HOST FILE
El fichero /etc/hosts debe de contener lo siguiente :
<> <> <>
PARÁMETROS DEL KERNEL
Añade las siguientes líneas a /etc/sysctl.conf :
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
Lanza el siguiente comando para actualizar los parámetros del kernel :
/sbin/sysctl –p
Añade las siguientes líneas a /etc/security/limits.conf :
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
Añade la siguiente línea a /etc/pam.d/login si no existe :
session required /lib/security/pam_limits.so
* ver bug para pam_limits.so: Bug 115442
CREACIÓN DE USUARIOS
Usuarios que deben existir en el sistema :
- Grupo oinstall à Oracle Inventory group
- Grupo dba à OSDBA group
- Oracle à usuario propietario del software
Por notación vamos a utilizar los usuarios anteriormente especificados acabados en 10g (para remarcar la versión).
groupadd oinstall10g
groupadd dba10g
useradd –g oinstall10g –G dba10g oracle10g
passwd oracle10g
CONFIGURACIÓN DEL .bash_profile
En el .bash_profile del usuario oracle10g creado especificar los parámetros siguientes ( ver ambiente oracle ):
Ejemplo:
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
# Ambiente de Oracle
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.1.0
export ORACLE_SID=unicorn
export ORACLE_TERM=vt100
#export TNS_ADMIN= configurar si sqlnet.ora,
#tnsnames.ora, etc. no estan en
#$ORACLE_HOME/network/admin
export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1;
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
# Configuración del PATH del shell
export LANG=C;
export PATH=$PATH:$ORACLE_HOME/bin
# CLASSPATH:
#CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib
#CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlib
#CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
#export CLASSPATH
CREAR Y DAR "PERMISOS" A LOS DIRECTORIOS DE INSTALACION DEL .BASH_PROFILE
$ mkdir /opt/oracle
$ mkdir /opt/oracle/product
$ mkdir /opt/oracle/product/10.1.0
$ chown -R oracle10g.oinstall10g /opt/oracle/
INSTALACIÓN DE JAVA RUN TIME 1.3.1 O SUPERIOR
rpm –i nb_pakete.rpm
wich java (para saber donde se ha instalado)
ln –s /usr/bin/java /bin/java (creacion de enlace simbólico)
APLICAR PARCHES PARA QUE PUEDA SER INSTALADO CON LA VERSIÓN ADVANCE 4 SERVER
Antes de instalar Oracle Database 10g versión 10.1.0.3 en Red Hat 4, hay que seguir los siguientes pasos:
Loguearse en Oracle Metalink (http://metalink.oracle.com/)
Click Patches
Click Simple Search
Seleccionar Patch Number(s) en el campo Search By
Introducir 4153257 en el campo Search By Patch Number(s)
Seleccionar Linux x86 en el campo Platform or Language y hacer click en Go
Hacer click en Download
Descomprimir el fichero p4153257_10103_LINUX.zip en directorio de nuestro sistema. Contiene el fichero oraparam.ini
Ejecutar dicho comando: $ ./runInstaller -paramFile oraparam_path
Al ejecutar este comando puede dar el siguiente error: No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The Java RunTime Environment was not found at à/bin/java. Hence, the Oracle Universal Installer cannot be run.
Please visit http://www.javasoft.com and install JRE version 1.3.1 or higher and try again.
: No such file or directory
La solución que a mí me funcionó para arreglar este problema fue la siguiente:
Copiar en el oraparm.ini original sólo las líneas de validación de la versión Red Hat 4.0 de esta forma las demás rutas de parámetros de inicialización es correcto.
Este error se debe a que alguno de los parámetros del oraparm.ini bajado, sus rutas no apuntan a una ruta existente puesto que se ha podido poner en otro directorio diferente al que te viene por defecto para instalar.
En este caso si colocamos el parche en el mismo sitio donde está el que viene por defecto y ejecutamos. ./runInstaler debería funcionar.
lunes, 1 de septiembre de 2008
Consultas combinadas. JOINS
Consultas combinadas.
Habitualmente cuando necesitamos recuperar la información de una base de datos nos encontramos con que dicha información se encuentra repartida en varias tablas, referenciadas a través de varios códigos. De este modo si tuvieramos una tabla de ventas con un campo cliente, dicho campo contendría el código del cliente de la tabla de cliente.
Sin embargo está forma de almacenar la información no resulta muy util a la hora de consultar los datos. SQL nos proporciona una forma facil de mostrar la información repartida en varias tablas, las consultas combinadas o JOINS.
Las consultas combinadas pueden ser de tres tipos:
Combinación interna
Combinación externa
Uniones
--------------------------------------------------------------------------------
Combinación interna.
La combinación interna nos permite mostrar los datos de dos o más tablas a través de una condición WHERE.
Si recordamos los ejemplos de los capitulos anteriores tenemos una tabla de coches, en la que tenemos referenciada la marca a través del código de marca. Para realizar la consulta combinada entre estas dos tablas debemos escribir una consulta SELECT en cuya claúsula FROM escribiremos el nombre de las dos tablas, separados por comas, y una condición WHERE que obligue a que el código de marca de la tabla de coches sea igual al código de la tabla de marcas.
Lo más sencillo es ver un ejemplo directamente:
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
FROM tCoches, tMarcas
WHERE tCoches.marca = tMarcas.codigo
La misma consulta de forma "visual" ...
La misma consulta de forma "visual" ...
Demonos cuenta que hemos antepuesto el nombre de cada tabla a el nombre del campo, esto no es obligatorio si los nombres de campos no se repiten en las tablas, pero es acondajable para evitar conflictos de nombres entre campos. Por ejemplo, si para referirnos al campo marca no anteponemos el nombre del campo la base de datos no sabe si queremos el campo marca de la tabla tCoches, que contiene el código de la marca, o el campo marca de la tabla tMarcas, que contiene el nombre de la marca.
Otra opción es utilizar la cláusula INNER JOIN. Su sintaxis es identica a la de una consulta SELECT habitual, con la particularidad de que én la cláusula FROM sólo aparece una tabla o vista, añadiendose el resto de tablas a través de cláusulas INNER JOIN .
SELECT [ALL DISTINCT ][{,}]FROM [{INNER JOIN ON }][WHERE [{ ANDOR }]][GROUP BY [{,}]][HAVING [{ ANDOR }]][ORDER BY [ASC DESC] [{, [ASC DESC ]}]]
El ejemplo anterior escrito utilizando la clausula INNER JOIN quedaria de la siguiente manera:
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
Otra opción es utilizar la cláusula INNER JOIN. Su sintaxis es identica a la de una consulta SELECT habitual, con la particularidad de que én la cláusula FROM sólo aparece una tabla o vista, añadiendose el resto de tablas a través de cláusulas INNER JOIN .
SELECT [ALL DISTINCT ]
El ejemplo anterior escrito utilizando la clausula INNER JOIN quedaria de la siguiente manera:
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
FROM tCoches
INNER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
La cláusula INNER JOIN permite separar completamente las condiciones de combinación con otros criterios, cuando tenemos consultas que combinan nueve o diez tablas esto realmente se agradece. Sin embargo muchos programadores no son amigos de la cláusula INNER JOIN, la razón es que uno de los principales gestores de bases de datos, ORACLE, no la soportaba. Si nuestro porgrama debia trabajar sobre bases de datos ORACLE no podiamos utilizar INNER JOIN. A partir de la version ORACLE 9i oracle soporta la cláusula INNER JOIN.
--------------------------------------------------------------------------------
Combinación Externa
La combinación interna es excluyente. Esto quiere decir que si un registro no cumple la condición de combinación no se incluye en los resultados. De este modo en el ejemplo anterior si un coche no tiene grabada la marca no se devuelve en mi consulta.
Según la naturaleza de nuestra consulta esto puede ser una ventaja , pero en otros casos significa un serio problema. Para modificar este comportamiento SQL pone a nuestra disposición la combinación externa. La combinación externa no es excluyente.
La sintaxis es muy parecida a la combinación interna,
SELECT [ALL DISTINCT ][{,}]FROM [{LEFTRIGHT OUTER JOIN ON }][WHERE [{ ANDOR }]][GROUP BY [{,}]][HAVING [{ ANDOR }]][ORDER BY [ASC DESC] [{, [ASC DESC ]}]]
La combinación externa puede ser diestra o siniestra, LEFT OUTER JOIN o RIGHT OUTER JOIN. Con LEFT OUTER JOIN obtenemos todos los registros de en la tabla que situemos a la izquierda de la clausula JOIN, mientras que con RIGHT OUTER JOIN obtenmos el efecto contrario.
Como mejor se ve la combinación externa es con un ejemplo.
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
La cláusula INNER JOIN permite separar completamente las condiciones de combinación con otros criterios, cuando tenemos consultas que combinan nueve o diez tablas esto realmente se agradece. Sin embargo muchos programadores no son amigos de la cláusula INNER JOIN, la razón es que uno de los principales gestores de bases de datos, ORACLE, no la soportaba. Si nuestro porgrama debia trabajar sobre bases de datos ORACLE no podiamos utilizar INNER JOIN. A partir de la version ORACLE 9i oracle soporta la cláusula INNER JOIN.
--------------------------------------------------------------------------------
Combinación Externa
La combinación interna es excluyente. Esto quiere decir que si un registro no cumple la condición de combinación no se incluye en los resultados. De este modo en el ejemplo anterior si un coche no tiene grabada la marca no se devuelve en mi consulta.
Según la naturaleza de nuestra consulta esto puede ser una ventaja , pero en otros casos significa un serio problema. Para modificar este comportamiento SQL pone a nuestra disposición la combinación externa. La combinación externa no es excluyente.
La sintaxis es muy parecida a la combinación interna,
SELECT [ALL DISTINCT ]
La combinación externa puede ser diestra o siniestra, LEFT OUTER JOIN o RIGHT OUTER JOIN. Con LEFT OUTER JOIN obtenemos todos los registros de en la tabla que situemos a la izquierda de la clausula JOIN, mientras que con RIGHT OUTER JOIN obtenmos el efecto contrario.
Como mejor se ve la combinación externa es con un ejemplo.
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
FROM tCoches
LEFT OUTER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
Esta consulta devolverá todos los registros de la tabla tCoches, independientemente de que tengan marca o no. En el caso de que el coche no tenga marca se devolverá el valor null para los campos de la tabla tMarcas.
Visualmente (la consulta devuelve los datos en azul) ...
Esta consulta devolverá todos los registros de la tabla tCoches, independientemente de que tengan marca o no. En el caso de que el coche no tenga marca se devolverá el valor null para los campos de la tabla tMarcas.
Visualmente (la consulta devuelve los datos en azul) ...
El mismo ejemplo con RIGHT OUTER JOIN.
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas
FROM tCoches
RIGHT OUTER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
Esta consulta devolverá los registros de la tabla tCoches que tengan marca relacionada y todos los registros de la tabla tMarcas, tengan algún registro en tCoches o no.
Visualmente (la consulta devuelve los datos en azul) ...
--------------------------------------------------------------------------------
Union
La cláusula UNION permite unir dos o más conjuntos de resultados en uno detras del otro como si se tratase de una única tabla. De este modo podemos obtener los registros de mas de una tabla "unidos".
La sintaxis corresponde a la de varias SELECT unidas a través de UNION, como se muestra a continuación:
SELECT [ALL DISTINCT ][{,}]FROM [{LEFTRIGHT OUTER JOIN ON }][WHERE [{ ANDOR }]][GROUP BY [{,}]][HAVING [{ ANDOR }]]{UNION [ALL DISTINCT ]SELECT [ALL DISTINCT ] [{,}]FROM [{LEFTRIGHT OUTER JOIN ON }][WHERE [{ ANDOR }]][GROUP BY [{,}]][HAVING [{ ANDOR }]]}[ORDER BY [ASC DESC] [{, [ASC DESC ]}]]
Para utilizar la clausula UNION debemos cumplir una serie de normas.
Las consultas a unir deben tener el mismo número campos, y además los campos deben ser del mismo tipo.
Sólo puede haber una única clausula ORDER BY al final de la sentencia SELECT.
El siguiente ejemplo muestra el uso de UNION
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas FROM tCochesINNER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
UNION
SELECT tMotos.matricula, tMarcas.marca, tMotos.modelo, tMotos.color, tMotos.numero_kilometros, 0FROM tMotosINNER JOIN tMarcas ON tMotos.marca = tMarcas.codigo;
Puede observarse el uso de la constante cero en la segunda lista de selección para hacer coincidir el número y tipo decampos que devuelve la consulta UNION.
Esta consulta devolverá los registros de la tabla tCoches que tengan marca relacionada y todos los registros de la tabla tMarcas, tengan algún registro en tCoches o no.
Visualmente (la consulta devuelve los datos en azul) ...
--------------------------------------------------------------------------------
Union
La cláusula UNION permite unir dos o más conjuntos de resultados en uno detras del otro como si se tratase de una única tabla. De este modo podemos obtener los registros de mas de una tabla "unidos".
La sintaxis corresponde a la de varias SELECT unidas a través de UNION, como se muestra a continuación:
SELECT [ALL DISTINCT ]
Para utilizar la clausula UNION debemos cumplir una serie de normas.
Las consultas a unir deben tener el mismo número campos, y además los campos deben ser del mismo tipo.
Sólo puede haber una única clausula ORDER BY al final de la sentencia SELECT.
El siguiente ejemplo muestra el uso de UNION
SELECT tCoches.matricula, tMarcas.marca, tCoches.modelo, tCoches.color, tCoches.numero_kilometros, tCoches.num_plazas FROM tCochesINNER JOIN tMarcas ON tCoches.marca = tMarcas.codigo
UNION
SELECT tMotos.matricula, tMarcas.marca, tMotos.modelo, tMotos.color, tMotos.numero_kilometros, 0FROM tMotosINNER JOIN tMarcas ON tMotos.marca = tMarcas.codigo;
Puede observarse el uso de la constante cero en la segunda lista de selección para hacer coincidir el número y tipo decampos que devuelve la consulta UNION.
Sub Consultas en Oracle Avanzadas
Este tipo de consultas permiten comprobar si un dato se encuentra relacionado con datos que proceden de una segunda consulta.
Aquí se comentan algunas mejoras a ese tipo de consultas implementadas por Oracle.
Subconsultas sobre múltiples valores
Los operaciones de subconsulta permiten comparar un valor con el resultado de una subconsulta. También se pueden comparar varios valores:
SELECT * FROM piezas
WHERE (tipo, modelo) IN (SELECT tipo,modelo FROM EXISTENCIAS);
Lógicamente los valores entre paréntesis deben de coincidir, es decir si entre paréntesis se hace referencia a tres campos, el SELECT interior debe devolver tres campos exactamente del mismo tipo que los del paréntesis.
Subconsultas correlacionadas
Las subconsultas correlacionadas hacen un proceso fila a fila, de modo que la subconsulta se ejecuta una vez por cada fila de la consulta principal.
Esto es absolutamente diferente respecto a la ejecución normal de una subconsulta, ya que normalmente la subconsulta se ejecuta primero, y con sus resultados se ejecuta la consulta principal. La sintaxis de este tipo de consultas es:
SELECT listaDeColumnas
FROM tabla alias
WHERE expresion operador (SELECT listaDeExpresiones+
FROM tabla2
WHERE expr1 = alias.expr2)
Ejemplo:
SELECT nombre, salario, cod_departamento
FROM empleados emp
WHERE salario >(SELECT AVG(salario)
FROM empleados
WHERE departamento = emp.departamento)
Este ejemplo muestra los datos de los empleados cuyo sueldo supera la media de su departamento.
Consultas EXISTS
Este operador devuelve verdadero si la consulta que le sigue devuelve algún valor. Si no, devuelve falso. Se utiliza sobre todo en consultas correlacionadas. Ejemplo:
SELECT tipo,modelo, precio_venta
FROM piezas p WHERE EXISTS (SELECT tipo,modelo FROM existencias
WHERE tipo=p.tipo AND modelo=p.modelo);
Esta consulta devuelve las piezas que se encuentran en la tabla de existencias (es igual al ejemplo comentado en el apartado subconsultas sobre múltiples valores). La consulta contraria es :
SELECT tipo,modelo, precio_venta
FROM piezas p WHERE NOT EXISTS (SELECT tipo,modelo FROM existencias
WHERE tipo=p.tipo AND modelo=p.modelo);
Normalmente las consultas EXISTS se pueden realizar de alguna otra forma con los operadores ya comentados.
Aquí se comentan algunas mejoras a ese tipo de consultas implementadas por Oracle.
Subconsultas sobre múltiples valores
Los operaciones de subconsulta permiten comparar un valor con el resultado de una subconsulta. También se pueden comparar varios valores:
SELECT * FROM piezas
WHERE (tipo, modelo) IN (SELECT tipo,modelo FROM EXISTENCIAS);
Lógicamente los valores entre paréntesis deben de coincidir, es decir si entre paréntesis se hace referencia a tres campos, el SELECT interior debe devolver tres campos exactamente del mismo tipo que los del paréntesis.
Subconsultas correlacionadas
Las subconsultas correlacionadas hacen un proceso fila a fila, de modo que la subconsulta se ejecuta una vez por cada fila de la consulta principal.
Esto es absolutamente diferente respecto a la ejecución normal de una subconsulta, ya que normalmente la subconsulta se ejecuta primero, y con sus resultados se ejecuta la consulta principal. La sintaxis de este tipo de consultas es:
SELECT listaDeColumnas
FROM tabla alias
WHERE expresion operador (SELECT listaDeExpresiones+
FROM tabla2
WHERE expr1 = alias.expr2)
Ejemplo:
SELECT nombre, salario, cod_departamento
FROM empleados emp
WHERE salario >(SELECT AVG(salario)
FROM empleados
WHERE departamento = emp.departamento)
Este ejemplo muestra los datos de los empleados cuyo sueldo supera la media de su departamento.
Consultas EXISTS
Este operador devuelve verdadero si la consulta que le sigue devuelve algún valor. Si no, devuelve falso. Se utiliza sobre todo en consultas correlacionadas. Ejemplo:
SELECT tipo,modelo, precio_venta
FROM piezas p WHERE EXISTS (SELECT tipo,modelo FROM existencias
WHERE tipo=p.tipo AND modelo=p.modelo);
Esta consulta devuelve las piezas que se encuentran en la tabla de existencias (es igual al ejemplo comentado en el apartado subconsultas sobre múltiples valores). La consulta contraria es :
SELECT tipo,modelo, precio_venta
FROM piezas p WHERE NOT EXISTS (SELECT tipo,modelo FROM existencias
WHERE tipo=p.tipo AND modelo=p.modelo);
Normalmente las consultas EXISTS se pueden realizar de alguna otra forma con los operadores ya comentados.
Sub Consultas en Oracle
Definición de subconsultas.
Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT. Normalmente se utilizan para filtrar una clausula WHERE o HAVING con el conjunto de resultados de la subconsulta, aunque también pueden utilizarse en la lista de selección.
Por ejemplo podriamos consultar el alquirer último de un cliente.
SELECT CO_CLIENTE, NOMBRE, MARCA, MODDELO FROM ALQUILERES WHERE CO_CLIENTE = 1 AND FECHA_ALQUILER = (SELECT MAX(FECHA_ALQUILER) FROM ALQUILERES WHERE CO_CLIENTE = 1)
En este caso, la subconsulta se ejecuta en primer lugar, obteniendo el valor de la máxima fecha de alquier, y posteriormente se obtienen los datos de la consulta principal.
Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis.
La subconsulta se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HAVING de la consulta principal.
Tiene las siguientes reestricciones:
No puede contener la cláusula ORDER BY
No puede ser la UNION de varias sentencias SELECT
Si la subconsulta aparece en la lista de selección,o esta asociada a un operador igual "=" solo puede devolver un único registro.
Referencias externas
A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna de la fila actual en la consulta principal, ese nombre de columna se denomina referencia externa.
Una referencia externa es un campo que aparece en la subconsulta pero se refiere a la una de las tablas designadas en la consulta principal.
Cuando se ejecuta una consulta que contiene una subconsulta con referencias externas, la subconsulta se ejecuta por cada fila de la consulta principal.
En este ejemplo la subconsulta aparece en la lista de selección, ejecutandose una vez por cada fila que devuelve la consulta principal.
SELECT CO_EMPLEADO, NOMBRE, (SELECT MIN(FECHA_NOMINA) FROM NOMINAS WHERE CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO) PRIMERA_NOMINA FROM EMPLEADOS;
Anidar subconsultas
Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula WHERE (por ejemplo) de otra subconsulta que a su vez forma parte de otra consulta principal.
SELECT CO_EMPLEADO, EMPLEADOS FROM EMPLEADOS WHERE CO_EMPLEADO IN (SELECT CO_EMPLEADO FROM NOMINAS WHERE ESTADO IN ( SELECT ESTADO FROM ESTADOS_NOMINAS WHERE EMITIDO = 'S' AND PAGADO = 'N') )
Los resultados que se obtienen con subconsultas normalmente pueden conseguirse a través de consultas combinadas ( JOIN ).
SELECT CO_EMPLEADO, NOMBRE FROM EMPLEADOS WHERE ESTADO IN (SELECT ESTADO FROM ESTADOS WHERE ACTIVO = 'S')
Podrá escribirse como :
SELECT CO_EMPLEADO, NOMBRE FROM EMPLEADOS, ESTADOS WHERE EMPLEADOS.ESTADO = ESTADOS.ESTADO AND ESTADOS.ACTIVO = 'S'
Normalmente es más rápido utilizar un JOIN en lugar de una subconsulta, aunque esto depende sobre todo del diseño de la base de datos y del volumen de datos que tenga.
Utilizacion de subconsultas con UPDATE
Podemos utilizar subconsultas también en consultas de actualización conjuntamente con UPDATE. Normalmente se utilizan para "copiar" el valor de otra tabla.
UPDATE EMPLEADOS SET SALARIO_BRUTO = (SELECT SUM(SALIRO_BRUTO) FROM NOMINAS WHERE NOMINAS.CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO) WHERE SALARIO_BRUTO IS NULL
La función EXISTS
EXISTS es una función SQL que devuelve veradero cuando una subconsulta retorna al menos una fila.
SELECT CO_CLIENTE, NOMBRE FROM CLIENTES WHERE EXISTS ( SELECT * FROM MOROSOS WHERE CO_CLIENTE = CLIENTES.CO_CLIENTE AND PAGADO = 'N')
Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT. Normalmente se utilizan para filtrar una clausula WHERE o HAVING con el conjunto de resultados de la subconsulta, aunque también pueden utilizarse en la lista de selección.
Por ejemplo podriamos consultar el alquirer último de un cliente.
SELECT CO_CLIENTE, NOMBRE, MARCA, MODDELO FROM ALQUILERES WHERE CO_CLIENTE = 1 AND FECHA_ALQUILER = (SELECT MAX(FECHA_ALQUILER) FROM ALQUILERES WHERE CO_CLIENTE = 1)
En este caso, la subconsulta se ejecuta en primer lugar, obteniendo el valor de la máxima fecha de alquier, y posteriormente se obtienen los datos de la consulta principal.
Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis.
La subconsulta se puede encontrar en la lista de selección, en la cláusula WHERE o en la cláusula HAVING de la consulta principal.
Tiene las siguientes reestricciones:
No puede contener la cláusula ORDER BY
No puede ser la UNION de varias sentencias SELECT
Si la subconsulta aparece en la lista de selección,o esta asociada a un operador igual "=" solo puede devolver un único registro.
Referencias externas
A menudo, es necesario, dentro del cuerpo de una subconsulta, hacer referencia al valor de una columna de la fila actual en la consulta principal, ese nombre de columna se denomina referencia externa.
Una referencia externa es un campo que aparece en la subconsulta pero se refiere a la una de las tablas designadas en la consulta principal.
Cuando se ejecuta una consulta que contiene una subconsulta con referencias externas, la subconsulta se ejecuta por cada fila de la consulta principal.
En este ejemplo la subconsulta aparece en la lista de selección, ejecutandose una vez por cada fila que devuelve la consulta principal.
SELECT CO_EMPLEADO, NOMBRE, (SELECT MIN(FECHA_NOMINA) FROM NOMINAS WHERE CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO) PRIMERA_NOMINA FROM EMPLEADOS;
Anidar subconsultas
Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la cláusula WHERE (por ejemplo) de otra subconsulta que a su vez forma parte de otra consulta principal.
SELECT CO_EMPLEADO, EMPLEADOS FROM EMPLEADOS WHERE CO_EMPLEADO IN (SELECT CO_EMPLEADO FROM NOMINAS WHERE ESTADO IN ( SELECT ESTADO FROM ESTADOS_NOMINAS WHERE EMITIDO = 'S' AND PAGADO = 'N') )
Los resultados que se obtienen con subconsultas normalmente pueden conseguirse a través de consultas combinadas ( JOIN ).
SELECT CO_EMPLEADO, NOMBRE FROM EMPLEADOS WHERE ESTADO IN (SELECT ESTADO FROM ESTADOS WHERE ACTIVO = 'S')
Podrá escribirse como :
SELECT CO_EMPLEADO, NOMBRE FROM EMPLEADOS, ESTADOS WHERE EMPLEADOS.ESTADO = ESTADOS.ESTADO AND ESTADOS.ACTIVO = 'S'
Normalmente es más rápido utilizar un JOIN en lugar de una subconsulta, aunque esto depende sobre todo del diseño de la base de datos y del volumen de datos que tenga.
Utilizacion de subconsultas con UPDATE
Podemos utilizar subconsultas también en consultas de actualización conjuntamente con UPDATE. Normalmente se utilizan para "copiar" el valor de otra tabla.
UPDATE EMPLEADOS SET SALARIO_BRUTO = (SELECT SUM(SALIRO_BRUTO) FROM NOMINAS WHERE NOMINAS.CO_EMPLEADO = EMPLEADOS.CO_EMPLEADO) WHERE SALARIO_BRUTO IS NULL
La función EXISTS
EXISTS es una función SQL que devuelve veradero cuando una subconsulta retorna al menos una fila.
SELECT CO_CLIENTE, NOMBRE FROM CLIENTES WHERE EXISTS ( SELECT * FROM MOROSOS WHERE CO_CLIENTE = CLIENTES.CO_CLIENTE AND PAGADO = 'N')
Suscribirse a:
Entradas (Atom)