Base de Datos Oracle 10g:
Conceptos Fundamentales
de SQL II
Guía del Alumno • Volumen 1
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Prefacio
I Introducción Objetivos I-2
Objetivos del Curso I-3 Visión General del Curso I-4 Aplicación del Curso 1-5 Resumen I-6
1 Control de Acceso de Usuarios Objetivos 1-2
Control de Acceso de Usuarios 1-3 Privilegios 1-4
Privilegios del Sistema 1-5 Creación de Usuarios 1-6
Privilegios del Sistema de Usuario 1-7 Otorgamiento de Privilegios del Sistema 1-8 ¿Qué es un Rol? 1-9
Creación y Otorgamiento de Privilegios a un Rol 1-10 Cambio de Contraseñas 1-11
Privilegios de Objeto 1-12
Otorgamiento de Privilegios de Objeto 1-14 Transferencia de Privilegios 1-15
Confirmación de Privilegios Otorgados 1-16 Revocación de Privilegios de Objeto 1-17 Resumen 1-19
Práctica 1: Visión General 1-20 2 Gestión de Objetos de Esquema
Objetivos 2-2
Sentencia ALTER TABLE 2-3 Adición de una Columna 2-5 Modificación de una Columna 2-6 Borrado de una Columna 2-7 Opción SET UNUSED 2-8
Adición de una Sintaxis de Restricción 2-10 Adición de una Restricción 2-11
ON DELETE CASCADE 2-12 Diferir Restricciones 2-13 Borrado de una Restricción 2-14
Contenido
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Activación de Restricciones 2-16 Restricciones en Cascada 2-18 Visión General de Índices 2-20
CREATE INDEX con Sentencia CREATE TABLE 2-21 Índices Basados en Funciones 2-23
Eliminación de un Índice 2-24 DROP TABLE ...PURGE 2-25 Sentencia FLASHBACK TABLE 2-26 Tablas Externas 2-28
Creacisn de un Directorio para la Tabla Externa 2-30 Creación de una Tabla Externa 2-32
Creación de una Tabla Externa mediante ORACLE_LOADER 2-34 Consulta de Tablas Externas 2-36
Resumen 2-37
Práctica 2: Visión General 2-38
3 Manipulación de Grandes Juegos de Datos Objetivos 3-2
Uso de Subconsultas para Manipular Datos 3-3 Copia de Filas de Otra Tabla 3-4
Inserción mediante una Subconsulta como Destino 3-5
Recuperación de Datos con una Subconsulta como Origen 3-7 Actualización de Dos Columnas con una Subconsulta 3-8 Actualización de Filas Basándose en Otra Tabla 3-9 Supresión de Filas Basándose en Otra Tabla 3-10
Uso de las Palabras Clave WITH CHECK OPTION en Sentencias DML 3-11 Visión General de la Función de Valor por Defecto Explícito 3-12
Uso de Valores Por Defecto Explícitos 3-13
Visión General de Sentencias INSERT de Varias Tablas 3-14 Tipos de Sentencias INSERT de Varias Tablas 3-16
Sentencias INSERT de Varias Tablas 3-17 INSERT ALL Incondicional 3-19
INSERT ALL Condicional 3-20 INSERT FIRST Condicional 3-22 INSERT de Pivoting 3-24
Sentencia MERGE 3-27
Sintaxis de la Sentencia MERGE 3-28 Fusión de Filas 3-29
Seguimiento de Cambios en los Datos 3-31
Ejemplo de Consulta de Versiones de Flashback 3-32 Cláusula VERSIONS BETWEEN 3-34
Resumen 3-35
Práctica 3: Visión General 3-36
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
4 Generación de Informes mediante la Agrupación de Datos Relacionados Objetivos 4-2
Revisión de Funciones de Grupo 4-3 Revisión de la Cláusula GROUP BY 4-4 Revisión de la Cláusula HAVING 4-5
GROUP BY con los Operadores ROLLUP y CUBE 4-6 Operador ROLLUP 4-7
Operador ROLLUP: Ejemplo 4-8 Operador CUBE 4-9
Operador CUBE: Ejemplo 4-10 Función GROUPING 4-11
Función GROUPING: Ejemplo 4-12 GROUPING SETS 4-13
GROUPING SETS: Ejemplo 4-15 Columnas Compuestas 4-17
Columnas Compuestas: Ejemplo 4-19 Agrupamientos Concatenados 4-21
Agrupamientos Concatenados: Ejemplo 4-22 Resumen 4-23
Práctica 4: Visión General 4-24
5 Gestión de Datos en Zonas Horarias Diferentes Objetivos 5-2
Zonas Horarias 5-3
Parámetro de Sesión TIME_ZONE 5-4
CURRENT_DATE, CURRENT_TIMESTAMP y LOCALTIMESTAMP 5-5 CURRENT_DATE 5-6
CURRENT_TIMESTAMP 5-7 LOCALTIMESTAMP 5-8
DBTIMEZONE y SESSIONTIMEZONE 5-9 Tipo de Datos TIMESTAMP 5-10
Tipos de Datos TIMESTAMP 5-11 Campos TIMESTAMP 5-12
Diferencia entre DATE y TIMESTAMP 5-13
Tipo de Datos TIMESTAMP WITH TIME ZONE 5-14 TIMESTAMP WITH TIMEZONE: Ejemplo 5-15 TIMESTAMP WITH LOCAL TIMEZONE 5-16
TIMESTAMP WITH LOCAL TIMEZONE: Ejemplo 5-17 Tipos de Datos INTERVAL 5-18
Campos INTERVAL 5-20
Tipo de Datos INTERVAL YEAR TO MONTH 5-21 INTERVAL YEAR TO MONTH: Ejemplo 5-22
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Tipo de Datos INTERVAL DAY TO SECOND: Ejemplo 5-24 EXTRACT 5-25
TZ_OFFSET 5-26
Conversión de TIMESTAMP mediante FROM_TZ 5-28
Conversión a TIMESTAMP mediante TO_TIMESTAMP y TO_TIMESTAMP_TZ 5-29 Conversión de Intervalo de Tiempo con TO_YMINTERVAL 5-30
Uso de TO_DSINTERVAL: Ejemplo 5-31 Horario de Verano 5-32
Resumen 5-34
Práctica 5: Visión General 5-35
6 Recuperación de Datos mediante Subconsultas Objetivos 6-2
Subconsultas de Varias Columnas 6-3 Comparaciones de Columnas 6-4
Subconsulta de Comparación entre Pares 6-5 Subconsulta de Comparación entre No Pares 6-6 Expresiones de Subconsultas Escalares 6-7 Subconsultas Escalares: Ejemplos 6-8 Subconsultas Correlacionadas 6-10
Uso de Subconsultas Correlacionadas 6-12 Uso del Operador EXISTS 6-14
Búsqueda de Empleados que Tengan al Menos una Persona a sus Órdenes 6-15 Búsqueda de Todos los Departamentos que No Tengan Empleados 6-16
Consulta Correlacionada: UPDATE 6-17
Uso de Consultas Correlacionadas: UPDATE 6-18 Consulta Correlacionada: DELETE 6-20
Uso de Consultas Correlacionadas: DELETE 6-21 Cláusula WITH 6-22
Cláusula WITH: Ejemplo 6-23 Resumen 6-25
Práctica 6: Visión General 6-27 7 Recuperación Jerárquica
Objetivos 7-2
Ejemplo de Datos de la Tabla EMPLOYEES 7-3 Estructura de Árbol Natural 7-4
Consultas Jerárquicas 7-5 Desplazamiento por el Árbol 7-6
Desplazamiento por el Árbol: De Abajo Arriba 7-8 Desplazamiento por el Árbol: De Arriba Abajo 7-9
Clasificación de Filas con la Pseudocolumna LEVEL 7-10 Formato de Informes Jerárquicos mediante LEVEL y LPAD 7-11 Eliminación de Ramas 7-13
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Resumen 7-14
Práctica 7: Visión General 7-15 8 Soporte de Expresiones Normales
Objetivos 8-2
Visión General de Expresiones Normales 8-3 Metacaracteres 8-4
Uso de Metacaracteres 8-5
Funciones de Expresiones Normales 8-7 Sintaxis de la Función REGEXP 8-8 Realización de Búsquedas Básicas 8-9
Comprobación de la Presencia de un Patrón 8-10 Ejemplo de Extracción de Subcadenas 8-11 Sustitución de Patrones 8-12
Expresiones Normales y Restricciones de Control 8-13 Resumen 8-14
Práctica 8: Visión General 8-15 Apéndice A: Soluciones a la Práctica Apéndice B: Descripciones de Tabla
Apéndice C: Escritura de Archivos de Comandos Avanzados Objetivos C-2
Uso de SQL para Generar SQL C-3
Creación de un Archivo de Comandos Básico C-4 Control del Entorno C-5
La Imagen Completa C-6
Volcado del Contenido de una Tabla en un Archivo C-7 Generación de un Predicado Dinámico C-9
Resumen C-11
Apéndice D: Componentes de la Arquitectura Oracle Objetivos D-2
Arquitectura de la Base de Datos Oracle: Visión General D-3 Arquitectura Física de la Base de Datos D-4
Archivos de Control D-5 Archivos Redo Log D-6
Tablespaces y Archivos de Datos D-7 Segmentos, Extensiones y Bloques D-8 Gestión de Instancias Oracle D-9
Estructuras de Memoria Oracle D-10 Procesos Oracle D-12
Otras Estructuras Físicas Clave D-13
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Conexión a una Instancia D-15 Procesamiento de una Consulta D-17 Pool Compartido D-18
Caché de Buffers de Base de Datos D-20 PGA (Área Global de Programas) D-21 Procesamiento de una Sentencia DML D-22 Buffer de Redo Log D-24
Segmento de Rollback D-25 Procesamiento COMMIT D-26 Resumen D-28
Índice
Prácticas Adicionales
Soluciones a las Prácticas Adicionales
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Prefacio
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Perfil
Antes de Empezar Este Curso
• Antes de empezar el curso, debe tener experiencia práctica con SQL. Requisitos
• Base de Datos Oracle 10g: Conceptos Fundamentales de SQL I
Organización de Este Curso
Base de Datos Oracle 10g: Conceptos Fundamentales de SQL II es un curso dirigido por un instructor
que ofrece clases teóricas y ejercicios prácticos. Las demostraciones en línea y las sesiones de prácticas escritas refuerzan los conceptos y los conocimientos introducidos.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Publicaciones Adicionales
• Boletines de versión de sistema • Guías de usuario y de instalación • Archivos read-me
• Artículos de IOUG (International Oracle User’s Group) • Oracle Magazine
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Convenciones Tipográficas
Convenciones Tipográficas del Texto
Convención Elemento Ejemplo Negrita Frases y palabras enfatizadas
sólo en el contenido Web
Para navegar dentro de esta aplicación, no haga clic en los botones Back y Forward. Cursiva
negrita
Términos de glosario (si hay algún glosario)
El algoritmo inserta la clave nueva.
Corchetes Nombres de tecla Pulse [Enter]. Mayúsculas
y minúsculas Botones, casillas de control, disparadores, ventanas
Haga clic en el botón Executable. Active la casilla de control Registration Required.
Asigne un disparador When-Validate-Item. Abra la ventana Master Schedule.
Corchetes angulares
Rutas de acceso de menú Seleccione File > Save.
Comas Secuencias de teclas Pulse y suelte de una en una las siguientes teclas: [Alt], [F], [D]
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Convenciones Tipográficas del Texto (continuación) Convención Objeto o Término Ejemplo
Courier New, sensible a mayúsculas/m inúsculas Salida de código, elementos de código SQL y PL/SQL, elementos de código Java, nombres de directorio, nombres de archivo, contraseñas, nombres de ruta de acceso, direcciones URL, datos introducidos por el usuario, nombres de usuario
Salida de código: debug.seti (‘I’,300);
Elementos de código SQL: Utilice el comando SELECT para visualizar la información almacenada en la columna last_name de la tabla emp.
Elementos de código Java: La programación Java afecta a las clases String y StringBuffer.
Nombres de directorio: bin (DOS), $FMHOME (UNIX) Nombres de archivo: Busque el archivo init.ora. Contraseñas: Utilice la contraseña tiger.
Nombres de ruta de acceso: Abra c:\my_docs\projects.
Direcciones URL: Vaya a http://www.oracle.com. Datos introducidos por el usuario: Introduzca 300.
Nombres de usuario: Conéctese como scott. Mayúsculas iniciales Etiquetas de gráficos (siempre que el término no sea un nombre propio)
Dirección de cliente (excepto Oracle Payables)
Cursiva Frases y palabras enfatizadas en
publicaciones impresas, títulos de libros y cursos, variables
No guarde los cambios en la base de datos.
Para obtener más información, consulte Oracle7 Server
SQL Language Reference Manual.
Introduzca [email protected], donde user_id es el nombre de usuario.
Signos más Combinaciones de teclas
Mantenga pulsadas las teclas siguientes: [Control] + [Alt] + [Supr]
Comillas Títulos de capítulo y de lección en
referencias cruzadas, elementos de
interfaz con
nombres largos que sólo tienen las iniciales en mayúsculas
Este tema se trata en la Unidad II, Lección 3, “Trabajo con Objetos”.
Seleccione el componente “Include a reusable module component” y haga clic en Finish.
Utilice la propiedad “WHERE clause of query”.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Convenciones Tipográficas (continuación)
Convenciones Tipográficas de las Rutas de Acceso de Navegación
Este curso utiliza rutas de acceso de navegación simplificadas, como la del siguiente ejemplo, para guiarlo a través de las aplicaciones Oracle.
Ejemplo:
Invoice Batch Summary
(N) Invoice > Entry > Invoice Batches Summary (M) Query > Find (B) Approve Esta ruta de acceso simplificada significa lo siguiente:
1. (N) En la ventana del navegador, seleccione Invoice > Entry > Invoice Batches Summary. 2. (M) En el menú, seleccione Query > Find.
3. (B) Haga clic en el botón Approve. Notación:
(N) = Navegador (I) = Icono
(M) = Menú (H) = Enlace de hipertexto (S) = Separador (B) = Botón
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Introducción
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Objetivos
Al finalizar esta lección, debería estar capacitado para:
•
Mostrar los objetivos del curso
•
Describir las tablas de ejemplo utilizadas en el curso
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Objetivos del Curso
Al finalizar este curso, debería estar capacitado para:
•
Utilizar técnicas SQL avanzadas de recuperación
de datos para recuperar datos de tablas de base
de datos
•
Aplicar técnicas avanzadas en una práctica que
simule la vida real
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Visión General del Curso
En este curso, utilizará técnicas avanzadas SQL de
recuperación de datos como:
•
Funciones de fecha/hora
•
GROUPING SETS y operadores ROLLUP y CUBE
•
Consultas jerárquicas
•
Subconsultas correlacionadas
•
Inserciones de varias tablas
•
Operación de fusión (merge)
•
Tablas externas
•
Uso de expresiones normales
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Aplicación del Curso
EMPLOYEES DEPARTMENTS
COUNTRIES REGIONS
LOCATIONS
Tablas Utilizadas en el Curso
En el curso, se utilizan las siguientes tablas:
EMPLOYEES: La tabla EMPLOYEES contiene información sobre todos los empleados como
nombre y apellido, identificador de puesto, salario, fecha de contratación, identificador de departamento e identificador de supervisor. Esta tabla es secundaria de la tabla DEPARTMENTS.
DEPARTMENTS: La tabla DEPARTMENTS contiene información como identificador de
departamento, nombre de departamento, identificador de supervisor e identificador de ubicación. Esta tabla es la tabla de clave primaria de la tabla EMPLOYEES.
LOCATIONS: Esta tabla contiene información de la ubicación de departamentos. Contiene
información de identificador de ubicación, calle, ciudad, estado o provincia, código postal e identificador de país. Es la tabla de clave primaria de la tabla DEPARTMENTS y secundaria de la tabla COUNTRIES.
COUNTRIES: Esta tabla contiene los nombres de país, los identificadores de país y los
identificadores de región. Es secundaria de la tabla REGIONS. Es la tabla de clave primaria de la tabla LOCATIONS.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Resumen
En esta lección, ha aprendido lo siguiente:
•
Objetivos del curso
•
Tablas de ejemplo utilizadas en el curso
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Control de Acceso de Usuarios
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Objetivos
Al finalizar esta lección, debería estar capacitado para:
•
Diferenciar los privilegios del sistema de los de
objeto
•
Otorgar privilegios en tablas
•
Ver privilegios en el diccionario de datos
•
Otorgar roles
•
Distinguir entre privilegios y roles
Objetivos
En esta lección, aprenderá a controlar el acceso a base de datos para objetos específicos y a agregar nuevos usuarios con diferentes niveles de privilegios de acceso.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Control de Acceso de Usuarios
Administrador
de la base
de datos
Usuarios
Nombre de usuario y contraseña
Privilegios
Control de Acceso de Usuarios
En un entorno de varios usuarios, necesita mantener la seguridad del acceso y el uso de la base de datos. Con la seguridad de base de datos de Oracle Server, puede:
• Controlar el acceso a la base de datos
• Otorgar acceso a objetos específicos de la base de datos
• Confirmar los privilegios otorgados y recibidos con el diccionario de datos Oracle • Crear sinónimos para objetos de base de datos
La seguridad de base de datos se puede clasificar en dos categorías: seguridad del sistema y seguridad de los datos. La seguridad del sistema cubre el acceso y el uso de la base de datos en el nivel del sistema como, por ejemplo, nombre de usuario y contraseña, el espacio en disco asignado a los usuarios y las operaciones del sistema que pueden realizar los usuarios. La seguridad de datos cubre el acceso y el uso de los objetos de base de datos y las acciones que esos usuarios pueden llevar a cabo en los objetos.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Privilegios
•
Seguridad de base de datos:
– Seguridad del sistema
– Seguridad de datos
•
Privilegios del sistema: Obtención de acceso a la
base de datos
•
Privilegios de objeto: Manipulación del contenido
de los objetos de base de datos
•
Esquemas: Recopilaciones de objetos como, por
ejemplo, tablas, vistas y secuencias
Privilegios
Los privilegios son el derecho a ejecutar sentencias SQL en particular. El DBA (administrador de la base de datos) es un usuario de alto nivel con la capacidad de crear usuarios y de
otorgarles acceso a la base de datos y a sus objetos. Los usuarios necesitan privilegios del
sistema para obtener acceso a la base de datos y privilegios de objeto para manipular el
contenido de los objetos de la base de datos. A los usuarios también se les puede otorgar el privilegio de otorgar privilegios adicionales a otros usuarios o a roles, que son grupos especificados de privilegios relacionados.
Esquemas
Un esquema es una recopilación de objetos como, por ejemplo, tablas, vistas y secuencias. El esquema es propiedad de un usuario de base de datos y tiene el mismo nombre que el usuario. Para obtener más información, consulte el manual de referencia Oracle Database 10g
Application Developer’s Guide – Fundamentals.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Privilegios del Sistema
•
Hay más de 100 privilegios disponibles.
•
El administrador de la base de datos tiene
privilegios del sistema de alto nivel para tareas
como, por ejemplo:
– Creación de usuarios nuevos
– Eliminación de usuarios
– Eliminación de tablas
– Realización de copias de seguridad de tablas
Privilegios del Sistema
Los usuarios y los roles tienen a su disposición más de 100 privilegios del sistema distintos. Los privilegios del sistema suelen ser proporcionados por el administrador de la base de datos.
Privilegios de DBA Típicos
Privilegio del Sistema Operaciones Autorizadas
CREATE USER La persona a la que se otorga el privilegio puede crear otros usuarios de Oracle
DROP USER La persona a la que se otorga el privilegio puede borrar otro usuario.
DROP ANY TABLE La persona a la que se otorga el privilegio puede borrar una tabla de cualquier esquema.
BACKUP ANY TABLE La persona a la que se otorga el privilegio puede realizar copias de seguridad de cualquier esquema con la utilidad de exportación. SELECT ANY TABLE La persona a la que se otorga el privilegio puede consultar tablas,
vistas o instantáneas en cualquier esquema.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Creación de Usuarios
El DBA crea usuarios mediante la sentencia CREATE USER.
CREATE USER HR
IDENTIFIED BY
HR;
User created.
CREATE USER user
IDENTIFIED BY password;
Creación de un Usuario
Para crear el usuario, el DBA ejecuta la sentencia CREATE USER. El usuario no tiene ningún privilegio en ese momento. Por tanto, el DBA puede otorgar privilegios a ese usuario. Estos privilegios determinan lo que el usuario podrá hacer en el nivel de base de datos.
La diapositiva muestra la sintaxis resumida para crear un usuario. En la sintaxis:
user es el nombre del usuario que se va a crear
Password especifica que el usuario se debe conectar con esta contraseña
Para obtener más información, consulte Oracle Database 10g SQL Reference, “GRANT” y “CREATE USER”.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Privilegios del Sistema de Usuario
•
Una vez creado el usuario, el DBA le puede otorgar
privilegios del sistema específicos.
•
Un desarrollador de aplicaciones, por ejemplo,
puede tener los siguientes privilegios del sistema:
– CREATE SESSION
– CREATE TABLE
– CREATE SEQUENCE
– CREATE VIEW
– CREATE PROCEDURE
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];
Privilegios de Usuario Típicos
Una vez creado el usuario, el DBA le puede asignar privilegios.
En la sintaxis:
privilege es el privilegio del sistema que se va a otorgar
user |role|PUBLIC es el nombre del usuario, el nombre del rol o, en el caso de PUBLIC, designa que el privilegio se otorga a todos los usuarios
Privilegio del Sistema Operaciones Autorizadas CREATE SESSION Conectarse a la base de datos
CREATE TABLE Crear tablas en el esquema del usuario
CREATE SEQUENCE Crear una secuencia en el esquema del usuario CREATE VIEW Crear una vista en el esquema del usuario
CREATE PROCEDURE Crear un procedimiento, una función o un paquete en el esquema del usuario
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Otorgamiento de Privilegios del Sistema
El DBA puede otorgar privilegios del sistema específicos
a un usuario.
GRANT
create session, create table,
create sequence, create view
TO
scott;
Grant succeeded.
Otorgamiento de Privilegios del Sistema
El DBA utiliza la sentencia GRANT para asignar privilegios del sistema al usuario. Una vez que se le han otorgado los privilegios al usuario, éste puede utilizarlos de forma inmediata. En el ejemplo de la diapositiva, se han asignado al usuario Scott privilegios para crear sesiones, tablas, secuencias y vistas.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
¿Qué es un Rol?
Asignación de privilegios sin un rol Asignación de privilegios con un rol Privilegios Usuarios Gestor ¿Qué es un Rol?Un rol es un grupo especificado de privilegios relacionados que se pueden otorgar al usuario. Este método facilita la revocación y el mantenimiento de privilegios.
Un usuario puede tener acceso a varios roles y se puede asignar a varios usuarios el mismo rol. Los roles se suelen crear para una aplicación de base de datos.
Creación y Asignación de un Rol
En primer lugar, el DBA debe crear el rol. Después, el DBA puede asignar privilegios al rol y asignar el rol a usuarios.
Sintaxis
CREATE ROLE role;
En la sintaxis:
role es el nombre del rol que se va a crear
Una vez creado el rol, el DBA puede utilizar la sentencia GRANT para asignar el rol a usuarios, del mismo modo que puede asignar privilegios al rol.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Creación y Otorgamiento de Privilegios a un Rol
•
Cree un rol
•
Otorgue privilegios a un rol
•
Otorgue un rol a usuarios
CREATE ROLE manager;
Role created.
GRANT create table, create view
TO manager;
Grant succeeded.
GRANT manager TO DE HAAN, KOCHHAR;
Grant succeeded.
Creación de un Rol
El ejemplo de la diapositiva crea un rol de gestor y, a continuación, permite a los gestores crear tablas y vistas. Otorga después a De Haan y a Kochhar el rol de gestores. De Haan y Kochhar ya pueden crear tablas y vistas.
Si se otorga a los usuarios varios roles, reciben todos los privilegios asociados a esos roles.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Cambio de Contraseñas
•
El DBA crea la cuenta de usuario e inicializa la
contraseña.
•
La contraseña se puede cambiar mediante la
sentencia ALTER USER.
ALTER USER HR
IDENTIFIED BY employ;
User altered.
Cambio de Contraseñas
El DBA crea una cuenta e inicializa una contraseña para cada usuario. La contraseña se puede cambiar mediante la sentencia ALTER USER.
Sintaxis
ALTER USER user IDENTIFIED BY password;
En la sintaxis:
user es el nombre del usuario
password especifica la nueva contraseña
Aunque esta sentencia se puede utilizar para cambiar la contraseña, hay muchas otras opciones. Debe tener el privilegio ALTER USER para cambiar cualquier otra opción. Para obtener más información, consulte el manual Oracle Database 10g SQL Reference.
Nota: SQL*Plus dispone de un comando PASSWORD (PASSW) que se puede utilizar para
cambiar la contraseña de un usuario cuando éste está conectado. Este comando no está disponible en iSQL*Plus.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Privilegios de Objeto
Privilegios
de Objeto Tabla Vista Secuencia Procedimiento
ALTER √ √ DELETE √ √ EXECUTE √ INDEX √ INSERT √ √ REFERENCES √ SELECT √ √ √ UPDATE √ √ Privilegios de Objeto
Un privilegio de objeto es un privilegio o un derecho a realizar una acción determinada en una tabla, una vista, una secuencia o un procedimiento específicos. Cada objeto dispone de un juego determinado de privilegios que se pueden otorgar. La tabla de la diapositiva muestra los privilegios de varios objetos. Tenga en cuenta que los únicos privilegios que se aplican a una secuencia son SELECT y ALTER. UPDATE, REFERENCES e INSERT se pueden restringir mediante la especificación de un subjuego de columnas que se puedan actualizar. Para restringir un privilegio SELECT, se puede crear una vista con un subjuego de columnas y otorgar el privilegio SELECT únicamente en la vista. Un privilegio otorgado en un sinónimo se convierte en un privilegio en la tabla base a la que haga referencia el sinónimo.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Privilegios de Objeto
•
Los privilegios de objeto varían de un objeto a otro.
•
Un propietario tiene todos los privilegios en el objeto.
•
Un propietario puede otorgar privilegios específicos
en el objeto del que es propietario.
GRANT
object_priv [(columns)]
ON
object
TO
{user|role|PUBLIC}
[WITH GRANT OPTION];
Otorgamiento de Privilegios de Objeto
Existen diferentes privilegios de objeto disponibles para diferentes tipos de objetos de esquema. Un usuario tiene automáticamente todos los privilegios de objeto para objetos de esquema contenidos en el esquema del usuario. Un usuario puede otorgar cualquier privilegio de objeto en cualquier objeto de esquema que sea propiedad del usuario a cualquier otro usuario o rol. Si el otorgamiento incluye WITH GRANT OPTION, la persona a la que se otorga el privilegio puede otorgar a su vez el privilegio de objeto a otros usuarios; de lo contrario, la persona a la que se otorga el privilegio lo puede utilizar pero no lo puede otorgar a otros usuarios.
En la sintaxis:
object_priv es un privilegio de objeto que se va a otorgar ALL especifica todos los privilegios de objeto
columns especifica la columna de una tabla o de una vista en la que se otorgan los privilegios
ON object es el objeto en el que se otorgan privilegios TO identifica a quién se otorga el privilegio
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Otorgamiento de Privilegios de Objeto
•
Otorgue privilegios de consulta en la tabla EMPLOYEES.
•
Otorgue privilegios para actualizar columnas
específicas para usuarios y roles.
GRANT
select
ON
employees
TO
sue, rich;
Grant succeeded.
GRANT
update (department_name, location_id)
ON
departments
TO
scott, manager;
Grant succeeded.
Instrucciones
• Para otorgar privilegios en un objeto, éste debe estar en el esquema o le deben haber otorgado los privilegios de objeto con la cláusula WITH GRANT OPTION.
• Un propietario de objeto puede otorgar cualquier privilegio de objeto a cualquier otro usuario o rol de la base de datos.
• El propietario de un objeto adquiere automáticamente todos los privilegios de objeto en ese objeto.
El primer ejemplo de la diapositiva otorga a los usuarios Sue y Rich el privilegio para
consultar la tabla EMPLOYEES. El segundo ejemplo otorga privilegios UPDATE en columnas específicas de la tabla DEPARTMENTS a Scott y al rol de gestor.
Si Sue o Rich quieren utilizar ahora una sentencia SELECT para obtener datos de la tabla EMPLOYEES, la sintaxis que deben utilizar es:
SELECT * FROM HR.employees;
De forma alternativa, pueden crear un sinónimo para la tabla y emitir una sentencia SELECT desde el sinónimo:
CREATE SYNONYM emp FOR HR.employees; SELECT * FROM emp;
Nota: Los DBA suelen asignar privilegios del sistema; cualquier usuario propietario de un
objeto puede otorgar privilegios de objeto.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Transferencia de Privilegios
•
Autorice a un usuario a transferir privilegios.
•
Permita a todos los usuarios del sistema consultar
datos de la tabla DEPARTMENTS de Alice.
GRANT
select, insert
ON
departments
TO
scott
WITH GRANT OPTION;
Grant succeeded.
GRANT
select
ON
alice.departments
TO
PUBLIC;
Grant succeeded.
Palabras Clave WITH GRANT OPTION
La persona a la que se otorga un privilegio que se otorgue con la cláusula WITH GRANT OPTION lo puede transferir a otros usuarios y roles. Los privilegios de objeto otorgados con la cláusula WITH GRANT OPTION se revocan si se revoca el privilegio del otorgante. El ejemplo de la diapositiva otorga al usuario Scott acceso a la tabla DEPARTMENTS con los privilegios para consultar la tabla y agregarle filas. El ejemplo muestra también que Scott puede otorgar a otros estos privilegios.
Palabra Clave PUBLIC
Un propietario de la tabla puede otorgar acceso a todos los usuarios mediante la palabra clave PUBLIC.
El segundo ejemplo permite a todos los usuarios del sistema consultar datos de la tabla DEPARTMENTS de Alice.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Confirmación de Privilegios Otorgados
Vista del Diccionario de Datos Descripción
ROLE_SYS_PRIVS Privilegios del sistema otorgados a roles ROLE_TAB_PRIVS Privilegios de la tabla otorgados a roles USER_ROLE_PRIVS Roles a los que puede acceder el usuario USER_TAB_PRIVS_MADE Privilegios de objeto otorgados en los
objetos del usuario
USER_TAB_PRIVS_RECD Privilegios de objeto otorgados al usuario USER_COL_PRIVS_MADE Privilegios de objeto otorgados en las
columnas de los objetos del usuario
USER_COL_PRIVS_RECD Privilegios de objeto otorgados al usuario en columnas específicas
USER_SYS_PRIVS Privilegios del sistema otorgados al usuario
Confirmación de Privilegios Otorgados
Si intenta realizar una operación no autorizada, como suprimir una fila de una tabla para la que no tiene el privilegio DELETE, Oracle Server no permite que la operación se realice. Si recibe el mensaje de error de Oracle Server “table or view does not exist”, es porque ha realizado una de estas acciones:
• Ha especificado una tabla o una vista que no existen
• Ha intentado realizar una operación en una tabla o en una vista para la que no tiene el privilegio adecuado
Puede acceder al diccionario de datos para ver los privilegios de los que dispone. El gráfico de la diapositiva describe varias vistas de diccionario de datos.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Revocación de Privilegios de Objeto
•
Utilice la sentencia REVOKE para revocar privilegios
otorgados a otros usuarios.
•
También se revocan los privilegios otorgados a
otros mediante la cláusula WITH GRANT OPTION.
REVOKE {privilege [, privilege...]|ALL}
ON
object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
Revocación de Privilegios de Objeto
Puede eliminar privilegios otorgados a otros usuarios mediante la sentencia REVOKE. Al utilizar la sentencia REVOKE, los privilegios que especifique se revocarán de los usuarios que especifique y de cualquier otro usuario a quien el usuario revocado hubiera otorgado esos privilegios.
En la sintaxis:
CASCADE es necesario para eliminar cualquier restricción de integridad referencial realizada en el objeto CONSTRAINTS mediante el privilegio REFERENCES
Para obtener más información, consulte Oracle Database 10g SQL Reference.
Nota: Si revoca los privilegios de un usuario que debe dejar la compañía, debe volver a
otorgar cualquier privilegio que este usuario hubiera otorgado a otros usuarios. Si borra la cuenta de usuario sin revocarle los privilegios, esta acción no afectará a los privilegios del sistema otorgados por este usuario a otros usuarios.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Revocación de Privilegios de Objeto
Como usuario Alice, revoque los privilegios SELECT e
INSERT otorgados al usuario Scott en la tabla
DEPARTMENTS.
REVOKE
select, insert
ON
departments
FROM
scott;
Revoke succeeded.
Revocación de Privilegios de Objeto (continuación)
El ejemplo de la diapositiva revoca los privilegios SELECT e INSERT otorgados al usuario Scott en la tabla DEPARTMENTS.
Nota: Si se otorga un privilegio a un usuario con la cláusula WITH GRANT OPTION, ese
usuario también puede otorgar el privilegio con la cláusula WITH GRANT OPTION, de forma que es posible una larga cadena de personas a las que se otorgan privilegios, aunque no se permiten otorgamientos circulares. Si el propietario revoca un privilegio de un usuario que otorgó dicho privilegio a otros usuarios, se revocarán en cascada todos los privilegios
otorgados.
Por ejemplo, si el usuario A otorga un privilegio SELECT en una tabla al usuario B con la cláusula WITH GRANT OPTION, el usuario B también puede otorgar al usuario C el privilegio SELECT con la cláusula WITH GRANT OPTION y el usuario C puede otorgar al usuario D el privilegio SELECT. Si el usuario A revoca los privilegios del usuario B, los privilegios otorgados a los usuarios C y D también se revocan.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Resumen
En esta lección, ha obtenido información acerca de
sentencias que controlan el acceso a la base de datos
y a objetos de base de datos.
Sentencia Acción
CREATE USER Crea un usuario (la suele realizar un DBA) GRANT Otorga a otros usuarios privilegios para
acceder a objetos
CREATE ROLE Crea una recopilación de privilegios (la suele realizar un DBA)
ALTER USER Cambia la contraseña de un usuario
REVOKE Elimina privilegios en un objeto de usuarios
Resumen
Los DBA establecen la seguridad de base de datos inicial para los usuarios asignándoles privilegios.
• El DBA crea usuarios que deben tener una contraseña. El DBA es responsable además de establecer los privilegios iniciales del sistema para un usuario.
• Cuando el usuario ha creado un objeto, puede transferir cualquiera de los privilegios de objeto disponibles a otros usuarios o a todos los usuarios mediante la sentencia GRANT. • Un DBA puede crear roles mediante la sentencia CREATE ROLE para transferir una
recopilación de privilegios del sistema o de objeto a varios usuarios. Los roles facilitan el mantenimiento del otorgamiento y de la revocación de privilegios.
• Los usuarios pueden cambiar la contraseña mediante la sentencia ALTER USER. • Puede eliminar privilegios otorgados a otros usuarios mediante la sentencia REVOKE. • Con las vistas de diccionario de datos, los usuarios pueden ver los privilegios que se les
han otorgado y los que se han otorgado a sus objetos.
• Con los enlaces de base de datos, puede acceder a datos en bases de datos remotas. No se pueden otorgar privilegios en objetos remotos.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Práctica 1: Visión General
Esta práctica cubre los temas siguientes:
•
Otorgamiento a otros usuarios de privilegios de
su tabla
•
Modificación de la tabla de otro usuario a través
de los privilegios que se le han otorgado
•
Creación de sinónimos
•
Consulta de las vistas de diccionario de datos
relacionadas con privilegios
Práctica 1: Visión General
Forme un equipo con otros alumnos para este ejercicio sobre el control del acceso a los objetos de base de datos.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Práctica 1
Para contestar a la pregunta 6 y a las posteriores, se deberá conectar a la base de datos mediante iSQL*Plus. Para ello, inicie el explorador Internet Explorer desde el escritorio del cliente. Introduzca la dirección URL en formato http://machinename:5561/isqlplus/ y utilice la cuenta oraxx y la contraseña y el identificador de servicio correspondientes (en formato
Tx) que le proporcione el instructor para conectarse a la base de datos.
1. ¿Qué privilegio se debe otorgar a un usuario para conectarse a Oracle Server? ¿Se trata de un privilegio de objeto o del sistema?
_____________________________________________________________________ 2. ¿Qué privilegio se debe otorgar a un usuario para crear tablas?
_____________________________________________________________________ 3. Si crea una tabla, ¿quién puede transferir privilegios a otros usuarios en su tabla?
_____________________________________________________________________ 4. Usted es el DBA. Está creando muchos usuarios que requieren los mismos privilegios
del sistema.
¿Qué debería utilizar para facilitar el trabajo?
_____________________________________________________________________ 5. ¿Qué comando se utiliza para cambiar la contraseña?
_____________________________________________________________________ 6. Otorgue acceso a su tabla DEPARTMENTS a otro usuario. Haga que el usuario le
otorgue acceso de consulta a su tabla DEPARTMENTS. 7. Consulte todas las filas de la tabla DEPARTMENTS.
…
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
como número de departamento 500. El equipo 2 debe agregar Human Resources como número de departamento 510. Consulte la tabla del otro equipo.
9. Cree un sinónimo para la tabla DEPARTMENTS del otro equipo.
10. Consulte todas las filas de la tabla DEPARTMENTS del otro equipo mediante el sinónimo.
Team 1 SELECT statement results:
Team 2 SELECT statement results:
…
…
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Práctica 1 (continuación)
11. Consulte el diccionario de datos USER_TABLES para ver información sobre las tablas que son de su propiedad.
12. Consulte la vista de diccionario de datos ALL_TABLES para ver información sobre todas las tablas a las que puede acceder. Excluya las tablas de su propiedad.
Nota: La lista que obtenga puede que no coincida exactamente con la que se muestra a
continuación.
13. Revoque el privilegio SELECT del otro equipo.
14. Elimine la fila que insertó en la tabla DEPARTMENTS en el paso 8 y guarde los cambios.
…
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Gestión de Objetos de Esquema
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Objetivos
Al finalizar esta lección, debería estar capacitado para:
•
Agregar restricciones
•
Crear índices
•
Crear índices mediante la sentencia CREATE
TABLE
•
Crear índices basados en funciones
•
Borrar columnas y definir columnas como UNUSED
•
Realizar operaciones FLASHBACK
•
Crear y utilizar tablas externas
Objetivos
Esta lección contiene información sobre la creación de índices y restricciones, así como sobre la modificación de objetos existentes. También recibirá información sobre tablas externas y la provisión para asignar un nombre el índice en el momento de la creación de una restricción de clave primaria.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Sentencia ALTER TABLE
Utilice la sentencia ALTER TABLE para:
•
Agregar una nueva columna
•
Modificar una columna existente
•
Definir un valor por defecto para la nueva columna
•
Borrar una columna
Sentencia ALTER TABLE
Después de crear una tabla, puede que necesite cambiar la estructura de tabla porque ha omitido una columna, se debe cambiar la definición de columna o debe eliminar columnas. Puede hacerlo mediante la sentencia ALTER TABLE.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Sentencia ALTER TABLE
Utilice la sentencia ALTER TABLE para agregar,
modificar o borrar columnas.
ALTER TABLE table
ADD
(column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
MODIFY
(column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
DROP
(column);
Sentencia ALTER TABLE (continuación)
Puede agregar columnas a una tabla, modificar columnas y borrar columnas de una tabla mediante la sentencia ALTER TABLE.
En la sintaxis:
table es el nombre de la tabla ADD|MODIFY|DROP es el tipo de modificación
column es el nombre de la nueva columna
datatype es el tipo de datos y la longitud de la nueva columna DEFAULT expr especifica el valor por defecto para una nueva columna
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Adición de una Columna
•
Se utiliza la cláusula ADD para agregar columnas.
•
La nueva columna se convierte en la última.
…
ALTER TABLE dept80
ADD
(job_id VARCHAR2(9));
Table altered.
Instrucciones para Agregar una Columna
• Puede agregar o modificar columnas.
• No puede especificar dónde aparecerá la columna. La nueva columna se convierte en la última.
El ejemplo de la diapositiva agrega una columna denominada JOB_ID a la tabla DEPT80. La columna JOB_ID se convierte en la última columna de la tabla.
Nota: Si una tabla ya contiene filas cuando se agrega una columna, la nueva columna es nula
inicialmente para todas las filas. No puede agregar una columna NOT NULL obligatoria a una tabla que contiene datos en otras columnas. Sólo puede agregar una columna NOT NULL a una tabla vacía.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Modificación de una Columna
•
Puede cambiar el tipo de datos de una columna,
su tamaño y el valor por defecto.
•
Un cambio en el valor por defecto sólo afecta a las
siguientes inserciones en la tabla.
ALTER TABLE dept80
MODIFY
(last_name VARCHAR2(30));
Table altered.
Modificación de una Columna
Puede modificar una definición de columna mediante la sentencia ALTER TABLE con la cláusula MODIFY. La modificación de columnas puede incluir cambios en el tipo de datos de una columna, su tamaño y el valor por defecto.
Instrucciones
• Puede aumentar el ancho o la precisión de una columna numérica. • Puede aumentar el ancho de las columnas numéricas o de caracteres. • Puede reducir el ancho de una columna si:
- La columna contiene sólo valores nulos - La tabla no tiene ninguna fila
- La reducción en el ancho de columna no es menor que los valores existentes en esa columna
• Puede cambiar el tipo de datos si la columna contiene sólo valores nulos. La excepción son las conversiones CHAR a VARCHAR2, que se pueden realizar con datos en las columnas.
• Puede convertir una columna CHAR al tipo de datos VARCHAR2 o convertir una
columna VARCHAR2 al tipo de datos CHAR sólo si la columna contiene valores nulos o si no cambia el tamaño.
• Un cambio en el valor por defecto de una columna sólo afecta a las siguientes inserciones en la tabla.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Borrado de una Columna
Utilice la cláusula DROP COLUMN para borrar las
columnas de la tabla que ya no necesite.
ALTER TABLE dept80
DROP COLUMN job_id;
Table altered.
Borrado de una Columna
Puede borrar una columna de una tabla mediante la sentencia ALTER TABLE con la cláusula DROP COLUMN.
Instrucciones
• La columna puede contener o no datos.
• Mediante la sentencia ALTER TABLE, sólo se puede borrar una columna cada vez. • Debe quedar al menos una columna en la tabla después de la modificación.
• Una vez borrada una columna, no se puede recuperar.
• Una columna no se puede borrar si forma parte de una restricción o de una clave de índice a menos que se agregue la opción de cascada.
• El borrado de una columna puede tardar si la columna tiene muchos valores. En este caso, es posible que sea mejor definirla como no utilizada y borrarla cuando haya menos usuarios en el sistema para evitar bloqueos extendidos.
Nota: Hay columnas que no se pueden borrar nunca, como las que forman parte de la clave de
partición de una tabla particionada o las columnas que forman parte de la clave primaria de una tabla organizada por índice.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Opción SET UNUSED
•
Utilice la opción SET UNUSED para marcar una o más
columnas como no utilizadas.
•
Utilice la opción DROP UNUSED COLUMNS para eliminar
las columnas marcadas como no utilizadas.
ALTER TABLE
<table_name>
SET UNUSED(<column_name>);
ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;
OR
ALTER TABLE <table_name>
DROP UNUSED COLUMNS;
Opción SET UNUSED
La opción SET UNUSED marca una o más columnas como no utilizadas para que se puedan borrar cuando la demanda de recursos del sistema sea menor. La especificación de esta cláusula no elimina realmente las columnas de destino de cada fila de la tabla (es decir, no restaura el espacio en disco que utilizan estas columnas). Por lo tanto, el tiempo de respuesta es más rápido que si ejecuta la cláusula DROP. Las columnas no utilizadas se tratan como si se hubieran borrado, incluso aunque sus datos de columna sigan estando en las filas de la tabla. Si una columna se ha marcado como no utilizada, no se puede acceder a ella. Una consulta SELECT * no recuperará datos de columnas no utilizadas. Además los nombres y los tipos de columnas marcadas como no utilizadas no se mostrarán durante una sentencia DESCRIBE y puede agregar una nueva columna a la tabla con el mismo nombre que una columna no utilizada. La información SET UNUSED se almacena en la vista de diccionario USER_UNUSED_COL_TABS.
Nota: Las instrucciones para definir una columna como UNUSED son parecidas a las que se
aplican al borrado de una columna.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Opción DROP UNUSED COLUMNS
DROP UNUSED COLUMNS elimina de la tabla todas las columnas marcadas actualmente como no utilizadas. Puede utilizar esta sentencia si desea reclamar el espacio en disco
adicional de las columnas no utilizadas en la tabla. Si la tabla no contiene tablas no utilizadas, la sentencia no devuelve ningún error.
ALTER TABLE dept80
SET UNUSED (last_name); Table altered.
ALTER TABLE dept80 DROP UNUSED COLUMNS; Table altered.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Adición de una Sintaxis de Restricción
Utilice la sentencia ALTER TABLE para:
•
Agregar o borrar una restricción, pero sin modificar
su estructura
•
Activar o desactivar restricciones
•
Agregar una restricción NOT NULL mediante la
cláusula MODIFY
ALTER TABLE
<table_name>
ADD [CONSTRAINT <constraint_name>]
type (<column_name>);
Adición de una Restricción
Puede agregar una restricción para tablas existentes mediante la sentencia ALTER TABLE con la cláusula ADD.
En la sintaxis:
table es el nombre de la tabla
constraint es el nombre de la restricción
type es el tipo de restricción
column es el nombre de la columna a la que afecta la restricción
La sintaxis de nombre de restricción es opcional, aunque se recomienda. Si no asigna nombres a las restricciones, el sistema los generará.
Instrucciones
• Puede agregar, borrar, activar o desactivar una restricción, pero no puede modificar su estructura.
• Puede agregar una restricción NOT NULL a una columna existente mediante la cláusula MODIFY de la sentencia ALTER TABLE.
Nota: Puede definir una columna NOT NULL sólo si la tabla está vacía o si la columna tiene
un valor para todas las filas.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
Adición de una Restricción
Agregue una restricción FOREIGN KEY a la tabla EMP2
que indique que ya debe existir un supervisor como
empleado válido en la tabla EMP2.
ALTER TABLE emp2
modify employee_id Primary Key;
Table altered.
ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);
Table altered.
Adición de una Restricción (continuación)
El primer ejemplo de la diapositiva modifica la tabla EMP2 para agregar una restricción PRIMARY KEY en la columna EMPLOYEE_ID. Observe que, como no se proporciona un nombre de restricción, Oracle Server lo especifica automáticamente. El segundo ejemplo de la diapositiva crea una restricción FOREIGN KEY en la tabla EMP2. La restricción asegura que existe un supervisor como empleado válido en la tabla EMP2.
Base
de
Datos
Oracle
10g
SQL
Fundamentals
II
Guía
del
Alumno
ON DELETE CASCADE
Suprima las filas secundarias al suprimir una clave
principal.
ALTER TABLE Emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments ON DELETE CASCADE);
Table altered.
ON DELETE CASCADE
La acción ON DELETE CASCADE permite que se supriman los datos de clave principal a los que se hace referencia desde la tabla secundaria, pero no que se actualicen. Cuando se
suprimen los datos de la clave principal, también se suprimen todas las filas de la tabla secundaria que dependen de los valores de la clave principal suprimida. Para especificar esta acción referencial, incluya la opción ON DELETE CASCADE en la definición de la
restricción FOREIGN KEY.
