Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 1/29 UNIDAD 7 GESTIÓN DE OBJETOS DE ESQUEMA 7.1. OBJETIVOS Definir objetos de esquema y tipos de dato. Crear y modificar tablas. Definir restricciones. Visualizar las columnas y el contenido de una tabla. Crear índices, Crear vistas. Crear secuencias. Explicar el uso de tablas temporales. Utilizar el diccionario de datos. 7.2. QUÉ ES UN ESQUEMA (SCHEMA) Un esquema es una recopilación de objetos de base de datos propiedad de un usuario en particular. Normalmente, en una base de datos de producción, este usuario no representa una persona, sino una aplicación. Un esquema tiene el mismo nombre que el usuario propietario del esquema. Los objetos de esquema son estructuras lógicas que hacen referencia directa a datos de la base de datos. Los objetos de esquema incluyen estructuras como tablas, vistas e índices (Figura 7.1). Se puede crear y manipular objetos de esquema mediante SQL o Enterprise Manager. Cuando se utiliza Enterprise Manager, el SQL subyacente se genera automáticamente. Un esquema no tiene que estar relacionado necesariamente de forma directa con un tablespace. Puede definir configuraciones de modo que los objetos de un esquema puedan estar en diferentes tablespaces, y un tablespace pueda contener objetos de diferentes esquemas. Al crear la base de datos, se crean automáticamente varios esquemas. Hay dos de especial importancia: Esquema SYS: contiene el diccionario de datos, Esquema SYSTEM: contiene tablas y vistas adicionales en las que se almacena información administrativa. Durante una instalación completa de una base de datos Oracle, se instalan automáticamente esquemas de ejemplo. Los esquemas de ejemplo sirven para proporcionar una plataforma común para ejemplos de la documentación y el plan de estudios de Oracle. Son un juego de esquemas entrelazados destinados a proporcionar ejemplos de diferentes niveles de complejidad e incluyen lo siguiente: HR: el esquema Human Resources es un esquema sencillo para introducir temas básicos. Una extensión de este esquema da soporte a las demostraciones de Oracle Internet Directory. OE: el esquema Order Entry se emplea en asuntos de complejidad intermedia. Hay una gran variedad de tipos de dato disponibles en el esquema OE. El subesquema OC (Online Catalog) es una recopilación de objetos de base de datos objeto‐relacional incorporados en el esquema OE. PM: el esquema Product Media está dedicado a tipos de dato multimedia. QS: el esquema Queued Shipping contiene un juego de esquemas que se utilizan para demostrar las capacidades de Oracle Advanced Queuing. SH: el esquema Sales History permite demostraciones con cantidades mayores de datos. Una extensión de este esquema proporciona soporte para el procesamiento analítico avanzado. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 2/29 Figura 7.1: Concepto de esquema 7.2.1. Acceso a Objetos de Esquema Se puede acceder rápidamente a varios tipos de objetos de esquema desde la región Schema de la página Database Administration (Figura 7.2). Después de hacer clic en uno de los enlaces, se mostrará la página Results. En la región Search de la página, se puede introducir un nombre de esquema y un nombre de objeto para buscar un objeto concreto. Además, se puede buscar otros tipos de objetos en la región Search seleccionando el tipo de objeto en la lista desplegable. La lista desplegable incluye tipos de objetos adicionales que no se muestran como enlaces en la página Database Administration. Figura 7.2: Acceso a objetos de esquema 7.3. NOMENCLATURA DE OBJETOS DE BASE DE DATOS Al asignar un nombre a un objeto de la base de datos, se pueden introducir el nombre con comillas dobles (“”). Pero se pueden romper varias reglas de nomenclatura mencionadas en la Figura 7.3. Sin embargo, no se recomienda esta operación porque si se asigna un nombre a un objeto de esta forma, siempre debe hacer referencia a él con el nombre entre comillas. Por ejemplo, si asigna a una tabla el nombre “Local Temp”, siempre deberá realizar lo siguiente: UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 3/29 SQL> select * from "Local Temp"; TEMP_DATE LO_TEMP HI_TEMP --------- ---------- ---------- 01-DEC-03 30 41 Si no se introduce adecuadamente el nombre en mayúsculas o minúsculas, recibirá un error: SQL> select * from "local temp"; select * from "local temp" * ERROR at line 1: ORA-00942: table or view does not exist Los nombres sin entrecomillar se almacenan en mayúscula y no son sensibles a mayúsculas/minúsculas. Cuando se procesa una sentencia SQL, los nombres sin entrecomillar se convierten a mayúsculas. Los identificadores sin entrecomillar sólo pueden incluir caracteres alfanuméricos del juego de caracteres de la base de datos y el carácter de subrayado (_), el signo del dólar ($) y el signo numeral (#). Los enlaces de base de datos también pueden contener puntos (.) y el signo de arroba (@). No se recomienda el uso de $ y # en identificadores sin entrecomillar. Los identificadores entre comillas pueden incluir cualquier carácter y signo de puntuación, así como espacios. Sin embargo, ni los identificadores entre comillas ni los sin entrecomillar pueden contener comillas dobles. Figura 7.3: Nomenclatura de Objetos de Base de Datos 7.4. MANEJO DE TABLAS El objetivo de esta sección es conocer los métodos de almacenamiento de datos que utiliza Oracle (Figura 7.4), y además describir los datos contenidos. Figura 7.4: Manejo de tablas UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 4/29 7.4.1. Especificación de Tipos de Dato en Tablas Al crear una tabla, debe especificar un tipo de dato para cada una de sus columnas (Figura 7.5). Al crear un procedimiento o función, debe especificar un tipo de dato para cada uno de sus argumentos. Estos tipos de dato definen el dominio de valores que puede incluir cada columna o que puede tener cada argumento. Entre los tipos de dato incorporados en la base de datos Oracle se incluyen los siguientes: CHAR: datos de tipo carácter de longitud fija en bytes o caracteres especificada por size. El tamaño máximo es 2.000 bytes o caracteres, y el tamaño por defecto y mínimo es 1 byte. BYTE indica que la semántica de la columna es de longitud en byte. CHAR indica que la semántica de la columna es de caracteres. VARCHAR2: cadena de caracteres de longitud variable, con una longitud en bytes o caracteres especificada por la cláusula size. El tamaño máximo es 4.000 bytes. Debe especificar el tamaño para VARCHAR2. DATE: rango de fechas válido del 1 de enero de 4712 AC hasta el 31 de diciembre de 9999 DC. También almacena el tiempo: horas, minutos y segundos. NUMBER: número que tiene precisión p y escala s. El rango de precisión es de 1 a 38. El rango de escala es de ‐84 a 127. BINARY_FLOAT: es un número de coma flotante de 32 bits. Este tipo de dato necesita 5 bytes, incluido el byte de longitud. BINARY DOUBLE: es un número de BINARY_coma flotante de 64 bits. Este tipo de dato necesita 9 bytes. FLOAT(p): es un tipo de dato ANSI (American National Standards Institute). El tipo de dato FLOAT es un número de coma flotante con precisión p binaria.La precisión por defecto para este tipo de dato es 126 binaria o 38 decimal. INTEGER: equivale a NUMBER(p,0). NCHAR(length): el tipo de dato NCHAR es sólo Unicode. Cuando se crea una tabla con una columna NCHAR, se define la longitud de la columna en caracteres. El juego de caracteres nacional se define al crear la base de datos. La longitud máxima de una columna viene determinada por la definición del juego de caracteres nacional. Las especificaciones de ancho del tipo de dato NCHAR hacen referencia al número de caracteres. El tamaño máximo de columna permitido es 2.000 bytes. Si se inserta un valor menor que la longitud de columna, la base de datos Oracle rellena en blanco el valor hasta alcanzar la longitud de la columna. No puede insertar un valor CHAR en una columna NCHAR, ni insertar un valor NCHAR en una columna CHAR. NVARCHAR2(size [BYTE|CHAR]): el tipo de dato NVARCHAR2 es sólo Unicode. Es como NCHAR, excepto en que la longitud máxima es 4.000 bytes y que no se rellena en blanco. LONG: es un dato de carácter de longitud variable, hasta 2 GB o 231 ‐1 bytes. El tipo de dato LONG está obsoleto; utilice en su lugar el tipo de dato de objeto grande (LOB). LONG RAW: son datos binarios raw de longitud variable, hasta 2 GB. RAW(size): son datos binarios raw con una longitud en bytes especificada por size. El tamaño máximo es 2.000 bytes. Debe especificar el tamaño para un valor RAW. ROWID: es una cadena de base 64 que representa la dirección única de una fila en su tabla correspondiente. Este tipo de dato es principalmente para valores devueltos por la pseudocolumna ROWID. UROWID: es una cadena de base 64 que representa la dirección lógica de una fila de una tabla organizada por índices. El tamaño opcional es el de una columna de tipo UROWID. El tamaño máximo y por defecto es 4.000 bytes. BLOB: es un objeto grande binario. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 5/29 CLOB: es un objeto grande de caracteres que contiene caracteres de un solo byte o multibyte. Se soportan los juegos de caracteres de ancho fijo y variable y ambos utilizan el juego de caracteres de la base de datos CHAR. NCLOB: es un objeto grande de caracteres que contiene caracteres Unicode. Se soportan los juegos de caracteres de ancho fijo y variable y ambos utilizan el juego de caracteres de la base de datos NCHAR. Almacena datos del juego de caracteres nacional. El tamaño máximo para todos los tipos de dato LOB (BLOB, CLOB y NCLOB) es: (4 GB - 1) * (el valor de CHUNK). CHUNK es un atributo opcional que se puede establecer al definir un LOB. Especifica el número de bytes que se van a asignar para la manipulación de LOB. Si el tamaño no es un múltiplo del tamaño del bloque de base de datos, la base de datos redondea en bytes hasta el siguiente múltiplo. Por ejemplo, si el tamaño del bloque de base de datos es 2.048 y el tamaño de CHUNK es 2.050, la base de datos asigna 4.096 bytes (2 bloques). El valor máximo es 32.768 (32 K), que es el mayor tamaño del bloque de base de datos Oracle permitido. El tamaño de CHUNK por defecto es un bloque de base de datos Oracle. BFILE: el tipo de dato BFILE contiene un localizador a un archivo binario grande almacenado fuera de la base de datos. Permite el acceso de E/S de flujo de bytes a LOB externos que residen en el servidor de bases de datos. El tamaño máximo es 4 GB. TIMESTAMP(fractional_seconds_precision): con este tipo de dato, puede especificar los valores de fecha (año, mes y día), así como los valores de tiempo (hora, minuto y segundo), donde fractional_seconds_precision es el número de dígitos en la parte fraccional de un segundo. Los valores aceptados son 0 y 9. El valor por defecto es 6. Figura 7.5: Especificación de tipos de dato en tablas 7.4.2. Creación de una Tabla Las tablas son las unidades básicas de almacenamiento de datos en una base de datos Oracle. Contienen todos los datos a los que pueden acceder los usuarios. Cada tabla tiene columnas y filas o registros. Los registros de datos son almacenados en bloques de la base de datos, como si fueran archivos de longitud variable. Las columnas de un registro, son almacenadas en el orden en que fueron definidas y las columnas que tienen el valor NULL, no son almacenadas. La estructura de un registro, en una tabla está compuesta por dos partes: Row header y Row data. Row header: es utilizado para almacenar los números de columnas en el registro, los cambios de información y el estado del registro. Row Data: el servidor Oracle almacena, para cada columna, la longitud de la columna y el valor de la misma. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 6/29 Los registros pueden ser almacenados sin espacios intermedios entre ellos. Cada uno de los registros posee una ranura en el directorio de registros, que identifica el comienzo del mismo. Para crear una tabla mediante Enterprise Manager, se realizan los siguientes pasos (Figura 7.6): 1. Hacer clic en Tables en la región Schema de la página Administration. Aparecerá la página Tables. 2. Si se conoce el nombre del esquema, introducirlo completo o en parte en el campo Schema de la región Search. Si no se sabe el nombre del esquema, hacer clic en el icono de linterna situado junto al campo Schema. Aparecerá la ventana Search and Select: Schema. Se puede examinar los nombres de esquema y seleccionar el que esté buscando. 3. Hacer clic en Create. Aparecerá la página Create Table: Table Organization. 4. Aceptar el valor por defecto Standard, Heap Organized haciendo clic en Continue. Aparecerá la página Create Table. 5. Introducir el nombre de la tabla en el campo Name. 6. Introducir el nombre del esquema en el campo Schema o hacer clic en el icono de linterna para llamar a la función de búsqueda. 7. Introducir el nombre del tablespace en el campo Tablespace o hacer clic en el icono de linterna para llamar a la función de búsqueda. 8. En la región Columns, introducir el nombre de columna y los tipos de dato. 9. Hacer clic en OK. Aparecerá un mensaje de actualización que indica que la tabla se ha creado correctamente. Figura 7.6: Creación de una tabla 7.4.3. Modificación de una tabla Es posible modificar una tabla desde el Enterprice Manager, por ejemplo para agregar una columna a la tabla (Figura 7.7): 1. En la página Tables, seleccionar la tabla en la lista de resultados y hacer clic en Botón Editar. 2. En la página Edit Table, hacer clic en el botón Add 5 Table Columns. Aparecerá una lista de columnas editables. 3. Introducir el nombre de columna, el tipo de dato y el tamaño. 4. Haga clic en Apply. Aparecerá un mensaje de actualización que indica que la tabla se ha modificado correctamente. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 7/29 Figura 7.7: Modificación de una tabla 7.4.4. Visualización de las columnas de una tabla Para ver los atributos de una tabla con el Enterprice Manager, se realizan los siguientes pasos (Figura 7.8): 1. Hacer clic en el enlace Tables en la región Schema de la página Database Administration. 2. Seleccionar una tabla de la lista de resultados y hacer clic en el botón View para ver los atributos de la tabla. Figura 7.8: Ver las columnas de una tabla 7.4.5. Visualización del Contenido de una Tabla Para visualizar las filas de una tabla mediante Enterprise Manager, realizar los siguientespasos (Figura 7.9): 1. Seleccione la tabla en la página Tables. 2. Seleccione View Data en el menú Actions y haga clic en Go. Aparecerá la página View Data for Table. Los datos de fila para la tabla se mostrarán en la región Result. El recuadro Query muestra la consulta SQL ejecutada para producir los resultados. En esta página, se puede hacer clic en cualquier nombre de columna y ordenar los datos de la columna en orden ascendente o descendente. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 8/29 Para cambiar la consulta, hacer clic en el botón Refine Query. En la página Refine Query for Table, se pueden seleccionar las columnas que se desean ver y especificar una cláusula WHERE para la sentencia SQL con el fin de limitar los resultados Figura 7.9: Ver el contenido de una tabla 7.4.6. Acciones a realizar sobre una tabla Se puede seleccionar una tabla y, a continuación, realizar acciones en ella. Algunas de esas acciones son las siguientes (Figura 7.10): Create Like: se puede crear una tabla que tenga la misma estructura que la seleccionada. Se deberá cambiar los nombres de las restricciones. También se puede agregar o suprimir columnas y realizar otros cambios en la estructura de la tabla antes de crearla. Create Index: crea índices en una tabla. Generate DDL: se genera el DDL que representa la tabla que ya existe. Grant Privileges: por defecto, cuando se crea una tabla, sólo el propietario puede realizar acciones en ella. El propietario debe otorgar privilegios a los demás usuarios para que puedan ejecutar DML o DDL en la tabla. Show Dependencies: muestra objetos de los que depende esta tabla u objetos que dependen de esta tabla. View Data: selecciona y muestra datos de la tabla en modo de sólo lectura. Figura 7.10: Acciones que se pueden realizar sobre una tabla UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 9/29 7.4.7. Borrado de una tabla El comando DROP TABLE permite borrar datos, la estructura de la tabla y los privilegios de objetos asociados (Figura 7.11): DROP TABLE [schema.] table [CASCADE CONSTRAINTS] [PURGE] El comando DROP TABLE elimina los datos, la estructura de la tabla y los privilegios de objeto asociados. Algunos aspectos acerca de DROP TABLE que se deben tener en cuenta son: Sin la cláusula PURGE, la definición de tabla, los índices asociados y los disparadores se colocan en una papelera de reciclaje. Los datos de la tabla permanecen, pero no resultan accesibles sin la definición de tabla. Si borra una tabla a través de Enterprise Manager, la cláusula PURGE no se utiliza. Utilice el comando FLASHBACK TABLE para recuperar objetos de esquema de la papelera de reciclaje. El comando PURGE RECYCLEBIN vacía la papelera de reciclaje. La opción CASCADE CONSTRAINTS es necesaria para suprimir todas las restricciones de integridad referencial dependientes. Si no utiliza la opción PURGE, el espacio que ocupa la tabla y sus índices siguen contando en la cuota permitida del usuario para los tablespaces implicados. Es decir, todavía se considera que ese espacio se está utilizando Figura 7.11: Borrado de una tabla 7.4.8. Truncar una tabla El truncamiento de una tabla hace que sus datos de fila dejen de estar disponibles y opcionalmente, libera el espacio utilizado. Los índices correspondientes a la tabla se truncan (Figura 7.12). La sintaxis de esta instrucción es la siguiente: TRUNCATE TABLE [schema.] table [(DROP | REUSE) STORAGE] Los efectos de utilizar este comando son los siguientes: La tabla se marca como vacía definiendo el límite superior al principio de la tabla, por lo que sus filas dejan de estar disponibles. No se generan datos de deshacer y el comando se confirma implícitamente porque TRUNCATE TABLE es un comando DDL. Los índices correspondientes también se truncan. No se pueden truncar las tablas a las que una clave externa hace referencia. Los disparadores de supresión no se activan cuando se utiliza este comando. Normalmente este comando es mucho más rápido que emitir una sentencia DELETE para suprimir todas las filas de la tabla, por lo siguiente: UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 10/29 La base de datos Oracle restablece el límite superior de la tabla en lugar de procesar cada fila como una operación DELETE. No se genera ningún dato de deshacer. Figura 7.12: Truncamiento de una tabla 7.5. INTEGRIDAD DE LOS DATOS El objetivo de mantener la integridad de los datos, es hacer cumplir las reglas de negocio de las clases. En la Figura 7.13 se muestra el diagrama lógico de la base de datos Empresa. Se pueden utilizar las siguientes restricciones de integridad para imponer limitaciones en la entrada de valores de columna: NOT NULL: por defecto, todas las columnas de una tabla permiten valores nulos. Nulo significa la ausencia de un valor. Una restricción NOT NULL necesita que una columna de una tabla no contenga ningún valor nulo. Por ejemplo, puede definir una restricción NOT NULL para exigir que se introduzca un valor en la columna LAST_NAME de todas las filas de la tabla EMPLOYEES. Clave UNIQUE: una restricción de integridad de clave UNIQUE necesita que todos los valores de una columna o juego de columnas (clave) sean únicos; es decir, que no haya dos filas de una tabla con valores duplicados en una columna o juego de columnas concreto. Por ejemplo, se define una restricción de clave UNIQUE en la columna DEPARTMENT_NAME de la tabla DEPARTMENTS para no permitir filas con nombres de departamento duplicados. Salvo en casos especiales, esta restricción se aplica con un índice único. PRIMARY KEY: todas las tablas de la base de datos pueden tener como máximo una restricción PRIMARY KEY. Los valores del grupo de una o más columnas sujetas a esta restricción constituyen el identificador único de la fila. En efecto, cada fila se denomina por sus valores de clave primaria. La implementación de la restricción de integridad PRIMARY KEY del servidor de Oracle garantiza que se cumplan las dos condiciones siguientes: No puede haber dos filas de una tabla que tengan valores duplicados en la columna o juego de columnas especificado. Las columnas de clave primaria no permiten valores nulos. Es decir, debe existir un valor para las columnas de clave primaria en cada fila. En circunstancias normales, la base de datos aplica las restricciones PRIMARY KEY utilizando índices. La restricción de clave primaria creada para la columna DEPARTMENT_ID de la tabla DEPARTMENTS se aplica mediante la creación implícita de lo siguiente: Un índice único en dicha columna Una restricción NOT NULL para dicha columna Restricciones de integridad referencial: distintas tablas de una base de datos relacional se pueden relacionar por columnas comunes y se deben mantener las reglas que rigen la relación de las columnas. Las reglas de integridad referencial garantizan que se mantengan estas relaciones. Una restricción de integridad referencial necesita que para cada fila de una tabla, el valor de la clave ajena coincida con un valor de una clave principal. Por ejemplo, se define una clave ajena en la columna DEPARTMENT_ID de la tabla EMPLOYEES. De esta forma se garantiza que todos los valores de esta columna coincidan con un valor de la clave primaria de la UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra:BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 11/29 tabla DEPARTMENTS. Por lo tanto, no pueden existir números de departamento erróneos en la columna DEPARTMENT_ID de la tabla DEPARTMENTS. Otro tipo de restricción de integridad referencial se denomina restricción de integridad de autorreferencia. Este tipo de clave ajena hace referencia a una clave principal de la misma tabla. Restricciones de control: una restricción de integridad CHECK en una columna o juego de columnas necesita que una condición concreta se cumpla o sea desconocida para todas las filas de la tabla. Si una sentencia de lenguaje de manipulación de datos (DML) tiene como resultado que la condición de la restricción CHECK se evalúe como falsa, se realizará un rollback de la sentencia. Figura 7.13: Diagrama lógico de la base de datos Empresa La Figura 7.14 muestra los tipos de restricciones para el mantenimiento de la integridad: Figura 7.14: Tipos de restricciones La figura 7.15 muestra los tipos de restricciones de integridad declaradas: UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 12/29 Figura 7.15: Tipos de restricciones de integridad declaradas 7.5.1. Definición de Restricciones Para agregar una restricción a una tabla con Enterprise Manager, se realizan los siguientes pasos (Figura 7.16): 1. Seleccione la tabla en la página Tables y haga clic en Edit. 2. Haga clic en Constraints. Se mostrará la página Constraints con todas las restricciones definidas en la tabla. 3. Seleccionar el tipo de restricción que desea agregar en la lista desplegable y haga clic en Add. 4. Introducir la información adecuada para el tipo de restricción que está definiendo. Haga clic en OK. Figura 7.16: Definición de una restricción 7.5.2. Violaciones de Restricciones La violación de una restricción tiene lugar cuando se ejecuta DML, y éste no cumple la restricción (Figura 7.17). Las violaciones de las restricciones pueden adoptar muchas formas, por ejemplo: Unicidad: se realiza el intento de tener valores duplicados en una columna que tiene una restricción única, como el caso en que una columna es la clave primaria o está indexada de forma única. Integridad referencial: se viola la regla de que cada fila secundaria tiene una fila principal. Control: se realiza el intento de almacenar un valor en una columna que no sigue las reglas definidas para ella. Por ejemplo, una columna AGE podría tener una restricción de control de modo que al aplicarla sea un número positivo. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 13/29 Figura 7.17: Violaciones de restricción 7.5.3. Estados de una restricción Las restricciones de integridad de datos, tienen la posibilidad de ser activadas o desactivadas. En el caso de que la restricción sea activada, los datos son controlados cuando entran y actualizan la base de datos. Si los datos que entran no están de acuerdo a las reglas de restricción definidas, no se permite la entrada de los mismos. Si las restricciones de integridad se encuentran desactivadas, y en el caso de que los datos no estén acordes a las restricciones de integridad, los mismos pueden ser ingresados igualmente a la base de datos. Los estados que puede tomar una restricción de integridad son (Figura 7.18): ENABLE VALIDATE: los datos nuevos y existentes se ajustan a la restricción. Este es el estado habitual y por defecto de la restricción. ENABLE NOVALIDATE: los datos nuevos se ajustan a la restricción, pero los existentes están en un estado desconocido. Este estado se utiliza con frecuencia para que se puedan corregir las violaciones de restricciones existentes, y al mismo tiempo no se permita que se introduzcan nuevas violaciones en el sistema. DISABLE VALIDATE: si una restricción tiene este estado, no se permite ninguna modificación de las columnas restringidas, porque no sería coherente haber validado los datos existentes y luego permitir que en la tabla se introduzcan datos sin comprobar. Este estado se utiliza con frecuencia cuando los datos existentes se deben validar pero los datos no se van a modificar y el índice ya no es necesario para el rendimiento. DISABLE NOVALIDATE: en este estado puede que tanto los datos nuevos como los ya existentes no se ajusten a la restricción porque no se han comprobado. Este estado se utiliza con frecuencia cuando los datos provienen de un origen ya validado y la tabla es de sólo lectura, de modo que no se introduce ningún dato nuevo en la tabla. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 14/29 Figura 7.18: Estado de una restricción 7.5.4. Comprobación de una restricción Es posible aplazar la validación de restricciones hasta el final de la transacción. La definición de las restricciones como aplazable o no aplazable, determina si la restricción es aplazable por el usuario o no. Restricciones no aplazables o sin diferir (nondeferred): conocidas también como restricciones inmediatas, se aplican al final de las sentencias DML. La violación de una restricción ocasiona el rollback de la sentencia (Figura 7.19). Si una restricción provoca una acción, por ejemplo, delete cascade, ésta se lleva a cabo como parte de la sentencia que la ha ocasionado. Las restricciones definidas como no diferibles no se pueden cambiar a diferibles. Figura 7.19: Restricciones no aplazables Restricciones aplazables (deferred) o diferidas: son aquellas que solo se comprueban cuando se confirma una transacción. Si en el momento de la confirmación (COMMIT) se detecta alguna violación de restricción, se produce el rollback de toda la transacción (Figura 7.20). Estas restricciones son útiles principalmente cuando las filas principal y secundaria de una relación de clave ajena se introducen al mismo tiempo, como en el caso de un sistema de entrada de pedidos en el que el pedido y los artículos del pedido se introducen a la vez. Las restricciones definidas como diferibles se pueden especificar de dos maneras: UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 15/29 Initially immediate: especifica que por defecto, debe funcionar como restricción inmediata a menos que se establezca explícitamente lo contrario. Initially deferred, especifica que por defecto, la restricción sólo se debe aplicar al final de la transacción. Para definir las restricciones como inmediatas o diferidas se pueden utilizar las declaraciones SET CONSTRAINTS o ALTER SESSION. SET CONSTRAINTS determina la restricción como inmediata o diferida para una transacción en particular. ALTER SESSION también determina si las restricciones van a ser inmediatas o diferidas, pero esta sentencia se aplica sólo a las sesiones que se encuentren corriendo en el momento. Esta declaración se aplica a todas las restricciones aplazables. Figura 7.20: Restricciones aplazables 7.5.5. Creación de las restricciones Las restricciones pueden ser definidas cuando se crea o se modifica una tabla. En la Figura 7.21 se muestran tres ejemplos de creación de restricciones que se analizarán a continuación: Figura 7.21: Ejemplos de creación de Restricciones UNIVERSIDADNACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 16/29 a) Una vez que se ejecuta esta sentencia, las inserciones o actualizaciones realizadas en la tabla COUNTRIES tienen que tener un valor COUNTRY_NAME que sea único. Sin embargo, es posible que cuando se emite esta sentencia, ya existan valores COUNTRY_NAME en la tabla que no son únicos. La palabra clave NOVALIDATE indica que se deben ignorar. Sólo se restringen las nuevas filas. b) Esta sentencia agrega una clave primaria a la tabla EMPLOYEE. El nombre de la restricción es PK y la clave primaria es la columna EMPLOYEE_ID. c) Esta sentencia define restricciones en el momento de crearse la tabla en lugar de usar posteriormente una sentencia ALTER TABLE. La restricción RI impone que los valores de la columna FK deben estar presentes en la columna de clave primaria de la tabla T1. La restricción CK1 impone que las columnas PK y C1 sean mayores que cero. Toda restricción tiene un nombre. Si no se proporciona uno en la sentencia DDL, el sistema asigna un nombre, que empieza por SYS_. 7.6. ÍNDICES Frecuentemente, las tablas van adquiriendo grandes cantidades de datos, en estos casos, se torna muy difícil para los usuarios acceder rápidamente a la información que necesitan. Para facilitar el acceso a los datos, y optimizar la performance en cuestiones de velocidad de acceso en las tablas de las bases de datos, Oracle provee la posibilidad de manejar índices. Los índices son estructuras opcionales asociadas a las tablas. Se pueden crear para mejorar el rendimiento de la recuperación y actualización de los datos. Un índice de Oracle proporciona una ruta de acceso directo a una fila de datos (Figura 7.22). Los índices se pueden crear en una o más columnas de una tabla. Una vez creado un índice, el servidor de Oracle lo mantiene y utiliza automáticamente. Las actualizaciones de datos de una tabla, como la adición de nuevas filas, la actualización de filas o la supresión de filas, se propagan automáticamente a todos los índices relevantes con completa transparencia para los usuarios. Figura 7.22: Funcionamiento de un índice 7.6.1. Tipos de índices Oracle provee diversos tipos de índice para diferentes usos y necesidades. Los tipos de índices más comunes son: Árbol B Bitmap Un índice de árbol B tiene almacenados sus valores clave en un árbol equilibrado, lo que permite búsquedas binarias rápidas. Un índice de bitmap tiene un bipmap para cada valor clave distinto indexado. En cada bitmap, hay un juego de bits contiguos para cada fila de la tabla indexada. Esto permite búsquedas rápidas cuando hay pocos valores distintos, es decir, la columna indexada tiene baja cardinalidad. Un ejemplo sería un indicador de sexo. Sólo puede tener los valores de “H” y “M”. De modo que solo se pueden buscar dos bitmaps. Por UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 17/29 ejemplo, si se utilizara un índice de bitmap para una columna número de teléfono, habría que gestionar tantos bitmaps que la búsqueda resultaría muy poco eficaz. 7.6.2. Índices de Árbol B Estructura: al principio del índice está la raíz, que contiene entradas que apuntan al siguiente nivel del índice. En el siguiente nivel están los bloques rama, que su vez apuntan a los bloques del siguiente nivel del índice. En el nivel inferior están los nodos hoja, que contienen las entradas de índice que apuntan a las filas de la tabla. Los bloques hoja están enlazados doblemente para facilitar la búsqueda de los valores clave del índice en orden ascendente y descendente (Figura 7.23). Formato de las entradas de hojas del índice: una entrada de índice está formada por los siguientes componentes: Una cabecera de entrada, que almacena el número de columnas y la información de bloqueo Pares longitud‐valor de columna clave, que definen el tamaño de una columna en la clave seguida por el valor de la columna (el número de estos pares es el número máximo de columnas del índice) ROWID de una fila que contiene los valores clave. Figura 7.23: Estructura de un índice de Árbol B Características de las entradas de hojas: una tabla no particionada tiene las siguientes características: Si hay varias filas con la misma clave los valores clave se repiten a no ser que el índice esté comprimido. No existen entradas de índice correspondientes a una fila cuyas columnas de clave tengan todas el valor NULL. Por lo tanto, una cláusula WHERE que especifique NULL siempre dará lugar a una exploración de tabla completa. El ROWID restringido se utiliza para apuntar a las filas de la tabla, dado que todas las filas pertenecen al mismo segmento. Efecto de las operaciones DML en índices: el servidor de Oracle mantiene todos los índices cuando las operaciones DML se realizan en la tabla. A continuación se muestra una explicación del efecto de un comando DML en un índice: Las operaciones de inserción dan como resultado la inserción de una entrada de índice en el bloque adecuado. La supresión de una fila produce una supresión lógica de la entrada de índice. El espacio que utiliza la fila suprimida no estará disponible para las nuevas entradas hasta que todas las entradas del bloque se supriman. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 18/29 Las actualizaciones de las columnas clave dan como resultado una supresión lógica y una inserción en el índice. El valor PCTFREE no tiene efecto alguno en el índice excepto en el momento de la creación. Se puede agregar una nueva entrada en el bloque de índice incluso si cuenta con menos espacio que el especificado en PCTFREE. 7.6.3. Índices de Bitmap Los índices de bitmap ofrecen más ventajas que los índices de árbol B en determinadas situaciones: Cuando una tabla tiene millones de filas y las columnas de clave poseen baja cardinalidad, es decir, tienen pocos valores distintos. Por ejemplo, los índices de bitmap pueden ser preferibles a los de árbol B para las columnas para identificar el sexo y estado civil de una tabla que contenga registros de personas. Cuando es frecuente que las consultas utilicen una combinación de varias condiciones WHERE relacionadas con el operador OR. Cuando hay actividad de sólo lectura o de actualización baja en las columnas de clave. Estructura de un índice de bitmap El índice de bitmap también está organizado como un árbol B, pero el nodo hoja almacena un bitmap para cada valor clave en lugar de una lista de ROWID. Cada bit del bitmap corresponde a un posible ROWID, y si este bit está definido, significa que la fila con el ROWID correspondiente contiene el valor clave. Figura 7.24: Estructura de un índice de bitmap En la Figura 7.24 se muestra el nodo hoja de un índice de bitmap, que contiene lo siguiente: Una cabecera de entrada, que contiene el número de columnas y la información de bloqueo. Los valores clave constan de pares de longitud y valor para cada columna de clave. En el ejemplo la clave ejemplo, está formada por una columna, y la primera entrada tiene un valor clave de Blue. ROWID inicial, que en el ejemplo especifica el número de bloque diez, el número de fila cero y el número de archivo tres. ROWID final, que en el ejemplo especifica el número de bloque doce, el número de fila ocho y el número de archivo tres. Un segmento de bitmap consta de una cadena de bits. (El bit se define cuando la filacorrespondiente contiene el valor clave y se anula su definición cuando no lo contiene. El servidor de Oracle usa una técnica de compresión patentada para almacenar segmentos de bitmap.) UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 19/29 El ROWID inicial es el ROWID de la primera fila a la que apunta el segmento de bitmap del bitmap, es decir, el primer bit del bitmap corresponde a ese ROWID, el segundo a la siguiente fila del bloque y el ROWID final es un puntero a la última fila de la tabla incluida en el segmento de bitmap. Los índices de bitmap son ROWID restringidos. Uso de un índice de bitmap El árbol B se utiliza para localizar los nodos hoja que contienen segmentos de bitmap para un valor dado de la clave. El ROWID inicial y los segmentos de bitmap se utilizan para localizar las filas que contienen el valor clave. Cuando se realizan cambios en la columna de clave de la tabla, se deben modificar los bitmaps. El resultado es que se bloquean los segmentos de bitmap relevantes. Como los bloqueos se adquieren en todo el segmento de bitmap, otras transacciones no pueden actualizar una fila incluida en el bitmap hasta que termine la primera transacción. 7.6.4. Comparación entre los tipos de índices B‐Tree y Mapa de Bits En este punto se realiza una breve comparación, entre las características más importantes de estos dos tipos de índices utilizados por Oracle. Esta comparación, sirve para comprender, un poco mejor, las posibilidades que brinda uno u otro (Figura 7.25): Los índices de mapas de bits son más compactos que los B‐Tree, cuando utilizan columnas de baja cardinalidad. Las actualizaciones de las columnas clave, en los índices de mapa de bits, es más costosa, porque los mapas de bits utilizan un cierre a nivel de segmento de mapa de bits, mientras que en los índices B‐Tree, los cierres se encuentran sobre las entradas correspondientes a registros individuales dentro de la tabla. Los índices de mapa de bits pueden ser usados para realizar operaciones del tipo mapa de bits booleano. El servidor Oracle puede usar dos segmentos de mapas de bits para realizar una operación booleana y obtener como resultado un mapa de bits. Esto permite un muy eficiente resultado ante la necesidad de realizar consultas con predicado booleano. Los índices B‐Tree son más convenientes en un entorno OLTP, para ponerle índices a tablas dinámicas, mientras que los índices de mapas de bits, pueden ser útiles al indexar entornos de almacenamiento de datos, donde consultas complejas son realizadas a grandes tablas estáticas (Datawarehouse). Figura 7.25: Comparación entre índices Árbol B y Bitmap UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 20/29 7.6.5. Clasificación de los índices Los diferentes tipos de índices se pueden clasificar en lógicos y físicos, de acuerdo a su diseño e implementación. Índices Lógicos: actúan generando índices en una perspectiva de aplicación. Índices de Columna simple o concatenada: un índice de columna simple tiene una sola columna como clave del índice. Por el contrario, un índice concatenado, o también llamado compuesto, es creado sobre múltiples columnas de una tabla. Índices únicos o no únicos: los índices únicos garantizan que dos registros en una tabla, no posean valores duplicados en la columna, o columnas clave. Los no únicos, no imponen esta restricción, en los valores de las columnas. Índices basados en funciones: estos son creados cuando se utilizan funciones o expresiones que involucran una o muchas columnas en la tabla que debe ser indexada. Un índice basado en funciones, precalcula el valor de la función o expresión, y lo almacena en el índice. Índices de Dominio: es un índice de aplicación específico, permite a los fabricantes de software desarrollar los llamados cartuchos. Permiten funcionalidad para dominios de aplicación específicos: texto, datos espaciales, e imágenes. La funcionalidad de indexado va mas allá de la proporcionada por los tipos de índice Oracle estándar. Índices Físicos: se basan en cómo se indexa la información almacenada. Índices Particionados o no particionados: los índices particionados son utilizados en grandes tablas, para almacenar las entradas de nuevos índices, correspondientes a un índice en varios segmentos. La partición permite que un índice sea extendido a través de muchos tablespaces, disminuyendo la búsqueda para la consulta de índices, y aumentando la posibilidad de gestión. Los índices particionados, se utilizan para tablas particionadas. Puede crearse un índice particionado por cada partición en una tabla. Índices B‐Tree: este es el mecanismo de indexación tradicional de Oracle. En este tipo de índice se consideran dos situaciones: Índices de clave reversa (RKI): cabe aclarar que éstos son iguales que los normales, excepto en que los datos de la columna que está indexada son almacenados en orden inverso. Son útiles cuando son construidos sobre una columna que contiene números secuenciales. Si se construye un índice tradicional B‐Tree sobre una columna que tiene estos tipos de datos, el índice tiende a generar muchos niveles. Es sabido que si supera los cuatro niveles de profundidad, el rendimiento decaerá. Por ello un RKI es más recomendable para estas situaciones. Índices comprimidos: cuando un índice B‐Tree es construido sobre una tabla muy grande, particularmente en un sistema de datos históricos ó DataWarehouse ó un sistema de soporte a decisiones (Data Decision Support), estos índices pueden consumir una gran cantidad de espacio de almacenamiento. En los índices B‐Tree comprimidos las ocurrencias duplicadas del índice son eliminadas reduciendo el espacio requerido para su almacenamiento. Índices de Mapas de Bits: aunque todos los índices en Oracle son almacenados con la estructura de árbol, los índices de mapas de bits son conceptualmente diferentes. En vez de almacenar entradas para cada registro en la tabla, el índice de mapa de bits almacena una entrada que contiene cada valor distinto, el valor inicial y final del ROWID para indicar la variedad de ROWIDs en la tabla. 7.6.6. Creación de índices Para crear un índice se debe ir a la pagina Administration. Debajo de la cabecera Schema, hacer clic en el enlace Indexes. Se pueden ver atributos de índice o utilizar el menú Actions para ver dependencias de un índice (Figura 7.26). Los índices se pueden crear explícita o implícitamente mediante restricciones que se colocan en una tabla. Un ejemplo de un índice creado implícitamente es la definición de una clave primaria, en cuyo caso se crearía automáticamente un índice único para reforzar la unicidad en la columna. UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 21/29 Figura 7.26: Creación de índices 7.7. VISTAS Las vistas son representaciones personalizadas de datos de una o más tablas u otras vistas. Se pueden considerar como consultas almacenadas dado que pueden ocultar condiciones muy complejas, uniones y otras expresiones y construcciones SQL complejas (Figura 7.27). Las vistas en realidad no contienen datos, sino que derivan sus datos de las tablas en las que se basan. Estas tablas se denominan tablas base de la vista. La definición de la vista se almacena en el Diccionario de Datos, el resultadode la vista se almacena en memoria, pudiendo también almacenarse en disco (vistas materializadas). Figura 7.27: Armado y definición de una vista 7.7.1. Creación de vistas Al igual que con las tablas, se pueden realizar consultas, actualizaciones, inserciones y supresiones en las vistas, pero con algunas restricciones. Todas las operaciones realizadas en una vista en realidad afectan a las tablas base de la vista. Las vistas proporcionan un nivel adicional de seguridad al restringir el acceso a un juego predeterminado de filas y columnas de una tabla. También pueden ocultar la complejidad de los datos y almacenar consultas complejas. Para crear una vista, hacer clic en el enlace Create View situado bajo la cabecera Schema de la página Administration. Para ver la definición de una vistas hacer clic en el botón show SQL (Figura 7.28). UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 22/29 Figura 7.28: Creación de una vista 7.8. SECUENCIAS Una secuencia es un mecanismo para generar automáticamente enteros que siguen un patrón. Sus características son las siguientes (Figura 7.29): Tiene un nombre para poder referenciarla cuando se solicita el siguiente valor. No está asociada a ninguna tabla o columna en especial. La progresión puede ser ascendente o descendente. El intervalo entre números puede ser de cualquier tamaño. Puede crear un ciclo cuando se alcanza un límite. El almacenamiento en caché de los números de secuencia mejora el rendimiento ya que se asigna previamente un juego de números en la memoria para un acceso más rápido. En caso de fallo de la instancia, los números de secuencia almacenados en caché no se utilizan, lo que da lugar a intervalos vacíos. Figura 7.29: Características de las Secuencias 7.8.1. Creación de una Secuencia Puede ver y crear secuencias con Enterprise Manager haciendo clic en el enlace Sequences situado bajo la cabecera Schema de la página Administration (Figura 7.30). Éste es un resumen de las opciones de creación de secuencias: UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 23/29 Name: Es el nombre de la secuencia, con el que se hace referencia a ella. Schema: Es el propietario de la secuencia. Maximum Value: Especifique el valor máximo que puede generar la secuencia. Este valor entero puede tener 28 dígitos o menos. Debe ser mayor que el valor de Minimum Value y de Initial. Unlimited indica un valor máximo de 1027 para una secuencia ascendente, o de ‐1 para una secuencia descendente. El valor por defecto es Unlimited. Minimum Value: Especifique el valor mínimo de la secuencia. Este valor entero puede tener 28 dígitos o menos. Debe ser menor o igual que el valor de Initial y menor que Maximum Value. Unlimited indica un valor mínimo de 1 para una secuencia ascendente, o de ‐1026 para una secuencia descendente. El valor por defecto es Unlimited. Interval: Especifique el intervalo entre números de secuencia. Este valor entero puede ser cualquier entero positivo o negativo, pero no puede ser cero. Puede tener 28 dígitos o menos. El valor por defecto es uno. Initial: Especifique el primer número de secuencia que se va a generar. Utilice esta cláusula para iniciar una secuencia ascendente en un valor mayor que su mínimo o para iniciar una secuencia descendente en un valor menor que su máximo. Cycle Values: Después de que una secuencia ascendente alcance su valor máximo, genera su valor mínimo. Después de que una secuencia descendente alcance su mínimo, genera su valor máximo. Si no selecciona esta opción, se devolverá un error si intenta recuperar un valor después de que la secuencia se haya agotado. Order Values: Garantiza que los números de secuencia se generan en orden de solicitud. Esta cláusula es útil si utiliza números de secuencia como registros de hora. Garantizar el orden no suele ser importante con las secuencias utilizadas para generar claves primarias. Esta opción es necesaria sólo para garantizar la generación ordenada si utiliza la base de datos Oracle con Real Application Clusters. Cache Options: Especifique cuántos valores de la secuencia preasigna y mantiene la base de datos Oracle en la memoria para un acceso más rápido. Este valor entero puede tener 28 dígitos o menos. El valor mínimo para este parámetro es 2. Para secuencias en ciclos, este valor debe ser inferior al número de valores del ciclo. No puede almacenar en caché más valores de los que se ajustarán en un ciclo determinado de números de secuencia Figura 7.30: Creación de una Secuencia UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 24/29 7.8.2. Utilización de una Secuencia Se hace referencia a valores de secuencia en sentencias SQL con las siguientes pseudocolumnas (Figura 7.31): CURRVAL: devuelve el valor actual de una secuencia. NEXTVAL: incrementa la secuencia y devuelve el siguiente valor. Se deben cualificar a CURRVAL y NEXTVAL con el nombre de la secuencia: sequence.CURRVAL sequence.NEXTVAL La primera referencia a NEXTVAL devuelve el valor inicial de la secuencia. Las referencias posteriores a NEXTVAL aumentan el valor de secuencia con el incremento definido y devuelven el nuevo valor. Cualquier referencia a CURRVAL siempre devuelve el valor actual de la secuencia, que es el valor devuelto por la última referencia a NEXTVAL. Figura 7.31: Utilización de una Secuencia 7.9. TABLAS TEMPORALES Las tablas temporales e utilizan cuando se necesita almacenar datos de forma privada con el fin de realizar una tarea y se requiere que los datos se limpien una vez terminada ésta, al final de una transacción o sesión. Las tablas temporales ofrecen esta funcionalidad pero además le liberan de las responsabilidades de ocultar los datos de otras sesiones y eliminan los datos generados cuando ha terminado. Los únicos datos de la tabla temporal visibles para una sesión son los datos que ha insertado la sesión (Figura 7.32). Una tabla temporal puede ser específica de una transacción o de una sesión. En el caso de las tablas temporales específicas de una transacción, los datos se conservan lo que dura la transacción, y en el segundo caso lo que dura la sesión. En ambos casos, los datos que inserta una sesión son privados para la sesión. Cada sesión sólo puede ver y modificar sus propios datos. Figura 7.32: Utilización de una Secuencia UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 25/29 Como resultado, nunca se adquieren bloqueos DML en los datos de las tablas temporales. Las siguientes cláusulas controlan la duración de las filas: ON COMMIT DELETE ROWS: para especificar que la duración de las filas insertadas equivale a lo que dura la transacción. ON COMMIT PRESERVE ROWS: para especificar que la duración de las filas insertadas equivale a lo que dura la sesión. La sentencia CREATE GLOBAL TEMPORARY TABLE crea una tabla temporal. Puede crear índices, vistas y disparadores en tablas temporales, y también puede utilizar Export e Import o Data Pump para exportar e importar la definición de una tabla temporal. No obstante, no se exportará ningún dato aunque utilice la opción ROWS. Además de los casos ya mencionados que provocan la supresión de los datos, puede forzar la eliminación de éstosde manera eficaz con el comando TRUNCATE TABLE. Este comando elimina todos los datos insertados, y resulta más eficaz que utilizar el comando DELETE. Se pueden crear índices, vistas y disparadores en tablas temporales. Las tablas temporales se pueden crear mediante Enterprise Manager haciendo clic en la opción Temporary en la página Create Table: Table Organization. Haga clic en Continue. La siguiente página le permite especificar si la tabla temporal es específica de la sesión o de la transacción. El campo Tablespace está desactivado dado que una tabla temporal siempre se crea en el tablespace temporal del usuario; no se puede especificar ningún otro tablespace. 7.9.1. Consideraciones La sentencia CREATE GLOBAL TEMPORARY TABLE crea una tabla temporal. Se pueden crear índices, vistas y disparadores en tablas temporales, y también puede utilizar Export e Import o Data Pump para exportar e importar la definición de una tabla temporal. No obstante, no se exportará ningún dato aunque utilice la opción ROWS (Figura 7.33). Además de los casos ya mencionados que provocan la supresión de los datos, puede forzar la eliminación de éstos de manera eficaz con el comando TRUNCATE TABLE. Este comando elimina todos los datos insertados, y resulta más eficaz que utilizar el comando DELETE. La palabra clave GLOBAL se basa en la terminología especificada en el estándar ISO (Organización Internacional de Normalización) para SQL. Figura 7.33: Tablas Temporales: Consideraciones UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 26/29 7.10. DICCIONARIO DE DATOS El diccionario de datos de Oracle es la descripción de una base de datos. Contiene los nombres y atributos de todos los objetos de la base de datos (Figura 7.34). La creación o modificación de un objeto provoca la actualización del diccionario de datos para reflejar dichos cambios. Esta información se almacena en las tablas base mantenidas por la base de datos Oracle, pero puede acceder a estas tablas mediante vistas predefinidas en lugar de leer las tablas directamente. El diccionario de datos presenta las siguientes características: Lo utiliza el servidor de base de datos Oracle para encontrar información acerca de usuarios, objetos, restricciones y almacenamiento. Lo mantiene el servidor de base de datos Oracle a medida que las estructuras o definiciones de objetos se modifican. Lo puede utilizar cualquier usuario para consultar información acerca de la base de datos. Es propiedad del usuario SYS. No se debe modificar directamente mediante SQL. La vista del diccionario de datos DICTIONARY, o su sinónimo DICT, contiene los nombres y las descripciones de todo lo que hay en el diccionario de datos. La vista DICT_COLUMNS se utiliza para ver las columnas de vista y sus definiciones. Básicamente la información que brinda el diccionario de Datos, es la siguiente: La definición de todos los objetos de la base de datos. El espacio asignado y/o utilizado por los objetos de la base de datos. Los valores por defecto de las columnas. Información sobre la integridad de datos. Los nombres de usuarios. Autorizaciones y roles asignados a cada uno de los usuarios. Información útil para auditorias, como por ejemplo accesos de usuarios, etc. Figura 7.34: Diccionario de Datos: Visión General 7.10.1. Categorías de las vistas del Diccionario de Datos Los prefijos de la vista indican qué o cuántos datos puede ver un usuario determinado (Figura 7.35). La Tabla 7.1 muestra una clasificación de las vistas y sus características UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 27/29 A la vista global de todo sólo acceden los usuarios con privilegios DBA, mediante el prefijo DBA_. El siguiente nivel de privilegios está en el nivel de prefijo ALL_, que representa todos los objetos para los que el usuario que consulta tiene privilegios de vista, tanto si es o no su propietario. Por ejemplo, si USER_A tiene permiso para acceder a una tabla propiedad de USER_B, USER_A ve esa tabla en todas las vistas ALL_ relacionadas con nombres de tabla. El prefijo USER_ representa el ámbito de visibilidad más pequeño. Sólo se muestran aquellos objetos cuyo propietario es el usuario que consulta, es decir, los que están presentes en su propio esquema. Figura 7.35: Categorías de vistas del Diccionario de Datos Tabla 7.1: Clasificación de las Vistas y sus características 7.10.2. Vistas más utilizadas del Diccionario de Datos Las vistas más comúnmente utilizadas son: DICTIONARY DICT_COLUMNS DBA_TABLES DBA_INDEXES UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 28/29 DBA_TAB_COLUMNS DBA_CONTRAINTS DBA_SEGMENTS DBA_EXENTS DBA_TABLESPACES DBA_DATA_FILES Las mismas se clasifican según la información que poseen, en vistas de Descripción General, de Objetos, de Asignación de Espacios y de Estructura de bases de datos. Las de Descripción General son: DICTIONARY y DICT_COLUMNS Las de Objetos: DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS y DBA_CONSTRAINS Las de Asignación de Espacios: DBA_SEGMENTS y DBA_EXTENTS Y las de Estructura de BD: DBA_TABLESPACES y DBA_DATA_FILES 7.10.3. Ejemplos de uso de las vistas estáticas del Diccionario de Datos En los ejemplos de la Figura 7.36 se muestran consultas que responden a estas preguntas: a) ¿Cuáles son los nombres de las tablas (junto con el nombre del tablespace donde residen) que se han creado en el esquema?. b) ¿Cuál es la información importante acerca de las secuencias de la base de datos a la que ha accedido?. c) ¿Qué usuarios de esta base de datos se pueden conectar actualmente?. d) ¿Cuáles son las columnas de la vista DBA_INDEXES? Esto muestra la información que se puede ver acerca de todos los índices de la base de datos. A continuación se muestra una salida parcial de este comando: SQL> DESCRIBE dba_indexes; Name Null? Type ---------------- --------- ------------- OWNER NOT NULL VARCHAR2(30) INDEX_NAME NOT NULL VARCHAR2(30) INDEX_TYPE VARCHAR2(27) TABLE OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) Figura 7.36: Ejemplos de consultas al Diccionario de Datos UNIVERSIDAD NACIONAL DE JUJUY FACULTAD DE INGENIERIA ANALISTA PROGRAMADOR UNIVERSITARIO Cátedra: BASE DE DATOS II Profesor Adjunto: Ms. Ing. Héctor P. Liberatori 29/29 7.10.4. Descripción de las vistas de Perfomance Dinámicas Estas vistas son una serie de tablas en las que el servidor Oracle registra toda la actividad de la base de datos. Estas tablas existen en memoria y perduran solamente cuando la base de datos está corriendo, esto refleja en tiempo real las condiciones en las que están ejecutando las operaciones de la base de datos (Figura 7.37). Las tablas de vistas de Performance Dinámicas no tienen que ser accedidas por la mayor parte de los usuarios, sin embargo, los responsables de la administración de la base de datos tienen la posibilidad de conceder derechos de consulta y hasta crear vistas de las mismas. Las vistas de Performance Dinámicas no pueden ser modificadas ni eliminadas por el administrador de bases de datos. Los nombres de las tablas de Performance Dinámicas comienzan con V$. Las vistas se crean sobre estas tablas y se las nombra con V$ al inicio. Algunas vistas de Performance Dinámicas son: V$DATABASE,contiene información general sobre la base de datos montada en una instancia. V$SYSSTAT, almacena más información sobre la Performance de la base de datos. V$SESSION y V$SESSTAT, brinda más información acerca de la Performance por cada sesión individual que se encuentre iniciada. V$LOG, V$LOGFILE, tienen información sobre los redo log. V$DATAFILE, guarda información sobre los datafiles de una base de datos Oracle. V$CONTROLFILE, tiene la información acerca de los controlfiles de la base de datos. Figura 7.37: Vistas de Perfomance Dinámicas
Compartir