Manual de Oracle 9I (página 2)

• Tras la instalación de Oracle y la creación de la base de datos aparecerá una ventana indicando que el proceso de instalación ha finalizado:

Arquitectura de un servidor Oracle 9i

ESQUEMA GENERAL

Por cada instancia de Oracle se tiene una sola base de datos.

En un servidor se puede crear varias instancias pero es recomendable solo una porque cada instancia consume muchos recursos.

Arquitectura de Oracle 9i

La arquitectura de Oracle tiene tres componentes básicos:

Las estructuras de memoria. Para almacenar los datos y el código ejecutable.

Los Procesos. Son los que corren el sistema de BD y las tareas de cada usuario conectado a la BD.

Los Archivos. Son los que sirven para el almacenamiento físico en el disco de la información de la BD.

INSTANCIA DE ORACLE

Para permitir el acceso a los datos, Oracle utiliza un conjunto de procesos que son compartidos por todos los usuarios. Además, existen estructuras de memoria que son utilizadas para almacenar los datos más recientemente solicitados a la BD.

Una instancia de BD es el conjunto de estructuras de memoria y de procesos que acceden a los ficheros de datos.

Los parámetros que determinan el tamaño y composición de una instancia están almacenados en un fichero llamado init.ora. Este fichero es leído durante el arranque de la BD y puede ser modificado por el DBA. Cualquier modificación de este fichero no tiene efecto hasta la siguiente vez que se arranque la BD.

La instancia de Oracle esta compuesta por el proceso de fondo y un área de memoria compartida SGA

Consiste en una estructura de memoria SGA (System Global Area) y un conjunto de procesos usados por el servidor de Oracle para gestionar una BD.

Estructura de memoria

Hay dos clases de memoria, una de ella compartida por todos los usuarios conectados y otra dedicada al trabajo de cada una de ellas.

SGA (System Global Area)

PGA (Process Global Area)

SGA (System Global Area)

Sirve para facilitar la transferencia de información entre usuarios y también almacena la información estructural de la BD más frecuentemente requerida.

El SGA es utilizado para el intercambio de datos entre el servidor y las aplicaciones cliente.

Una instancia Oracle puede abrir una sola base de datos a la vez.

Library Cache

Almacena las sentencias SQL mas recientes en memoria.

Database Dictionary Cache

Buffer para el diccionario de datos. Tablas, columnas, tipos, índices.

Database Buffer Cache

Buffer de la base de datos, contiene bloques de datos que han sido cargados desde los Datafiles.

Redo Log Buffer Cache

Bloques de datos que han sido actualizados.

PGA (Process Global Area)

El Process Global Area es un área de memoria utilizada por un proceso Oracle. Esta zona de memoria no se puede compartir.

Es un área no compartida en la cual se puede escribir y contiene:

Área e almacenamiento

Información de la sesión

Estado del cursor

Espacio de la pila

Procesos

Los procesos son programas que se ejecutan para permitir el acceso a los datos, estos procesos se cargan en memoria y son transparentes para los usuarios y son los siguientes:

PMON (Process Monitor)

Monitorea los procesos de los usuarios en caso de que la conexión falle.

SMON (System Monitor)

Proceso encargado de recuperar la instancia y abrir la base de datos en caso de que ocurra alguna falla.

CKPT (CheckPoint Process)

Sintoniza las tareas de grabación en BD.

DBWR (Database Write)

Escribe los bloques de datos de la memoria a la base de datos.

Es uno de los dos únicos procesos que tienen permitido escribir en los archivos de datos que componen la base de datos Oracle.

LGWR (Log Writer)

Graba los bloques del Redo Log del Fuffer a los archivos Redo Log File.

Escribe las transacciones que se encuentran en los Redo Log a Disco cuando ocurre un commit.

Base de datos

La base de datos Oracle es un conjunto de datos tratados como una sola y misma entidad y está constituida de tres tipos de archivos, a saber:

Los ficheros de control (Control File)

Los ficheros de datos (Data File)

Los ficheros log (Log File)

Control File

Contiene información para mantener y controlar la integridad de la base de datos. Estos archivos especifican cuales Datafiles conforman la BD para poder tener acceso a los datos o para poder recuperar la BD antes de una falla

Data File

Sirve para el almacenamiento físico de las tablas, índices y procedimientos, Estos son los archivos donde se almacenan los datos de las aplicaciones es decir contienen los datos de los usuarios de la base de datos.

Redo Log File

Almacena los cambios hechos en la base de datos con propósito de recuperarlos en caso de falla.

CONEXIONES DE UNA BASE DE DATOS

1. CONEXIÓN NATIVA

Es una conexión entre el cliente y el servidor sin utilizar otra aplicación y pertenecen a una misma plataforma (familia).

Requerimientos para realizar una conexión nativa o TNS

Add Database Alias

Database Alias. Nombre a la instancia del servidor o nombre a la base de datos de nuestro servidor.

Protocolo a usar (TCP/IP).

TCP/IP

Hostname: IP del servidor

Database Instance: Oracle

Verificación de los datos

Exit Neteasy Configuration

2. CONEXIÓN ODBC

Es una conexión entre el cliente y el servidor utilizando una aplicación para la conexión, es decir una conexión entre diferentes plataformas (familias).

Requisitos

Instalar el driver ODBC de Oracle.

Crear la conexión nativa.

Crear la conexión ODBC.

ESQUEMA GENERAL DE LA CONEXIÓN A UNA BD DE ORACLE

Proceso Usuario

Es un programa, aplicación o herramienta que usa el usuario para iniciar un proceso de usuario y establecer una conexión.

Proceso servidor

Una vez establecida la conexión, un proceso servidor es iniciado, el cual maneja la peticiones del proceso usuario.

Un proceso servidor puede ser dedicado, es decir es decir solo atiende las peticiones de un solo proceso usuario, o puede ser compartido, con lo cual puede atender múltiples procesos usuarios.

Sesión

Es una conexión específica de un usuario a un servidor Oracle.

Se inicia cuando el usuario es validado por el servidor Oracle.

Finaliza cuando el usuario termina la sesión en forma normal o aborta la sesión.

Herramienta SQL Plus

Es una herramienta que permite al usuario comunicarse con el servidor para procesar comandos SQL o PL/SQL tiene la flexibilidad de realizar inicio y parada de la base de datos.

DDL (Data Definition Language )

Lenguaje de definición de datos, este lenguaje es proporcionado por el sistema de gestión de base de datos que permite a los usuarios de la misma llevar a cabo las tareas de definición de las estructuras que almacenarán los datos así como de los procedimientos o funciones que permitan consultarlos.

El lenguaje de programación SQL, el más difundido entre los gestores de bases de datos, admite las siguientes sentencias de definición: CREATE, DROP y ALTER, cada una de las cuales se puede aplicar a las tablas, vistas, procedimientos almacenados y triggers de la base de datos.

Otras que se incluyen dentro del DDL, pero que su existencia depende de la implementación del estándar SQL que lleve a cabo el gestor de base de datos son GRANT y REVOKE, los cuales sirven para otorgar permisos o quitarlos, ya sea a usuarios específicos o a un rol creado dentro de la base de datos.

DML (Data Manipulation Language)

Lenguaje de Manipulación de Datos, es un lenguaje proporcionado por el sistema de gestión de base de datos que permite a los usuarios de la misma llevar a cabo las tareas de consulta o manipulación de los datos, organizados por el modelo de datos adecuado.

El lenguaje de manipulación de datos más popular hoy día es SQL, usado para recuperar y manipular datos en una base de datos relacional. Otros ejemplos de DML son los usados por bases de datos IMS/DL1, CODASYL u otras.

CONEXIÓN UTILIZANDO PL/SQL

Para la conexión debemos ingresar el usuario, la contraseña y la cadena de conexión y podemos utilizar los siguientes usuarios.

La pantalla de bienvenida a SQL Plus indicara lo siguiente y estaremos listos para trabajar.

Ejemplo

Conexión como el usuario Scott

Áreas lógicas y archivos físicos

TABLESPACE

Un Tablespace es una unidad lógica que denota el espacio de almacenamiento de datos dentro de una base de datos y que están constituidos por uno o más Datafiles

Un Tablespace no es un fichero físico en el disco, simplemente es el nombre que tiene un conjunto de propiedades de almacenamiento que se aplican a los objetos (tablas, secuencias, etc.) que se van a crear en la base de datos bajo el Tablespace indicado (tablas, secuencias, etc.).

Un objeto en base de datos debe estar almacenado obligatoriamente dentro de un Tablespace.

Propiedades:

Localización de los ficheros de datos.

Especificación de máximas cuotas de consumo de disco.

Control de la disponibilidad de los datos (en línea o fuera de línea).

Backup de datos.

Cuando un objeto se crea dentro de un cierto Tablespace, este objeto adquiere todas las propiedades antes descritas del Tablespace utilizado.

Características:

Debe estar asociado a un Datafiles.

Un Tablespace esta asociado a uno o varios Datafiles y puede tener uno o varios usuarios.

Sintaxis para seleccionar los Tablespace del sistema.

SELECT * FROM DBA_TABLESPACES;

Creación de un tablespace

DATAFILE

Un Datafiles es la representación física de un Tablespace. Son los "ficheros de datos" donde se almacena la información físicamente, puede tener cualquier nombre y extensión (siempre dentro de las limitaciones del sistema operativo), y puede estar localizado en cualquier directorio del disco duro, aunque su localización típica suele ser $ORACLE_HOME/Database.

Un Datafiles tiene un tamaño predefinido en su creación (por ejemplo 100Mb) y este puede ser alterado en cualquier momento. Cuando creemos un Datafiles, este ocupará tanto espacio en disco como hayamos indicado en su creación, aunque internamente esté vacío. Oracle hace esto para reservar espacio continuo en disco y evitar así la fragmentación. Conforme se vayan creando objetos en ese Tablespace, se irá ocupando el espacio que creó inicialmente.

Características:

Archivo físico donde se almacenan los datos.

Es el espacio de memoria concedido al usuario para que interactué con el SGA.

Un Datafiles esta asociado a un solo Tablespace.

Se pueden extender automáticamente cuando la BD necesita espacio.

Sintaxis para seleccionar los DataFile del sistema.

SQL> SELECT * FROM DBA_DATA_FILE;

OBJETOS DE LA BASE DE DATOS PARA DAR SEGURIDAD

USUARIOS

Una cuenta de usuario no es una estructura física de la BD, pero está relacionada con los objetos de la BD: los usuarios poseen los objetos de la BD. Existen dos usuarios especiales: SYS y SYSTEM.

El usuario SYS posee las tablas del diccionario de datos; que almacenan información sobre el resto de las estructuras de la BD.

El usuario SYSTEM posee las vistas que permiten acceder a las tablas del diccionario, para el uso del resto de los usuarios de la BD.

Todo objeto creado en la BD se crea por un usuario, en un espacio de tablas y en un fichero de datos determinado. Toda cuenta de la BD puede estar unida a una cuenta del S.O, lo que permite a los usuarios acceder a la cuenta de la BD sin dar la clave de acceso.

Cada usuario puede acceder a los objetos que posea o a aquellos sobre los que tenga derecho de acceso.

Características:

Es el medio de conexión entre el usuario y la base de datos.

Los usuarios son los diferentes usuarios de la Base de Datos. Cada uno es propietario de sus objetos.

Un usuario puede ser activo es decir que puede interactuar con la BD o puede ser un usuario inactivo.

Consideraciones para crear un usuario:

El nombre de usuario no debe superar 30 caracteres, no debe tener caracteres especiales y debe iniciar con una letra.

El usuario puede autentificarse de varias formas por medio de:

Password

Usuario heredado

Autentificación global. Que puede ser por password o heredado.

Un Tablespace temporal, donde el usuario crea sus objetos temporales y hace los sort u ordenamientos.

Un perfil o profile de usuario, que son las restricciones que puede tener su cuenta (opcional).

Por ejemplo, conectado como el usuario SYSTEM, creamos un usuario con su clave y vamos a modificar mediante el password que cuenta con tres estados.

1. ALTA

Sintaxis para crear un usuario

SQL>CREATE USER ( nombre_usuario)

IDENTIFIED BY (contraseña)

DEFAULT TABLESPACE (nombre_tablespace);

Default TAblespace (tablespace asignado al usuario).

Temporary TAblespace (tablespace temporal asignado al usuario).

Password expire (permite cambiar la clave).

Account Lock o UnlocK(si no se pone la instrucción el sistema por defecto lo activa).

Ejemplo:

SQL> CREATE USER inventario

IDENTIFIED BY jessica

DEFAULT TABLESPACE septimo;

2. ACTUALIZAR

Sintaxis para actualizar un usuario

SQL> ALTER USER (nombre_usuario)

IDENTIFIED BY (nueva_contraseña)

ACCOUNT LOCK;

Ejemplo:

SQL> ALTER USER inventario

IDENTIFIED BY jessica1

ACCOUNT LOCK;

3. BAJA

En este estado es cuando el usuario ya no hace las funciones para la que fue creado.

Sintaxis para eliminar un usuario

SQL> DROP USER (nombre_usuario);

Ejemplo:

SQL> ALTER USER inventario

PRIVILEGIOS

Los privilegios pueden ser de dos tipos:

1. Privilegios del sistema, como por ejemplo para crear tablas.

2. Privilegios sobre objetos, como por ejemplo permiso de select sobre una tabla concreta.

PRIVILEGIOS DEL SISTEMA

Permite al usuario hacer ciertas tareas sobre la BD, como por ejemplo crear un Tablespace. Estos permisos son otorgados por el administrador o por alguien que haya recibido el permiso para administrar ese tipo de privilegio.

En general los permisos de sistema, permiten ejecutar comandos del tipo DDL (Data definition Language), como CREATE, ALTER y DROP o del tipo DML (Data Manipulation Language.

Para otorgar varios permisos a la vez, se hace de la siguiente manera:

Sintaxis para otorgar permisos.

SQL> GRANT CREATE USER, ALTER USER, DROP USER TO (nombre_usurio);

Ejemplo:

SQL> GRANT CONNECT, RESOURCE TO inventario;

Sintaxis para utilizar el privilegio Grant.

GRANT [privilegios_de_sistema | roles] TO [usuarios | roles |PUBLIC]

{WITH ADMIN OPTION};

WITH ADMIN OPTION permite que el privilegio/rol que se ha concedido, pueda ser concedido a otros usuarios por el usuario al que se le está asignando.

Instrucción REVOKE:

REVOKE [privilegios_de_sistema | roles] FROM [usuarios | roles

|PUBLIC];

Ejemplo:

SQL> REVOKE CONNECT, RESOURCE FROM inventario;

PRIVILEGIOS SOBRE LOS OBJETOS

Este tipo de privilegios le permite al usuario hacer cierta acción en un objeto de la base de datos, como puede ser una tabla, vista, función etc. Si a un usuario no se le dan estos privilegios solo podrías acceder a sus propios objetos. Y estos tipos de privilegios los da el dueño del objeto, el administrador o alguien que haya recibido este permiso explícitamente. Los privilegios sobre objetos consienten que un objeto (creado por un usuario) estos privilegios puede ser de SELECT, de UPDATE, de DELETE, de INSERT.

La sintaxis del privilegio Grant es:

GRANT [ALL {PRIVILEGES} | SELECT | INSERT | UPDATE | DELETE]

ON objeto TO [usuario | rol | PUBLIC]

{WITH ADMIN OPTION};

SQL> GRANT SELECT, INSERT, UPDATE, ON (esquema_objeto) TO (usuario);

Adicionalmente, podemos restringir los DML a una columna de la tabla mencionada. Si quisiéramos que este usuario pueda dar permisos sobre la tabla  a otros usuarios, utilizamos la cláusula WITH GRANT OPTION. Ejemplo:

SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON (esquema_objeto) TO (usuario) WITH GRANT OPTION;

De igual manera para eliminar privilegios sobre objetos se tiene la instrucción

REVOKE:

Si sintaxis es:

REVOKE [ALL {PRIVILEGES} | SELECT | INSERT | UPDATE | DELETE]

ON objeto FROM [usuario | rol | PUBLIC]

{WITH ADMIN OPTION};

SQL>REVOKE SELECT, INSERT, UPDATE, DELETE ON (esquema_objeto) TO (usuario) WITH GRANT OPTION;

ROLES

Los roles son conjuntos de privilegios. Un rol puede tener garantizados una serie de privilegios tanto del sistema como sobre objetos, y a la vez puede tener garantizado otros roles.

Por default cuando creamos un usuario desde el Enterprise Manager se le asigna el permiso de Connect, lo que permite al usuario conectarse a la BD y crear sus propios objetos en su propio esquema. De otra manera, debemos asignarlos en forma manual.

Sintaxis para crear un Rol y asignarlo a un usuario:

SQL> CREATE ROLE appl_dba;

Opcionalmente, se puede asignar una clave al Rol:

SQL> SET ROLE appl_dba IDENTIFIED BY app_pwd;

Para asignar este Rol a un usuario:

SQL> GRANT appl_dba TO jperez;

Otro uso común de los roles es asignarles privilegios a nivel de Objetos, por ejemplo en una Tabla de Facturas en donde sólo queremos que se puedan hacer Querys e Inserts:

SQL> CREATE ROLE consulta;

SQL> GRANT SELECT, INSERT on analista.factura TO consulta;

PERFILES

Los perfiles permiten definir limitaciones de recursos. Por ejemplo podemos definir un perfil que limite el número de sesiones abiertas concurrentemente por un usuario cualquiera, y posteriormente aplicar este perfil a uno o más usuarios concretos.

ESQUEMA BASICO DE SEGURIDAD

Secuencias

A menudo es preciso generar números en forma ordenada para implementar, por ejemplo, una clave primaria en una tabla o garantizar que esos números no se repiten y van siempre en un orden predefinido por el desarrollador (no necesariamente secuenciales).

Oracle implementa los objetos denominadas secuencias, que permiten hacer lo anterior de manera transparente para el usuario.

Cuando se define una secuencia se deben indicar, como mínimo, el valor de partida (valor mínimo) y el incremento.

Es un objeto que permite generar valores únicos. Se emplean para obtener las claves primarias de las tablas.

Objeto que genera números en forma continua.

La sintaxis de creación de una secuencia:

CREATE SEQUENCE nombre_secuencia

DISPARADORES

Son procedimientos que son ejecutados cuando se produce un determinado evento en la BD. Se pueden utilizar para mejorar y reforzar la integridad y la seguridad de la BD.

Son segmentos de código PL/SQL que se ejecutan cuando se producen operaciones de DML. Se definen a nivel de tabla y se clasifican según tres parámetros:

Before/After, si se ejecuta antes o después de la sentencia.

Insert/Update/Delete, según se ejecute una de estas sentencias.

Each row/Statement dependiendo si se ejecuta para cada fila afectada en la sentencia, o bien una sola vez por sentencia con independencia de las filas de la tabla afectadas.

EJERCICIOS

Creación de una secuencia y un disparador para cada tabla e insertar datos en la tabla.

Clientes

Categorías

Productos

Proveedores

Compras

Detalle Compras

Ventas

Declaraciones y tipos de datos de PL/SQL

DECLARACIONES

PL/SQL. Es un lenguaje procedimental que amplía la funcionalidad de SQL añadiendo estructuras habituales en otros lenguajes de programación como:

Variables y tipos (predefinidos y definidos por el usuario)

Estructuras de control (bucles y condiciones IF-THEN-ELSE)

Procedimientos y funciones.

Tipos de objetos y métodos

Creación de programas PL/SQL.

Podemos crear programas con cualquier editor y ejecutarlos desde el prompt de SQL con STAR o @. Los ficheros creados serán de texto y tendrán la extensión SQL.

Para que un fichero se ejecute correctamente debe tener en su última línea el símbolo /.

Características de PL/SQL

La unidad básica en PL/SQL es el bloque. Todos los programas de PL/SQL están compuestos por bloques que pueden estar anidados.

Estructura de un Bloque:

DECLARE

/*Aquí se declaran las variables, tipos cursores y subprogramas locales*/

BEGIN

/* Aquí se incluyen las órdenes SQL y PL/SQL, es obligatoria y debe contener, al menos una orden ejecutable */

END;

EXCEPTION

/* Sección para el manejo de excepciones (errores)*/

END;

Creación de bloques

Bloques anónimos: Se construyen de forma dinámica y se suelen ejecutar una sola vez.

Bloques nominados: Igual que los anónimos pero con una etiqueta que les da nombre.

Subprogramas: Procedimientos, paquetes y funciones, almacenados en la BD y que se ejecutan en múltiples ocasiones. Los subprogramas se ejecutarán mediante una llamada.

Disparadores ("Triggers"): Bloques nominados que se almacenan en la BD y se ejecutan ante algún suceso.

Para dar nombre a un bloque se le pone una etiqueta antes del DECLARE . <<etiqueta>>

Para transformar un bloque en un procedimiento almacenado reemplazamos la palabra clave

DECLARE por CREATE OR REPLACE PROCEDURE nombre_procedimiento AS

Bases de datos.

DECLARE

v_ContBucle BINARY_INTEGER := 1;

BEGIN

LOOP

INSERT INTO temp_table (num_col)

VALUES (v_ContBucle);

v_ContBucle := v_ContBucler + 1;

EXIT WHEN v_ContBucle > 50;

END LOOP;

END;

TIPOS DE DATOS DE ORACLE

NUMBER:(escala,pesición)

CHAR:(tamaño)

VARCHAR2:(tamaño)

DATE: fecha

BINARY_INTEGER: Se usa para almacenar valores que sólo van a ser utilizados en cálculos y no se van a almacenar en la BD.

BOOLEAN: Pueden contener los valores TRUE,FALSE o NULL.

ROWID: Almacena las direcciones físicas de los registros de una tabla y son utilizados para búsquedas de alto rendimiento.

Para mostrar un valor cadena podemos utilizar:

DBMS_OUTPUT.PUT_LINE (cadena)

En caso de que el valor a mostrar no sea una cadena puedes utilizar la función TO_CHAR() para transformarlo.

El paquete DBMS_OUTPUT simplemente implementa una cola, si queremos que los datos aparezcan por pantalla tenemos que activar la opción SERVEROUTPUT mediante la orden de SQL*Plus.

Ejemplos de declaraciones de bloques

Suma de dos números

Bucle simple LOOP

Bucle del while

Bucle del for

Bucle del if- then-else

NOTA: INFORMACION RECOPILADA DE VARIOS MANUALES.

Autor:

Omar Jaramillo

NIVEL: 9° SISTEMAS

INSTITUCION: UNIANDES