jueves, 28 de febrero de 2008

Excepciones en PL/SQL

Manejo de excepciones

En PL/SQL una advertencia o condición de error es llamada una excepción.

Las excepciones se controlan dentro de su propio bloque.La estructura de bloque de una excepción se muestra a continuación.


DECLARE
-- Declaraciones
BEGIN
-- Ejecucion
EXCEPTION
-- Excepcion
END;


Cuando ocurre un error, se ejecuta la porción del programa marcada por el bloque EXCEPTION, transfiriéndose el control a ese bloque de sentencias.

El siguiente ejemplo muestra un bloque de excepciones que captura las excepciones NO_DATA_FOUND y ZERO_DIVIDE. Cualquier otra excepcion será capturada en el bloque WHEN OTHERS THEN.


DECLARE
-- Declaraciones
BEGIN
-- Ejecucion
EXCEPTION
WHEN NO_DATA_FOUND THEN

-- Se ejecuta cuando ocurre una excepcion
de tipo NO_DATA_FOUND
WHEN ZERO_DIVIDE THEN
-- Se ejecuta cuando ocurre una excepcion
de tipo ZERO_DIVIDE


WHEN OTHERS THEN
-- Se ejecuta cuando ocurre una excepcion
de un tipo no tratado
-- en los bloques anteriores


END;

Como ya hemos dicho cuando ocurre un error, se ejecuta el bloque EXCEPTION, transfiriéndose el control a las sentencias del bloque. Una vez finalizada la ejecución del bloque de EXCEPTION no se continua ejecutando el bloque anterior.

Si existe un bloque de excepcion apropiado para el tipo de excepción se ejecuta dicho bloque. Si no existe un bloque de control de excepciones adecuado al tipo de excepcion se ejecutará el bloque de excepcion WHEN OTHERS THEN (si existe!). WHEN OTHERS debe ser el último manejador de excepciones.

Las excepciones pueden ser definidas en forma interna o explícitamente por el usuario. Ejemplos de excepciones definidas en forma interna son la división por cero y la falta de memoria en tiempo de ejecución. Estas mismas condiciones excepcionales tienen sus propio tipos y pueden ser referenciadas por ellos: ZERO_DIVIDE y STORAGE_ERROR.

Las excepciones definidas por el usuario deben ser alcanzadas explícitamente utilizando la sentencia RAISE.

Con las excepciones se pueden manejar los errores cómodamente sin necesidad de mantener múltiples chequeos por cada sentencia escrita. También provee claridad en el código ya que permite mantener las rutinas correspondientes al tratamiento de los errores de forma separada de la lógica del negocio.

Excepciones predefinidas

PL/SQL proporciona un gran número de excepciones predefinidas que permiten controlar las condiciones de error más habituales.

Las excepciones predefinidas no necesitan ser declaradas. Simplemente se utilizan cuando estas son lanzadas por algún error determinado.

La siguiente es la lista de las excepciones predeterminadas por PL/SQL y una breve descripción de cuándo son accionadas:

  • ACCESS_INTO_NULL/-6530 El programa intentó asignar valores a los atributos de un objeto no inicializado

  • COLLECTION_IS_NULL/-6531 El programa intentó asignar valores a una tabla anidada aún no inicializada
  • CURSOR_ALREADY_OPEN/-6511 El programa intentó abrir un cursor que ya se encontraba abierto. Recuerde que un cursor de ciclo FOR automáticamente lo abre y ello no se debe especificar con la sentencia
  • OPEN DUP_VAL_ON_INDEX/-1 El programa intentó almacenar valores duplicados en una columna que se mantiene con restricción de integridad de un índice único (unique index)
  • INVALID_CURSOR/-1001 El programa intentó efectuar una operación no válida sobre un cursor
  • INVALID_NUMBER/-1722 En una sentencia SQL, la conversión de una cadena de caracteres hacia un número falla cuando esa cadena norepresenta un número válido
  • LOGIN_DENIED/-1017 El programa intentó conectarse a Oracle con un nombre de usuario o password inválido
  • NO_DATA_FOUND/100 Una sentencia SELECT INTO no devolvió valores o el programa referenció un elemento no inicializado en una tablaindexada
  • NOT_LOGGED_ON/1012 El programa efectuó una llamada a Oracle sin estar conectado
  • PROGRAM_ERROR/-6501 PL/SQL tiene un problema interno
  • ROWTYPE_MISMATCH/-6504 Los elementos de una asignación (el valor a asignar y la variable que lo contendrá) tienen tipos incompatibles. También se presenta este error cuando un parámetro pasado a un subprograma no es del tipo esperado
  • SELF_IS_NULL/-30625 El parámetro SELF (el primero que es pasado a un método MEMBER) es nulo
  • STORAGE_ERROR/-6500 La memoria se terminó o está corrupta
  • SUBSCRIPT_BEYOND_COUNT/-6533 El programa está tratando de referenciar un elemento de un arreglo indexado que se encuentra en una posición más grande que el número real de elementos de la colección
  • SUBSCRIPT_OUTSIDE_LIMIT/-6532 El programa está referenciando un elemento de un arreglo utilizando un número fuera del rango permitido (por ejemplo, el elemento “-1”)
  • SYS_INVALID_ROWID/-1410 La conversión de una cadena de caracteres hacia un tipo rowid falló porque la cadena no representa un número
  • TIMEOUT_ON_RESOURCE/51 Se excedió el tiempo máximo de espera por un recurso en Oracle
  • TOO_MANY_ROWS/1422 Una sentencia SELECT INTO devuelve más de una fila
  • VALUE_ERROR/-6502 Ocurrió un error aritmético, de conversión o truncamiento. Por ejemplo, sucede cuando se intenta calzar un valor muy grande dentro de una variable más pequeña
  • ZERO_DIVIDE/-1476 El programa intentó efectuar una división por cero


Excepciones definidas por el usuario

PL/SQL permite al usuario definir sus propias excepciones, las que deberán ser declaradas y lanzadas explícitamente utilizando la sentencia RAISE.

Las excepciones deben ser declaradas en el segmento DECLARE de un bloque, subprograma o paquete. Se declara una excepción como cualquier otra variable, asignandole el tipo EXCEPTION. Las mismas reglas de alcance aplican tanto sobre variables como sobre las excepciones.


DECLARE
-- Declaraciones

MyExcepcion EXCEPTION;
BEGIN
-- Ejecucion
EXCEPTION
-- Excepcion
END;

Reglas de Alcance

Una excepcion es válida dentro de su ambito de alcance, es decir el bloque o programa donde ha sido declarada. Las excepciones predefinidas son siempre válidas.

Como las variables, una excepción declarada en un bloque es local a ese bloque y global a todos los sub-bloques que comprende.

La sentencia RAISE

La sentencia RAISE permite lanzar una excepción en forma explícita. Es posible utilizar esta sentencia en cualquier lugar que se encuentre dentro del alcance de la excepción.


DECLARE

-- Declaramos una excepcion identificada por VALOR_NEGATIVO

VALOR_NEGATIVO EXCEPTION;

valor NUMBER;
BEGIN
-- Ejecucion

valor := -1;

IF valor <> THEN

RAISE VALOR_NEGATIVO;

END IF;


EXCEPTION
-- Excepcion

WHEN VALOR_NEGATIVO THEN

dbms_output.put_line('El valor no puede ser negativo');
END;

Con la sentencia RAISE podemos lanzar una excepción definida por el usuario o predefinida, siendo el comportamiento habitual lanzar excepciones definidas por el usuario.

Recordar la existencia de la excepción OTHERS, que simboliza cualquier condición de excepción que no ha sido declarada. Se utiliza comúnmente para controlar cualquier tipo de error que no ha sido previsto. En ese caso, es común observar la sentencia ROLLBACK en el grupo de sentencias de la excepción o alguna de las funciones SQLCODESQLERRM, que se detallan en el próximo punto.

Uso de SQLCODE y SQLERRM

Al manejar una excepción es posible usar las funciones predefinidas SQLCode y SQLERRM para aclarar al usuario la situación de error acontecida.

SQLcode devuelve el número del error de Oracle y un 0 (cero) en caso de exito al ejecutarse una sentencia SQL.

Por otra parte, SQLERRM devuelve el correspondiente mensaje de error.

Estas funciones son muy útiles cuando se utilizan en el bloque de excepciones, para aclarar el significado de la excepción OTHERS.

Estas funciones no pueden ser utilizadas directamente en una sentencia SQL, pero sí se puede asignar su valor a alguna variable de programa y luego usar esta última en alguna sentencia.


DECLARE
err_num NUMBER;
err_msg VARCHAR2(255);
result NUMBER;
BEGIN
  SELECT 1/0 INTO result
FROM DUAL;

EXCEPTION
WHEN OTHERS THEN

err_num := SQLCODE;
err_msg := SQLERRM;
  DBMS_OUTPUT.put_line('Error:'||TO_CHAR(err_num));
DBMS_OUTPUT.put_line(err_msg);
END;

También es posible entregarle a la función SQLERRM un número negativo que represente un error de Oracle y ésta devolverá el mensaje asociado.


DECLARE
msg VARCHAR2(255);
BEGIN
msg := SQLERRM(-1403);
DBMS_OUTPUT.put_line(MSG);
END;

Mensajes de Texto en PLSQL

Para mostrar mensajes en PL/SQL existe un paquete denominado DBMS_OUTPUT que incluye un conjunto de procedimientos y funciones que permiten almacenar información en un "buffer", información que puede recuperarse más tarde. Estas funciones pueden utilizarse también para mostrar la información almacenada en dicho "buffer" a los usuarios.

Dentro de estas funciones encontramos, por ejemplo, la función PUT_LINE que pone en el "buffer" parte de la información seguida por un "end-of-line". Esta función puede usarse también para mostrar texto a los usuarios. La función utiliza un único parámetro del tipo carácter y si la función se usa para mostrar mensajes a los usuarios, entonces el contenido del parámetro se corresponde con el contenido del mensaje. Ejemplo:

dbms_output.put_line(texto_del_mensaje);

Es importante tener en cuenta que para que la aplicación muestre los mensajes al usuario, el parámetro del entorno SQL*Plus SERVEROUTPUT debe estar puesto en ON.


De igual forma en se debe de inicializar el buffer en el PLSQL, para poder escribir sobre el.
Ejm: dbms_output.enable(1000000);

martes, 26 de febrero de 2008

sysoper y sysdba

  1. OBJETIVOS

  2. Breve articulo para referenciar a los usuarios con privilegios sysoper,sysdba el archivo de contraseñas de oracle y el rol DBA.

  3. INTRODUCCIÓN

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

    1. “sys as sysdba”
    2. “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.

    ..
  5. ROL DBA

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

    1. SYS
    2. 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

Auditar Conexiónes

Se pretende mediante un sencillo ejemplo práctico para ver cómo se puede auditar las conexiónes a una base de datos ORACLE o auditar los intentos de modificación a las tablas de un usuario.

Teniendo en cuenta que el parámetro que habilita la posibilidad de auditar la base de datos ORACLE en el init.ora es audit_trail que el comando sql que activa la auditoría sobre algo es AUDIT ( para desactivar NOAUDIT ) y que la tabla para mirar ( usuario sys ) el seguimiento de auditoría es dba_audit_trail vamos a realizar este sencillo ejemplo.

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

    Controlar el TableSpaces

    1. OBJETIVOS

    2. Automatizar el control de espacios libre en los tablespaces de base de datos mediante un procedimiento pl/sql.
      Se pretende facilitar un procedimiento que compruebe el espacio libre del tablespace y si este es menor de un portentaje especificado del total se envie un correo de alerta para que el administrador pueda aumentar el tamaño del tablespace


    3. COMO VER EL ESPACIO LIBRE DE LOS TABLESPACES DE ORACLE

    4. Para ver el espacio libre que queda en un tablespace tenemos que mirar en la tabla dba_free_space

      SELECT tablespace_name,
      ROUND(sum(bytes)/1024/1024,0)
      FROM dba_free_space
      WHERE tablespace_name NOT LIKE 'TEMP%'
      GROUP BY tablespace_name;

      Con esta consulta obentemos el nombre del tablespace y el espacio en Megas libre

      Para ver el espacio total en un tablespace tenemos que mirar en la tabla dba_data_files

      SELECT tablespace_name,
      round(sum(BYTES/1024/1024),0)
      FROM dba_data_files b
      WHERE tablespace_name NOT LIKE 'TEMP%'
      GROUP BY b.tablespace_name;

      Con esta consulta obentemos el nombre del tablespace y el espacio total en Megas que puede llegar a tener


    5. COMO ENVIAR UN CORREO EN PL/SQL

    6. Como hemos dicho en el objetivo de este articulo, cuando el tablespace se esté quedando sin espacio libre, se recibirá un correo.
      Vamos a proporcionar un procedimiento para poder enviar un correo.

      CREATE OR REPLACE PROCEDURE SEND_MAIL(SENDER IN VARCHAR2, RECIPIENT IN VARCHAR2, SUBJECT IN VARCHAR2, MESSAGE IN VARCHAR2) IS
      -- SENDER: direccion de correo de quien envia el mail
      -- RECIPIENT: dirreción de correo a la que va dirigida el mail
      -- SUBJECT: Es el asunto del correo
      -- ESSAGE: es el texto del mensaje
      mailhost CONSTANT VARCHAR2(30) := 'mail.server.es'; -- servidor de correo , sustituir cadena por una valida
      mesg VARCHAR2(1000); -- texto del mensaje
      mail_conn UTL_SMTP.CONNECTION; -- conexion con el servidor smtp
      BEGIN
      mail_conn := utl_smtp.open_connection(mailhost, 25);
      mesg := 'Date: ' ||
      TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || CHR(13) || CHR(10) ||
      'From: <'|| Sender ||'>' || CHR(13) || CHR(10) ||
      'Subject: '|| Subject || CHR(13) || CHR(10)||
      'To: '||Recipient || CHR(13) || CHR(10) || '' || CHR(13) || CHR(10) || Message;
      utl_smtp.helo(mail_conn, mailhost);
      utl_smtp.mail(mail_conn, Sender);
      utl_smtp.rcpt(mail_conn, Recipient);
      utl_smtp.data(mail_conn, mesg);
      utl_smtp.quit(mail_conn);
      EXCEPTION
      WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20004,SQLERRM);
      END send_mail;

      Sustituir mail.server.es por un servidor smtp valido


    7. PROCEDIMIENTO PARA CONTROLAR EL ESPACIO LIBRE DE LOS TABLESPACES

    8. A través de este procedimiento comprobamos que queda más de un porcentaje establecido libre en el tablespace con respecto a su espacio total
      Si el espacio libre es menor al limite establecido ( portentaje ) del total del tablespace se envia un correo utilizando el procedimiento que se ha explicado en el punto anterior.

      CREATE OR REPLACE PROCEDURE ALERTA_ESPACIO (limite number) IS
      -- CREAMOS EL CURSOR CON EL NOMBRE DE LOS TABLESPACES
      -- Y ESPACIO LIBRE
      CURSOR c_espacio_libre
      IS
      SELECT tablespace_name,
      ROUND(sum(bytes)/1024/1024,0)
      FROM dba_free_space
      WHERE tablespace_name NOT LIKE 'TEMP%'
      GROUP BY tablespace_name;
      -- CREAMOS EL CURSOR CON EL NOMBRE DE LOS TABLESPACES
      -- Y ESPACIO total
      CURSOR c_espacio_total
      IS
      select tablespace_name,
      round(sum(BYTES/1024/1024),0)
      FROM dba_data_files b
      WHERE tablespace_name NOT LIKE 'TEMP%'
      GROUP BY b.tablespace_name;
      -- DEFINIMOS LAS VARIABLES PARA METER EL CONTENIDO DEL CURSOR
      c_nombre VARCHAR2(20);
      c_libre NUMBER(10);
      c_total NUMBER(10);
      v_bbdd VARCHAR(20);
      BEGIN
      -- OBTENEMOS EL NOMBRE DE LA BASE DE DATOS
      SELECT name into v_bbdd from v$database;
      -- ABRIMOS EL CURSOR Y NOS POSICIONAMOS EN LA PRIMERA LINEA
      OPEN c_espacio_libre;
      OPEN c_espacio_total;
      FETCH c_espacio_libre INTO c_nombre,c_libre;
      FETCH c_espacio_total INTO c_nombre,c_total;
      -- EN CASO DE QUE EXISTA RESULTADO REALIZAMOS LAS COMPROBACIONES DE ESPACIO
      WHILE c_espacio_libre%found
      LOOP
      -- comprobacion del tablespace ES MENOR DE limite MEGAS
      IF (c_libre * 100) / c_total < limite THEN
      send_mail('dedireccion@orasite.es','paradirecion@orasite.es',
      'ALERTA DE ESPACIO EN BASE DE DATOS ' || v_bbdd,
      'El tablespace con nombre: ' || c_nombre || ' se esta quedando sin espacio' ||chr(10)||
      'El tamaño restante es de: ' || c_libre || ' Megas');
      END IF;
      FETCH c_espacio_libre INTO c_nombre, c_libre;
      FETCH c_espacio_total INTO c_nombre,c_total;
      END LOOP;
      CLOSE c_espacio_libre;
      CLOSE c_espacio_total;
      END;
      /

      Este procedimiento recibe un parametro, que será el porcentaje que queramos comprobar. Un valor normal sería 10, de esta forma comprobará que el espacio libre sea mayor de un 10 por ciento del tamaño total del tablespace.
      En caso de que no sea mayor que ese límite puesto, se enviará un mail. Modificar las direcciones de correo, por direcciones de correo validas.


    9. AUTOMATIZAR LA TAREA DE COMPROBACION DE TABLESPACES

    10. Esta tarea se puede automatizar poniendo un job ( tarea ) en la base de datos y que compruebe cada x tiempo si los tablespaces se han llenado.
      Si no tenemos ningún job en la base de datos, antes de poner un job tenemos que asegurarnos que el valor job_queue_processes es mayor que 0.

    Usuarios

    1. CREACIÓN DE USUARIOS
    2. 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.

    3. AUTENTICACIÓN ORACLE
    4. 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.

    5. ASIGNACIONES A LOS USUARIOS
    6. 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

    Memoria de ORACLE

    El total disponible de memoria en un sistema tiene que estar configurado de forma que todos los componentes de ese sistema funcionen óptimamente. Una pauta a seguir para que el sistema quede bien configurado podría ser el siguiente:

    Componentes del sistema Memoria del Sistema
    Oracle SGA Componentes ~ 50\% del total
    Sistema operativo + Otros componentes ~15\% del total
    Memoria de usuario ~ 35\% del total

    Esta es la primera pauta que podemos seguir a la hora de reservar o ver la memoria que se necesita o que se puede poner cómo máximo en un sistema para que Oracle funcione correctamente y los demás componentes del sistema puedan hacerlo también. ( habría que tener en cuenta también el número de usuarios que accederán concurrentemente al sistema ).
    Una vez que hemos decidido que la SGA de nuestra base de datos ORACLE va a ser el 50\% de la memoria total del sistema. Esta memoria la tenemos que dividir entre los componentes que la forman. (Database buffer cache, shared_pool_area, fixed size, redo log buffer)

    Componentes de la SGA Memoria SGA
    Database Buffer Cache ~80\% de la SGA
    Shared Pool Area ~12\% de la SGA
    Fixed Size ~1\% de la SGA
    Redo Log Buffer ~0.1\% de la SGA

    La distribución puede venir bien para comenzar a establecer un sistema, aunque posteriormente podrán variar (y habrá que realizar tuning de ellos ) cuando se conozca o varíen el tipo de acceso a la base de datos, los patrones de acceso, usuarios concurrentes en el sistema etc.
    Para entender mejor estas tablas proponemos el siguiente ejemplo:
    Tengo un sistema configurado con 2 GB de memoria y con una estimación de 100 sesiones concurrentes. La aplicación requiere responder en pocos segundos. Es una base de datos que tiene muchas transacciones.

    Componentes del sistema Memoria asignada (en Mb)
    SGA para ORACLE ~1024
    Sistema operativo + Otros componentes ~306
    Memoria de usuario ~694

    Los 694 MB estarán disponibles para la PGA y todos los procesos servidores de Oracle.
    Teniendo en cuenta que en el ejemplo hemos dicho que teníamos 100 sesiones concurrentes nos daría un promedio de unos 7 Megas (aproximadamente) para el consumo de cada usuario. (Tenemos que tener en cuenta que el SORT_AREA_SIZE forma parte de la PGA)
    Por ultimo habría que distribuir la memoria que hemos dado a la SGA entre todos sus componentes.

    Componentes de la SGA Memoria asignada (en Mb)
    Database Buffer Cache ~800
    Shared Pool Area ~128 - 188
    Fixed Size + Misc ~ 8
    Redo Log Buffer ~ 1 (promedio 512K)

    Arranque y Parada

    1. Arrancar base de datos

    2. El arranque de una base de datos ORACLE requiere tres etapas

      1. Arrancar la instancia
      2. Montar la base de datos
      3. Abrir la base de datos
      1. Arrancar la base de datos

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

      3. Montar la base de datos

      4. En esta parte del proceso de arranque se produce la conexión al/los archivo/s de control.

        En este estado se puede:

        1. Cambiar el modo de archivado de la B.D.
        2. Renombrado de archivos de Redo Log o del asociado al tablespace SYSTEM
        3. 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

      5. Abrir base de datos

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

    3. Mas alternativas para el arranque de base de datos

    4. Arranque solo para usuarios con el privilegio RESTRICTED SESSION

      SQLPLUS> startup restrict

      Arranque forzado

      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

    5. Parada base de datos

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

      1. shutdown normal
      2. shutdown immediate
      3. shutdown abort


      1. Shutdown normal

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

      3. Shutdown immediate

      4. Espera a que las transacciones actuales se completen

        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

      5. Shutdown abort

      6. Parada drástica, no espera a que los usuarios conectados actualmente finalicen sus transacciones. El usuario conectado recibe el mensaje "No logged on".

        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

    El listener

    TNS Listener es un proceso servidor que provee la conectividad de red con la base de datos Oracle. El listener está configurado para escuchar la conexión en un puerto específico en el servidor de base de datos. Cuando una se pide una conexión a la base de datos, el listener devuelve la información relativa a la conexión. La información de una conexión para una instancia de una base de datos provee el nombre de usuario, la contraseña y el SID de la base de datos. Si estos datos no son correctos se devolverá un mensaje de error.

    • Por defecto el puerto del listener es el 1521
    • El listener no limita el número de conexiones a la base de datos

    Toda la información del listener la contiene un archivo denominado listener.ora ( $ORACLE_HOME/network/admin. )

    El comando para gestionar el listener es lsnrctl. Mediante este comando podemos:

    • Parar el listener.
    • Ver el estado del listener.
    • Arrancar el listener.
    • Rearrancar el listener.

  • SEGURIDAD LISTENER ORACLE 10G (securing the listener)
  • El principal paso para realizar la seguridad en el listener es ponerle una contraseña password.

    El primer método para poner una contraseña al listener es editando el fichero listener.ora y escribiendo la siguiente línea:

    PASSWORDS_LISTENER = orapass

    Cuando guardemos el fichero con los cambios realizamos un reload del listener

    lsnrctl> reload

    Nota: El comando para entrar en el listener es lsnrctl ( $ORACLE_HOME/bin )

    El segundo método para poder cambiar la contraseña al listener es el siguiente:

    lsnrctl> change_password

    Este comando te pedirá la clave antigua y la nueva clave.
    Si es la primera vez que ejecutas este comando la contraseña antigua ( old password ) habrá que dejarla en blanco.

    El comando SET y SAVE CONFIG permite guardar esos cambios en el listener porque ahora el listener está gobernado por un password.

    lsnrctl > set password

    lsnrctl > save config

    La información antigua se guardará enlistener.bck y listener.ora se actualizará con los nuevos datos.

    Ejemplo de configuración del listener.ora

    LISTENER9 =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 193.168.4.220)(PORT = 2484))
    )
    )
    )
    SID_LIST_LISTENER9 =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = orasite)
    (ORACLE_HOME = /oracle9/product/9.2.0)
    (SID_NAME = orasite)
    )
    )

    Parámetros del archivo:

    HOST: Dirección ip del servidor de base de datos

    PORT: Puerto de escucha de la base de datos ( por defecto suele ser el 1521 )

    CLOBAL_DB_NAME: Nombre de la base de datos

    ORACLE_HOME: Directorio de instalación de ORACLE ( ORACLE_HOME )

    SID_NAME: SID de la base de datos ( muchas veces coincide con el GLOBAL_DB_NAME )

    Este archivo incluye:

    • Direcciones de protocolo en las que acepta solicitudes de conexión.
    • Servicios de base de datos
    • Parámetros de control utilizados por el listener.

    Diccionario de Datos



    Oracle posee un diccionario de datos; es decir la manera de extraer el catálogo de objetos de una base de datos, nos refererimos a: tablas, usuarios, roles, vistas, columnas de las tablas, secuencias, constraints, sinónimos, índices, triggers, funciones etc.., esta información se encuentra contenida en tablas y vistas del sistema.

    Dichas tablas en base a las cuales podemos obtener esta información aplicando sentencias sql.

    A continuación enumeramos las tablas más importantes del diccionario de datos que nos permitirá obtener información de los objetos de la base de datos.

    Notas:
    Los prefijos dba_ son exclusividad para los usuarios que tengan el rol dba que sería el administrador de la base de datos y mostraría información de accesos de los usuarios que tienen asignado dicho rol.
    Los prefijos_user indican que mostrará información en el ámbito del usuario que se encuentre conectado; [esquema].[objeto].

    Información sobre todos los objetos: tablas, vistas, funciones, procedimientos, índices, triggers, etc. : dba_objects, user_objects, all_objects

    • Código de funciones y procedimientos: dba_source, user_source, all_source
    • Usuarios: dba_users, user_users, all_users
    • Roles: dba_roles
    • Roles asignados a roles o usuarios: dba_role_privs, user_role_privs
    • Privilegios asignados a roles o usuarios: dba_sys_privs
    • Permisos sobre tablas asignados a roles o usuarios: dba_tab_privs
    • Límites de recursos: user_resource_limits
    • Perfiles y sus límites de recursos asociados: dba_profiles
    • Límites de recursos en cuanto a restricciones en claves: user_password_limits
    • Límites de recursos en cuanto a espacio máximo en tablespaces: dba_ts_quotas, user_ts_quotas
    • Tablespaces: dba_tablespaces, user_tablespaces
    • Ficheros que componen los datafiles: dba_data_files
    • Segmentos: dba_segments, user_segments, all_segments
    • Segmentos de Rollback: dba_rollback_segs
    • Extensiones que forman los segmentos: dba_extents, user_extents
    • Bloques libres: dba_free_space, user_free_space
    • Bloques libres que podrían unirse: dba_free_space_coalesced
    • Secuencias: dba_sequences, user_sequences, all_sequences
    • Tablas, vistas, sinónimos y secuencias: dba_catalog, user_catalog, all_catalog
    • Tablas : dba_tables, user_tables, all_tables
    • Campos de tablas: dba_cons_columns, user_cons_columns, all_cons_columns
    • Columnas de las tablas: dba_tab_columns, user_tab_columns, all_tab_columns
    • Vistas: dba_views, user_views, all_views
    • Sinónimos: dba_synonyms, user_synonyms, all_synonyms
    • Restricciones de clave primaria, externa, not null, integridad referencial: dba_constraints, user_constraints, all_constraints
    • Índices: dba_indexes, user_indexes, all_indexes
    • Columnas de los índices: dba_ind_columns, user_ind_columns, all_ind_columns

    jueves, 21 de febrero de 2008

    Tablas

    El lenguaje de definición de datos (DDL, Data Definition Language) es el encargado de permitir la descripcion de los objetos que forman una base de datos.

    El lenguaje de definición de datos le va a permitir llevar a cabo las siguientes acciones:

    • Creación de tablas, índices y vistas.
    • Modificación de las estructura de tablas, índices y vistas.
    • Supresión de tablas, índices y vistas.

    Pero antes de continuar vamos a comentar la nomenclatura que emplearemos, si tiene algún conocimiento de programación le resultará familiar.



    Nomenclatura

    La sintaxis empleada para la sentencias en las diferentes páginas esta basada en la notación EBNF. Vamos a ver el significado de algunos simbolos.

    Símbolo
    Significado
    < >

    Encierran parámetros de una orden que el usuario debe sustituir al escribir dicha orden por los valores que queramos dar a los parámetros.

    [ ]

    Indica que su contenido es opcional.

    { }

    Indica que su contenido puede repetirse una o mas veces.

    |

    Separa expresiones. Indica que pueden emplearse una u otra expresión pero no más de una a la vez.

    Además las palabras clave aparecen en mayúscula negrita y los argumentos en minúscula cursiva.

    La sintaxis de una sentencia tendrá un aspecto como este:


    CREATE TABLE
    <nombre_tabla>
    (
    <nombre_campo> <tipo_datos(tamaño)>,
    {
    <nombre_campo> <tipo_datos(tamaño)>}
    ) ;



    Creación de tablas

    En el modelo relacional la información de una base de datos se almacena en tablas.

    La creación de la base de datos debe comenzar por con la creación de una o más tablas. Para ello utilizaremos la sentencia CREATE TABLE.

    La sintaxis de la sentencia es la siguiente:


    CREATE TABLE
    <nombre_tabla>
    (
    <nombre_campo> <tipo_datos(tamaño)>
    [null | not null] [default <valor_por_defecto>]
    {
    ,<nombre_campo> <tipo_datos(tamaño)>
    [null | not null] [default <valor_por_defecto>]}
    [
    , constraint <nombre> primary key (<nombre_campo>[ ,...n ])]
    [
    , constraint <nombre> foreign key (<nombre_campo>[ ,...n ])
    references ( <nombre_campo> [ ,...n ] ) ]
    ) ;

    Ejemplo: Vamos a simular una base de datos para un negocio de alquiler de coches, por lo que vamos a empezar creando una tabla para almacenar los coches que tenemos.


    CREATE TABLE tCoches
    (
    matricula char(8) not null,
    marca varchar(255) null,
    modelo varchar(255) null,
    color varchar(255) null,
    numero_kilometros numeric(14,2) null default 0,
    constraint PK_Coches primary key (matricula)
    ) ;

    En este ejemplo creamos una tabla llamada tCoches con cinco campos (matricula, marca, modelo, color, numero_kilometros).

    Notese que se han omitido las tildes y los espacios a proposito. Nunca cree campos que contengan caracteres especificos de un idioma (tildes, eñes, ...) ni espacios.

    Las claves primarias y externas (o foraneas) se pueden implementar directamente a través de la instrucción CREATE TABLE, o bien se pueden agregar a través de sentencias ALTER TABLE.

    Cada gestor de bases de datos implementa distintas opciones para la instrucción CREATE TABLE, pudiendo especificarse gran cantidad de parámetros y pudiendo variar el nombre que damos a los tipos de datos, pero la sintaxis standart es la que hemos mostrado aquí. Si queremos conocer más acerca de las opciones de CREATE TABLE lo mejor es recurrir a la documentación de nuestro gestor de base de datos.


    Modificación de tablas

    En ocasiones puede ser necesario modificar la estructura de una tabla, comúnmente para añadir un campo o reestricción. Para ello disponemos de la instruccción ALTER TABLE.

    ALTER TABLE nos va a permitir:

    • Añadir campos a la estructura incial de una tabla.
    • Añadir reestriciones y referencias.

    Para añadir un campo a una tabla existente:


    ALTER TABLE
    <nombre_tabla>
    ADD <tipo_datos(tamaño)>
    [null |not null] [default ]
    {
    , <nombre_campo> <tipo_datos(tamaño)>
    [null |not null] [default ]} ;

    Ejemplo:


    ALTER TABLE
    tCoches
    ADD num_plazas integer null default 5;

    En este ejemplo añadimos el campo num_plazas a la tabla tCoches que habiamos creado en el apartado anterior.

    Para añadir una clave primaria vamos a crear una tabla de cliente y le añadiremos la clave primaria ejecutando una sentencia alter table:


    CREATE TABLE
    tClientes
    (
    codigo integer not null,
    nombre varchar(255) not null,
    apellidos varchar(255) null,
    nif varchar(10) null,
    telefono varchar(9) null,
    movil varchar(9) null
    );
    ALTER TABLE tClientes ADD
    CONSTRAINT PK_tClientes primary key (codigo);

    Creamos la tabla clientes y le añadimos una reestricción primary key a la que damos el nombre PK_tClientes en el campo codigo.

    Solo podemos modificar una única tabla a la vez con ALTER TABLE, para modificar más de una tabla debemos ejecutar una sentencia ALTER TABLE por tabla.

    Para añadir una clave externa (o foranea) necesitamos una tercera tabla en nuestra estructura. Por un lado tenemos la tabla tCoches y la tabla tClientes, ahora vamos a crear la tabla tAlquileres que será la encargada de "decirnos" que clientes han alquilado un coche.


    CREATE TABLE
    tAlquileres
    (
    codigo integer not null,
    codigo_cliente integer not null,
    matricula char(8) not null,
    fx_alquiler datetime not null,
    fx_devolucion datetime null
    );

    ALTER TABLE tAlquileres ADD
    CONSTRAINT
    PK_tAlquileres primary key (codigo),
    CONSTRAINT FK_Clientes foreign key (codigo_cliente)
    references
    tClientes (Codigo),
    CONSTRAINT FK_Coches foreign key (matricula)
    references tCoches (matricula);

    Bien, en este código creamos la tabla tAlquileres, y luego mediante una sentencia ALTER TABLE añadimos una clave primaria llamada PK_tAlquileres en el campo codigo, una clave externa llamada FK_Clientes referenciada al codigo de la tabla tClientes, y por último otra clave externa llamada FK_Coches referenciada al campo matricula de la tabla tCoches.

    Nota:Cuando creamos una clave externa el campo referenciado y el que sirve de referencia deben ser del mismo tipo de datos.

    Si somos observadores nos daremos cuenta que los campos que sirven de referencia a las claves foraneas son las claves primarias de sus tablas. Sólo podemos crear claves externas que referencien claves primarias.

    Al igual que ocurria con la sentencia CREATE TABLE cada gestor de bases de datos implementa sus mejoras, siendo la mejor forma de conocerlas recurrir a la documentación del gestor de bases de datos.

    En principio, para borrar columnas de una tabla debemos:

    1. Crear una tabla con la nueva estructura.
    2. Transferir los datos
    3. Borrar la tabla original.

    y digo en principio, porque como ya hemos comentado según el gestor de bases de datos con el que trabajemos podremos realizar esta tarea a través de una sentencia ALTER TABLE.



    Eliminación de tablas.

    Podemos eliminar una tabla de una base de datos mediante la instruccion DROP TABLE.


    DROP TABLE
    <nombre_tabla>;

    La instrucción DROP TABLE elimina de forma permanente la tabla y los datos en ella contenida.

    Si intentamos eliminar una tabla que tenga registros relacionados a través de una clave externa la instrucción DROP TABLE fallará por integridad referencial.

    Cuando eliminamos una tabla eliminamos también sus índices.

    Sinónimos

    Un sinónimo es un nombre alternativo que identifica un tabla en la base de datos. Con un sinónimo se pretende normalmente simplicar el nombre original de la tabla, aunque tambien se suelen utilizar para evitar tener que escribir el nombre del propietario de la tabla.

    No todas las bases de datos soportan los sinónimos.

    Para crear un sinónimo hay uque utilizar la sentencia CREATE SYNONYM especificando el nombre que deseamos utilizar como sinónimo y la tabla para la que estamos creando el sinónimo.


    CREATE SYNONYM

    FOR ;

    Ejemplo: El siguente ejemplo crea el sinónimo Coches para la tabla tCoches.


    CREATE SYNONYM
    Coches
    FOR tCoches;

    Para eliminar el sinónimo creado debemos emplear la sentencia DROP SYNONYM.


    DROP SYNONYM Coches;

    Vistas

    En el modelo de datos relacional la forma de guardar la información no es la mejor para ver los datos

    Una vista es una consulta, que refleja el contenido de una o más tablas, desde la que se puede acceder a los datos como si fuera una tabla.

    Dos son las principales razones por las que podemos crear vistas.

    • Seguridad, nos pueden interesar que los usuarios tengan acceso a una parte de la información que hay en una tabla, pero no a toda la tabla.
    • Comodidad, como hemos dicho el modelo relacional no es el más comodo para visualizar los datos, lo que nos puede llevar a tener que escribir complejas sentencias SQL, tener una vista nos simplifica esta tarea.

    Las vistas no tienen una copia física de los datos, son consultas a los datos que hay en las tablas, por lo que si actualizamos los datos de una vista, estamos actualizando realmente la tabla, y si actualizamos la tabla estos cambios serán visibles desde la vista.

    Nota: No siempre podremos actualizar los datos de una vista, dependerá de la complejidad de la misma (dependerá de si el cojunto de resultados tiene acceso a la clave principal de la tabla o no), y del gestor de base de datos. No todos los gestores de bases de datos permiten actualizar vistas, ORACLE, por ejemplo, no lo permite, mientrar que SQL Server si.


    Creación de vistas.

    Para crear una vista debemos utilizar la sentencia CREATE VIEW, debiendo proporcionar un nombre a la vista y una sentencia SQL SELECT válida.


    CREATE VIEW

    AS
    ();

    Ejemplo:Crear una vista sobre nuestra tabla alquileres, en la que se nos muestre el nombre y apellidos del cliente en lugar de su código.


    CREATE VIEW vAlquileres
    AS
    (
    SELECT nombre,
    apellidos,
    matricula
    FROM tAlquileres,
    tClientes
    WHERE ( tAlquileres.codigo_cliente = tClientes.codigo )
    )

    Si queremos, modificar la definición de nuestra vista podemos utilizar la sentencia ALTER VIEW, de forma muy parecida a como lo haciamos con las tablas. En este caso queremos añadir los campos fx_alquiler y fx_devolucion a la vista.


    ALTER VIEW vAlquileres
    AS
    (
    SELECT nombre,
    apellidos,
    matricula,
    fx_alquiler,
    fx_devolucion
    FROM tAlquileres,
    tClientes
    WHERE ( tAlquileres.codigo_cliente = tClientes.codigo )
    )

    Por último podemos eliminar la vista a través de la sentencia DROP VIEW. Para eliminar la vista que hemos creado anteriormente se uitlizaría:


    DROP VIEW
    vAlquileres;

    Una vista se consulta como si fuese una tabla.

    Índices

    Definición de Índices

    Un índice es una estructura de datos que permite acceder a diferentes filas de una misma tabla a través de un campo (o campos clave).

    Un índice permite un acceso mucho más rápido a los datos.

    Introducción a los índices.

    Para entender lo que es un índice debemos saber primero como se almacena la información internamente en las tablas de una base de datos. Cada tabla se divide en páginas de datos, imaginemos un libro, podriamos escribirlo en "una sola hoja enorme" al estilo pergamino egipcio, o bien en páginas a las que podemos acceder rápidamente a traves de un índice. Está idea es la que se aplica en el mundo de las bases de datos, la información esta guardada en una tabla (el libro) que tiene muchas hojas de datos (las páginas del libro), con un índice en el que podemos buscar la información que nos interesa.

    Si queremos buscar la palabra zapato en un diccionario , ¿qué hacemos?

    • Leemos todo el diccionario hasta encontrar la palabra, con lo que nos habremos leido el diccionario enterito (¡seguro que aprenderiamos un montón!)
    • Buscamos en el índice en que página está la letra z, y es en esa página donde buscamos.

    Ni que decir tiene que la opción dos es la correcta, y es de este modo como se utiliza un índice en las bases de datos, se define el ínidice a través de un campo (o campos) y es a partir de este punto desde donde de busca.

    Los índices se actualizan automáticamente cuando realizamos operaciones de escritura en la base de datos. Este es un aspecto muy importante de cara al rendimiento de las operaciones de escritura, ya que además de escribir los datos en la tabla se escribiran también en el indice. Un número elevado de índices hará más lentas estas operaciones. Sin embargo, salvo casos excepcionales, el beneficio que aportan los indices compensa (de largo) esta penalización.



    Creación de índices

    La creación de índices, como ya hemos visto, permite acelerar las consultas que se realizan en la base de datos.

    Las sentencias de SQL para manipular índices son:


    CREATE INDEX
    ;

    DROP INDEX;

    La sintaxis para la creación de indices es la siguiente:


    CREATE
    [UNIQUE] INDEX
    ON (
    [ASC | DESC]
    {, [ASC | DESC]})
    );

    La pálabra clave UNIQUE especifica que que no pueden existir claves duplicadas en el índice.
    ASC | DESC especifican el criterio de ordenación elegido, ascendente o descendente, por defecto es ascendente.

    Ejemplo: En el apartado dedicado a la definición de tablas creamos la tabla tClientes, este ejmplo crea un índice único en el campo NIF. Esto nos permitirá buscar mucho mas rápido por el campo NIF y nos asegurará que no tengamos dos NIF iguales.


    CREATE UNIQUE INDEX
    UIX_CLIENTES_NIF
    ON tCLIENTES (NIF);

    Las claves primarias son índices.

    Los nombres de los índices deben ser únicos.

    Para eliminar un índice debemos emplear la sentencia DROP INDEX.


    DROP INDEX
    .;

    Ejemplo:Para eliminar el índice creado anteriormente.


    DROP INDEX
    tCLIENTES.UIX_CLIENTES_NIF;

    Subconsultas

    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')

    La función EXISTS puede ser utilizada en cualquier sentencia SQL váida, SELECT, UPDATE, INSERT o DELETE.

    Consultas agregadas

    La cláusula GROUP BY

    La clausula GROUP BY combina los registros con valores idénticos en un único registro. Para cada registro se puede crear un valor agregado si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción SELECT. Su sintaxis es:


    SELECT
    [ALL | DISTINCT ]
    <nombre_campo> [{,<nombre_campo>}]
    [{,}]
    FROM <nombre_tabla>|<nombre_vista>
    [{,<nombre_tabla>|<nombre_vista>}]
    [WHERE <condicion> [{ AND|OR }]]
    [GROUP BY <nombre_campo> [{,<nombre_campo >}]]
    [HAVING <condicion>[{ AND|OR }]]
    [ORDER BY <nombre_campo>| [ASC | DESC]
    [{,<nombre_campo>| [ASC | DESC ]}]]

    GROUP BY es opcional. Si se utiliza GROUP BY pero no existe una función SQL agregada en la instrucción SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de las funciones SQL agregadas.

    Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como argumentos de una función SQL agregada.


    SELECT marca, modelo, SUM(numero_kilometros)
    FROM tCoches
    GROUP BY marca, modelo

    La cláusula HAVING

    Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. Se utiliza la cláusula WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados.

    HAVING es similar a WHERE, determina qué registros se seleccionan. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuales de ellos se van a mostrar. HAVING permite el uso de funciones agregadas.


    SELECT
    marca, modelo, SUM(numero_kilometros)
    FROM tCoches
    WHERE marca <> 'BMW'
    GROUP BY marca, modelo
    HAVING SUM(numero_kilometros)>100000

    En el ejemplo anterior, no se cuentan los datos para todas las marcas menos "BMW", una vez que se han contado, se evalua HAVING, y el conjunto de resultados devuelve solo aquellos modelos con más de 100.000 km.


    AVG

    Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente


    AVG()

    En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.


    SELECT
    marca, modelo, AVG(numero_kilometros)
    FROM tCoches

    GROUP BY marca, modelo



    Count

    Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente:


    COUNT
    ()

    En donde expr contiene el nombre del campo que desea contar. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL). Puede contar cualquier tipo de datos incluso texto.

    Aunque expr puede realizar un cálculo sobre un campo, Count simplemente cuenta el número de registros sin tener en cuenta qué valores se almacenan en los registros. La función Count no cuenta los registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null. Count(*) es considerablemente más rápida que Count(Campo). No se debe poner el asterisco entre dobles comillas ('*').


    SELECT COUNT(*) FROM tCoches;
    SELECT marca, COUNT(modelo)
    FROM tCoches
    GROUP BY marca;
    SELECT marca, COUNT(DISTINCT modelo)
    FROM tCoches
    GROUP BY marca;


    Max, Min


    Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es:


    MIN
    ()
    MAX()

    En donde expr es el campo sobre el que se desea realizar el cálculo. Expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).


    SELECT
    marca, modelo, MIN(numero_kilometros)
    , MAX(numero_kilometros)
    FROM tCoches
    GROUP BY marca, modelo


    Sum

    Devuelve la suma del conjunto de valores contenido en un campo especifico de una consulta. Su sintaxis es:


    SUM
    ()

    En donde expr respresenta el nombre del campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).


    SELECT marca, modelo, SUM(numero_kilometros)
    FROM tCoches
    GROUP BY marca, modelo