CONOCIMIENTOS BÁSICOS DE ORACLE

INTRODUCCIÓN

Tablespace (espacio de tablas)

Una base de datos se divide en unidades lógicas denominadas TABLESPACES. 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…) que se van a crear en la base de datos bajo el tablespace indicado (tablas, secuencias…). Un espacio de tablas puede pertenecer sólo a una BD.
Datafile (fichero de datos)

Un datafile es la representación física de un tablespace. Son los "ficheros de datos" donde se almacena la información físicamente. Un datafile 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 datafile tiene un tamaño predefinido en su creación (por ejemplo 100Mb) y este puede ser alterado en cualquier momento. Cuando creemos un datafile, 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.
USUARIO

Es un nombre definido en la base de datos que se puede conectar a ella y acceder a determinados objetos según ciertas condiciones que establece el administrador.
Los objetos del diccionario de datos a los que un usuario puede acceder se encuentran en la vista DICTIONARY, que es propiedad del usuario SYS.
CONEXIONES TNS Y CONFIGURACION DEL AGENTE

Las herramientas "Net Configuration Assistant" y "Net Manager" nos permiten configurar la red o los archivos de configuración del "SQL Net". "Fundamentos de Administración de las Bases de Datos del Oracle 2", pero debemos saber cómo iniciar estos programas. La herramienta "Net Configuration Assistant" nos permite configurar los receptores individuales, el acceso a los métodos para dar nombres y los servicios locales. Vamos a configurar los archivos de nombre "tns" u otros tipos de archivos de configuración de métodos para dar nombres.

PRIVILEGIOS DEL SISTEMA

Los roles de sistema se utilizan para distribuir la disponibilidad de los comandos del sistema utilizados para gestionar la BD. Los privilegios más comunes están en la siguiente tabla. En ella se distinguen entre privilegios de manejo de objetos y de gestión de la BD. La palabra clave ANY significa que ese usuario tiene el privilegio para todos los esquemas en la BD.


OBJETIVOS:
OBJETIVO GENERAL:

Tener conocimientos de Tablespace, Datafile, conexiones tns y configuracion del agente, privilegios del sistema y Usuarios para poder manipular de manera correcta nuestro sistema de gestión de base de datos ORACLE.

OBJETIVO ESPECIFICO:
Conocer de manera detallada como funcionan Tablespace, Datafile, conexiones tns y configuracion del agente, privilegios del sistema y Usuarios con la finalidad de generar nuestras propias BD de acuerdo a las necesidades requeridas por los clientes.

TABLESPACES
El Tablespace System
Cuando se crea una base de datos es obligatorio crear un tablespace inicial en el que se van a crear los usuarios SYS y SYSTEM automáticamente. Estos usuarios son los que tienen la información necesaria para que funcione nuestra base de datos y podamos hacer todo tipo de operaciones como, por ejemplo, crear nuevos usuarios o crear nuevos tablespaces y tablas en esos nuevos tablespaces.
Este tablespace inicial se llama por defecto SYSTEM. Es una pieza clave para un buen funcionamiento de la base de datos ya que en él residen todos los objetos de los usuarios SYS y SYSTEM.
Es muy recomendable crear al menos otro tablespace nuevo distinto al SYSTEM. Así, todos los nuevos usuarios que creemos en nuestra base de datos, junto con todas sus tablas e índices se almacenarán en un tablespace diferente a SYSTEM. Se realiza esta separación para evitar que se bloquee toda la base de datos si ocurre algo grave en el tablespace SYSTEM. Suele ser habitual que para nuestras aplicaciones creemos usuarios y tablas en las que introducimos información y que sin darnos cuenta se llene de información el tablespace en el que están estas tablas. Si no hemos sido previsores, podemos haber llenado el tablespace SYSTEM con lo que es posible que se paralice toda la base de datos.
Manipulando Tablespaces
Ahora que nos hemos hecho una idea acerca de qué es un tablespace, vamos a realizar sobre él las manipulaciones básicas.
Partimos de una base de datos creada y levantada. Nos conectaremos a la misma con el usuario SYSTEM y su contraseña. La contraseña del usuario SYSTEM al crear la base de datos es, por defecto, MANAGER. Como medida de seguridad se recomienda cambiarla cuanto antes. Por lo tanto nos conectaremos bien al SqlPlus mediantesqlplus system/manager, o bien al server manager mediante el comando svrmgrl system/manager.
Crear un Tablespace.
En primer lugar vamos a crear un tablespace llamado Prueba. Esto lo podemos hacer por ejemplo desde el SQLPLUS conectados como system.
Create tablespace prueba datafile '/users/oradata/orcl/prueba01.dbf' size 100M;
Con esta sentencia estamos creando en nuestra base de datos un tablespace nuevo llamado "prueba" y que está formado físicamente por un fichero (datafile) llamado prueba01.dbf de 100 Mbytes y que está en el directorio "/users/oradata/orcl". Esta sentencia crea físicamente dicho fichero.
Aumentar de tamaño un Tablespace.
Para aumentar el tamaño de un tablespace que se nos ha quedado ya pequeño, tenemos varias posibilidades. La primera de ellas es crear un nuevo datafile y asignárselo al tablespace que queremos aumentar. Esto lo podemos hacer con la instrucción siguiente.
Alter tablespace prueba add datafile '/users/oradata/orcl/prueba02.dbf' size 50M;
Con esta sentencia hemos creado un nuevo fichero físico en nuestro directorio /users/oradata/orcl de 50 Mbytes de tamaño y se lo hemos asignado al tablespace "prueba".
Otra posibilidad es ampliar el tamaño de uno de los ficheros físicos o datafiles que forman el tablespace. Esto lo podemos hacer fácilmente con la siguiente instrucción:
Alter datafile '/users/oradata/orcl/prueba01.dbf' resize 150M;
Con esta sentencia lo que hacemos es aumentar el datafile que forma parte de nuestro tablespace en 50 Mbytes.
Tanto en la instrucción de creción como en la de aumentar el tamaño de un tablespace se puede observar fácilmente cómo un datafile pertenece solamente a un tablespace ya que en la propia sentencia se crea el fichero físico o datafile.
Borrando un tablespace.
Para eliminar un tablespace de la base de datos se debe utilizar la sentencia:
Drop tablespace prueba;

Como ver el tamaño de la tablas de un esquema.
select segment_name as table_name, sum(bytes)/(1024*1024) as table_size_meg
from user_extents
where (segment_type='TABLE')
group by segment_name
order by sum(bytes)/(1024*1024) desc
Como ver el tamaño de los tablespace y el espacio utilizado.
SELECT tablespace_name, round(BYTES/1024/1024,0) tamaño, round(user_BYTES/1024/1024,0) tamaño_Usado
FROM dba_data_files b
WHERE tablespace_name NOT LIKE 'TEMP%'
Como ver el espacio libre de los tablespaces de oracle
Para ver el espacio libre que queda en un tablespace tenemos que mirar en la tabla dba_free_space
SELECT tablespace_name,
ROUND(sum(bytes)/1024/1024,0)
FROM dba_free_space
WHERE tablespace_name NOT LIKE ‘TEMP%’
GROUP BY tablespace_name;
Con esta consulta obentemos el nombre del tablespace y el espacio en Megas libre
Para ver el espacio total en un tablespace tenemos que mirar en la tabla dba_data_files
SELECT tablespace_name,
round(sum(BYTES/1024/1024),0)
FROM dba_data_files b
WHERE tablespace_name NOT LIKE ‘TEMP%’
GROUP BY b.tablespace_name;
Con esta consulta obentemos el nombre del tablespace y el espacio total en Megas que puede llegar a tener
Como enviar un correo en pl/sql
Como hemos dicho en el objetivo de este articulo, cuando el tablespace se esté quedando sin espacio libre, se recibirá un correo.
Vamos a proporcionar un procedimiento para poder enviar un correo.
CREATE OR REPLACE PROCEDURE SEND_MAIL(SENDER IN VARCHAR2, RECIPIENT IN VARCHAR2, SUBJECT IN VARCHAR2, MESSAGE IN VARCHAR2) IS
– SENDER: direccion de correo de quien envia el mail
– RECIPIENT: dirreción de correo a la que va dirigida el mail
– SUBJECT: Es el asunto del correo
– ESSAGE: es el texto del mensaje
mailhost CONSTANT VARCHAR2(30) := ‘mail.server.es’; — servidor de correo , sustituir cadena por una valida
mesg VARCHAR2(1000); — texto del mensaje
mail_conn UTL_SMTP.CONNECTION; — conexion con el servidor smtp
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg := ‘Date: ‘ ||
TO_CHAR( SYSDATE, ‘dd Mon yy hh24:mi:ss’ ) || CHR(13) || CHR(10) ||
‘From: <’|| Sender ||’>’ || CHR(13) || CHR(10) ||
‘Subject: ‘|| Subject || CHR(13) || CHR(10)||
‘To: ‘||Recipient || CHR(13) || CHR(10) || ” || CHR(13) || CHR(10) || Message;
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, Sender);
utl_smtp.rcpt(mail_conn, Recipient);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20004,SQLERRM);
END send_mail;
Sustituir mail.server.es por un servidor smtp valido
Procedimiento para controlar el espacio libre de los tablespaces
A través de este procedimiento comprobamos que queda más de un porcentaje establecido libre en el tablespace con respecto a su espacio total
Si el espacio libre es menor al limite establecido ( portentaje ) del total del tablespace se envia un correo utilizando el procedimiento que se ha explicado en el punto anterior.
CREATE OR REPLACE PROCEDURE ALERTA_ESPACIO (limite number) IS
– CREAMOS EL CURSOR CON EL NOMBRE DE LOS TABLESPACES
– Y ESPACIO LIBRE
CURSOR c_espacio_libre IS
SELECT tablespace_name,
ROUND(sum(bytes)/1024/1024,0)
FROM dba_free_space
WHERE tablespace_name NOT LIKE ‘TEMP%’
GROUP BY tablespace_name;
– CREAMOS EL CURSOR CON EL NOMBRE DE LOS TABLESPACES
– Y ESPACIO total
CURSOR c_espacio_total IS
select tablespace_name,
round(sum(BYTES/1024/1024),0)
FROM dba_data_files b
WHERE tablespace_name NOT LIKE ‘TEMP%’
GROUP BY b.tablespace_name;

– DEFINIMOS LAS VARIABLES PARA METER EL CONTENIDO DEL CURSOR
c_nombre VARCHAR2(20);
c_libre NUMBER(10);
c_total NUMBER(10);
v_bbdd VARCHAR(20);
BEGIN

– OBTENEMOS EL NOMBRE DE LA BASE DE DATOS
SELECT name into v_bbdd from v$database;
– ABRIMOS EL CURSOR Y NOS POSICIONAMOS EN LA PRIMERA LINEA
OPEN c_espacio_libre;
OPEN c_espacio_total;
FETCH c_espacio_libre INTO c_nombre,c_libre;
FETCH c_espacio_total INTO c_nombre,c_total;
– EN CASO DE QUE EXISTA RESULTADO REALIZAMOS LAS COMPROBACIONES DE ESPACIO
WHILE c_espacio_libre%found
LOOP

– Comprobacion del tablespace ES MENOR DE limite MEGAS
IF (c_libre * 100) / c_total < limite THEN
send_mail(‘dedireccion@orasite.es’,'paradirecion@orasite.es’,
‘ALERTA DE ESPACIO EN BASE DE DATOS ‘ || v_bbdd,
‘El tablespace con nombre: ‘ || c_nombre || ‘ se esta quedando sin espacio’ ||chr(10)|
‘El tamaño restante es de: ‘ || c_libre || ‘ Megas’);
END IF;
FETCH c_espacio_libre INTO c_nombre, c_libre;
FETCH c_espacio_total INTO c_nombre,c_total;
END LOOP;
CLOSE c_espacio_libre;
CLOSE c_espacio_total;
END;
/

Este procedimiento recibe un parametro, que será el porcentaje que queramos comprobar. Un valor normal sería 10, de esta forma comprobará que el espacio libre sea mayor de un 10 por ciento del tamaño total del tablespace.
En caso de que no sea mayor que ese límite puesto, se enviará un mail. Modificar las direcciones de correo, por direcciones de correo validas.
automatizar la tarea de comprobacion de tablespaces
Esta tarea se puede automatizar poniendo un job ( tarea ) en la base de datos y que compruebe cada x tiempo si los tablespaces se han llenado.
Si no tenemos ningún job en la base de datos, antes de poner un job tenemos que asegurarnos que el valorjob_queue_processes es mayor que 0.

TABLESPACES ONLINE Y OFFLINE
Un tablespace puede estar en dos estados: Online y Offline. Que un tablespace esté online significa que está disponible para operar en él, mientras que si está offline quiere decir que no se puede utilizar. Cuando creamos un tablespace, se crea en estado online y, por lo tanto, podemos crear en dicho tablespace objetos como índices, tablas, etc.
¿Cómo sabemos en qué estado se encuentran nuestros tablespaces?.
Existe una vista que nos da información sobre los tablespaces de nuestra base de datos. Esta vista es la dba_tablespaces. Consultándola podemos conocer qué tablespaces tenemos en nuestra base de datos y en qué estado se encuentran.
select tablespace_name, status from dba_tablespaces;
¿Para qué queremos poner un tablespace offline?.
Hay que tener en cuenta que cuando un tablespace está offline, no se puede acceder a ningún objeto que se encuentre en él, es decir, que si en el tablespace hay tablas, no se podrá hacer consultas ni inserciones ni modificaciones de estas tablas, sin embargo, el resto de los objetos que se encuentran en otros tablespaces de la base de datos si están accesibles. Por lo tanto, pondremos un tablespace offline en general para realizar tareas administrativas.
• Para poder hacer una copia de seguridad del tablespace estando completamente seguros de que nadie está modificando los objetos del tablespace y que no quedan transacciones pendientes sin terminar y que pueden modificar estos objetos.
• Para poder actualizar una aplicación que se basa en los objetos de este tablespace sin que ningún usuario pueda modificar los datos en medio de la actualización.
En un tablespace puede haber objetos de varios tipos, como hemos indicado. Si en un tablespace existen segmentos de rollback activos, no se puede poner offline, primero hay que desactivar los segmentos de rollback activos del tablespace.
¿Cómo sabemos los rollback segments que existen en un tablespace y su estado?. Muy sencillo, con la siguiente sentencia:
select rollback_segment, status, tablespace_name from dba_rollback_segs;
Así podremos ver todos los rollback que tenemos, en qué estado se encuentran (online, offline) y en qué tablespace están. Si comprobamos que en el tablespace que vamos a poner offline tenemos algún segmento de rollback online (activo), debemos ponerlo offline antes que el tablespace. Para desactivar un segmento de rollback, ejecutaremos la siguiente sentencia desde el SqlPlus o desde el server manager.
alter rollback segment nombre_de_segmento offline;
Cuando ya no queden segmentos de rollback en estado online en nuestro tablespace, ya podremos desactivarlo para que no se pueda acceder a él.
alter tablespace nombre_de_tablespace offline;
Finalmente, cuando terminemos nuestras tareas administrativas sobre dicho tablespace, ya podemos activarlo para que todos sus objetos vuelvan a estar accesibles por los usuarios.
alter tablespace nombre_de_tablespace online;
Por supuesto, no debemos olvidar que si hemos tenido que desactivar algún segmento de rollback que se encontraba en nuestro tablespace, ahora deberemos volver a activarlo.
alter rollback segment nombre_de_segmento online;
Una curiosidad sobre los tablespaces que no están disponibles (offline), es que, como ya hemos comentado, no se pueden realizar consultas ni modificaciones ni inserciones en los datos de las tablas que están en ellos, pero si que se pueden eliminar objetos de dicho tablespace, que no es lo mismo que borrar datos de objetos del tablespace.
También es muy habitual que en el diseño de las bases de datos, se creen tablespaces para almacenar los índices de la aplicación y otros distintos para almacenar las tablas o datos. En estos casos, si desactivamos el tablespace en el que se encuentran los índices, podemos seguir accediendo a las tablas y realizar consultas sobre ellas porque su tablespace está accesible.
Por otro lado, es posible que si en servidor Oracle se encuentra con graves problemas para escribir en un tablespace, al cabo de varios intentos lo ponga automáticamente offline.
Tablespaces Read Only
Cuando creamos un tablespace, podemos crear en él todos los objetos que queramos y acceder a ellos y eliminarlos y también consultar los datos de las tablas que se encuentren en este tablespace, así como borrar, insertar y modificar estos datos. Existe la posibilidad de poner un tablespace en un estado en el cual, solamente se pueden consultar los datos de los objetos, no se puede ni borrar ni insertar nada en ellos.

¿Para qué me viene bien un tablespace read only?.
La principal ventaja de un tablespace read only es que, como no se pueden modificar los datos que en él se encuentran, no hace falta hacer backup del mismo. Dependiendo de las apliaciones que tengamos en nuestra base de datos nos puede interesar tener tablespaces read only o no. Por ejemplo, si tenemos una aplicación en la que se pueden consultar cientos de fotos de animales salvajes o de paisajes, podríamos crear un tablespace en el que introducir estas imágenes y luego ponerlo read only.
Generalmente un tablespace de estas características, que sirve de almacenamiento de fotos o temas similares, suele ocupar mucho espacio, por lo que hacer un backup del mismo todos los días puede resultar muy costoso en tiempo y espacio. Además, si no se modifican nunca estas fotos no tiene mucho sentido hacer copia de seguridad del mismo, y no solo eso, podríamos incluso almacenar dicho tablespace en un CDROM en vez de ocupar espacio en disco.
Para poner un tablespace en estado read only, simplemente debemos ejecutar la siguiente instrucción:
alter tablespace nombre_de_tablespace read only;
Como hemos indicado, en un tablespace read only solo se pueden realizar consultas de los datos, por lo tanto, si en el instante de ejecutar esta sentencia se están realizando modificaciones o inserciones o borrado de datos, el servidor espera hasta que acaben para poner el tablespace en estado read only. Para ver si ha quedado en estado read only, simplemente ejecutamos la misma select que al principio para ver la información general de los tablespaces:
select tablespace_name, status from dba_tablespaces;
Si por algún motivo necesitamos modificar los datos que se encuentran almacenados en espace un tablespace read only, simplemente deberemos ponerlo en primer lugar en estado read write y una vez realizada la modificación, volver a ponerlo en su estado read only. La sentencia que debemos ejecutar será:
alter tablespace nombre_de_tablespace read write;
Tenemos un concepto que debe quedar claro. Un tablespace read only no necesita backup, y por tanto, recovery, pero, esto no hay que tomarlo al pie de la letra. Siempre hay que hacer al menos un backup. En primer lugar creamos un tablespace vacío en el que iremos metiendo poco a poco toda la información que nos interesa que, como en el caso que hemos supuesto anteriormente, pueden ser varias tablas que almacenan fotos de animales y paisajes. Cuando ya no vamos a crear nuevas imágenes es cuando ponemos el tablespace read only, pero ahí si debemos hacer una copia de seguridad, backup, y como ya no vamos a tocar nunca más este tablespace será la única. Si por algún motivo decidimos poner este tablespace otra vez read write para crear o borrar datos, después de volver a ponerlo read only deberemos hacer un backup de los nuevos datos.
También hay que diferenciar dos ideas. Por un lado hemos dicho que en un tablespace read only no se pueden modificar, ni insertar ni borrar datos de sus tablas. Sin embargo y, al igual que en los tablespaces offline, si se pueden borrar objetos enteros del tablespace, como por ejemplo un índice o una tabla.
Tablespaces Temporales
Un tablespace temporal es aquél en el que solamente puede haber objetos temporales. No se pueden crear en él objetos permanentes como pueden ser los índices, las tablas o los segmentos de rollback. Están especialmente preparados para optimizar las operaciones en las que se lleven a cabo ordenaciones. Por lo tanto está muy recomendado tener al menos un tablespace temporal en cada base de datos. Algunas de las operaciones que implican realizar ordenaciones son, las selects que tienen group by, las que tienen order by, la creación de índices y analizar índices para calcularles las estadísticas. En todos estos casos, cuando para realizar la ordenación el servidor no encuentra espacio suficente libre en la memoria, utiliza el tablespace temporal. Los rendimientos son muy superiores comparándolos con los tiempos que se emplearía en realizar las ordenaciones en tablespaces normales. Esto se debe a que el mecanismo que se utiliza para reservar y desreservar el espacio en los tablespaces temporales es muy distinto que en los normales ya que está orientado a objetos que crecen mucho y rápido y que a continuación disminuyen totalmente su tamaño y desaparecen.
Para crear un tablespace temporal simplemente hay que añadir la palabra TEMPORARY a la instrucción utilizada para crear tablespaces normales. Siguiendo el ejemplo indicado en la creación de tablespaces, podríamos tener lo siguiente:
Create tablespace prueba datafile '/users/oradata/orcl/prueba01.dbf'
size 100M temporary;
Para indicar a un usuario de base de datos que sus ordenaciones debe hacerlas en un determinado tablespace temporal, hay que lanzar una sentencia como la que sigue.
Alter user nombre_de_usuario temporary tablespace nombre_de_tablespace;
Y para conocer qué usuarios existen en nuestra base de datos y cual es el tablespace temporal que utilizan, podemos consultar la base de datos de la siguiente manera:
Select username, temporary_tablespace from dba_users;
Y finalmente, si queremos conocer qué tablespaces tenemos y cuáles son temporales y cuales son permanentes, podemos consultar la vista que nos da la información sobre los tablespaces, es decir, la vista dba_tablespaces;
Select tablespace_name, contents from dba_tablespaces;
Como nota final apuntaremos que un tablespace permanente puede pasar a temporal y que uno temporal puede pasar a permanente.
DATAFILES
Los datafiles son los ficheros físicos en los que se almacenan los objetos que forman parte de un tablespace. Un datafile pertenece solamente a un tablespace y a una instancia de base de datos. Un tablespace puede estar formado por uno o varios datafiles. Cuando se crea un datafile, se debe indicar su nombre, su ubicación o directorio, el tamaño que va a tener y el tablespace al que va a pertenecer. Además, al crearlos, ocupan ya ese espacio aunque se encuentran totalmente vacíos, es decir, Oracle reserva el espacio para poder ir llenándolo poco a poco con posterioridad. Por supuesto, si no hay sitio suficiente para crear un fichero físico del tamaño indicado, se producirá un error y no se creará dicho fichero.

Cuando se van creando objetos en un tablespace, éstos físicamente se van almacenando en los datafiles asignados a dicho tablespace, es decir, cuando creamos una tabla y vamos insertando datos en ella, estos datos realmente se reparten por los ficheros físicos o datafiles que forman parte del tablespace. No se puede controlar en qué fichero físico se almacenan los datos de un tablespace. Si un tablespace está formado por 2 datafiles y tenemos una tabla en ese tablespace, a medida que vamos insertando filas éstas se almacenarán en cualquiera de los dos datafiles indistintamente, es decir, unas pueden estar en un datafile y otras en otro.
El espacio total disponible en un tablespace es lógicamente la suma de los tamaños que ocupan los ficheros físicos o datafiles que lo forman. Como hemos indicado estos datafiles, al crearlos, están totalmente vacíos, simplemente es un espacio reservado y formateado por Oracle para su uso. A medida que se van creando objetos en ellos como tablas, índices, etc y se van insertando registros en estas tablas, los datafiles se van llenando o, lo que es lo mismo, el tablespace se va llenando.


Creación y Manipulación

La creación de datafiles está estrechamente relacionada con el tablespace al que va a pertenecer. Tenemos varias formas de crear datafiles. Cada vez que se crea un tablespace nuevo, hay que indicar obligatoriamente cual es el datafile que va a pertenecer a dicho tablespace y, en ese momento, se crea tanto el tablespace como su datafile. También se pueden añadir datafiles nuevos a un tablespace que ya existe. Esto se suele hacer cuando un tablespace se está llenando y está a punto de llegar a su capacidad máxima. Al añadir un datafile a un tablespace, se aumenta el espacio disponible en dicho tablespace en tantos megabytes como tenga el datafile nuevo recién creado.

Creación de un nuevo datafile de 50 megabytes junto con un nuevo tablespace:
Create tablespace nombre_tablespace datafile
'/users/oracle/orcl/nombre_datafile.dbf' size 50M;
Una vez creado este tablespace, si con el tiempo queremos añadirle espacio, lo podemos hacer creando un nuevo datafile y asignándoselo al tablespace:
Alter tablespace nombre_tablespace add datafile
'/users/oracle/orcl/nombre_datafile2.dbf' size 100M;

Con estas dos instrucciones hemos creado un tablespace nuevo en nuestra base de datos en el que caben 150 megabytes de información. Este espacio está formado físicamente por dos ficheros llamados nombre_datafile.dbf y nombre_datafile2.dbf que se encuentran en el directorio /users/oracle/orcl de nuestra máquina y que ocupan 50 y 100 Mbytes respectivamente.

Para conocer los datafiles que forman parte de nuestra base de datos, podemos consultar la vista dba_data_files en la que se nos indica por cada datafile o fichero de datos, a qué tablespace pertenece y cuanto espacio total tiene reservado. Es importante recalcar que el espacio que aparece en esta vista es el espacio total que ocupa el fichero físico y no el espacio utilizado de ese fichero, es decir, que si creamos un datafile de 50Mbytes y acto seguido consultamos esta vista, veremos que ocupa 50Mbytes a pesar de estar totalmente vacío. Este dato indica la cantidad de espacio que ocupa el fichero físico, la cantidad de información que podremos introducir en él.

select tablespace_name, file_name, bytes /1024/1024 from dba_data_files;
Tenemos también la posibilidad de aumentar el tamaño de un datafile, es decir, podemos conseguir que un tablespace tenga más sitio vacío aumentando uno o varios de los ficheros físicos que lo forman, en lugar de añadiéndole un nuevo fichero físico. Para aumentar el tamaño de un datafile, podremos utilizar la siguiente instrucción:

alter database datafile '/users/oracle/orcl/nombre_datafile.dbf' resize 100M;
Esta instrucción deja el datafile indicado con un tamaño de 100M, no es que se aumente en 100Mbytes. Esto es fácil de recordar, vale con pensar en que esta instrucción se utiliza también para disminuir el tamaño de un datafile que en un primer lugar lo creamos excesivamente grande. En esta instrucción no se pueden utilizar números negativos, por lo que parece claro que si ponemos un número queremos indicar que será el tamaño que queremos que tenga nuestro datafile. Es importante tener en cuenta que no siempre podemos disminuir el tamaño de un datafile. Los motivos serán explicados en temas más avanzados y tienen que ver con la forma que tiene Oracle de reservar el espacio dentro de los tablespaces y datafiles.

Existe una posibilidad de que Oracle aumente automáticamente el tamaño de sus datafiles cuando éstos se están llenando, para evitar así la intervención manual del administrador de la base de datos. Se puede hacer de varias formas, al crear el tablespace con el datafile, al añadir un nuevo datafile al tablespace o incluso en cualquier otro momento.

Para indicar que queremos que un datafile aumente automáticamente cuando añadimos un nuevo datafile a un tablespace existente podemos utilizar:
alter tablespace nombre_tablespace add datafile nombre_datafile size 100M
autoextend on next 250K maxsize 200M;

Con esta instrucción lo que estamos haciendo es añadir un nuevo datafile llamado nombre_datafile a nuestro tablespace nombre_tablespace con 100Mbytes de tamaño. Además, estamos indicando que queremos que aumente por si mismo cada vez que se llene y que aumente en bloques de 250 Kbytes cada vez. Finalmente le ponemos un tope al tamaño total que queremos que tenga nuestro datafile con la instrucción maxsize, por lo que una vez que llegue a 200 Mbytes, si se llena, no volverá a crecer más.

Para indicar en cualquier momento que queremos que un datafile no crezca más automáticamente, podemos utilizar:
alter database datafile nombre_datafile autoextend off;
Y para indicar en cualquier momento que un determinado datafile crezca automáticamente, la instrucción que ejecutaremos será:

alter database datafile nombre_datafile autoextend on next 1 M maxize 300 M;

Renombrando Datafiles

Existe la posiblidad de cambiarle el nombre a un datafile o de cambiarlo de directorio. Esta operación no consiste simplemente en ir al sistema operativo y cambiarle el nombre, ya que si hiciéramos eso, Oracle no se da cuenta de que hemos movido de sitio un datafile y cuando intenta acceder a información de ese datafile muestra mensajes de error indicando que no lo encuentra.
Hay que distinguir entre los datafiles del tablespace SYSTEM y el resto. Los datafiles del tablespace SYSTEM son especiales y no se pueden mover con la misma facilidad que los demás.
Renombrando datafiles que no son del tablespace SYSTEM
En primer lugar, hay que comprobar cual es nombre y el path completo del fichero a mover y el estado en que se encuentra dicho fichero. Para realizar esta comprobación podemos consultar la vista dba_data_files.
select file_name, status, bytes from dba_data_files;
En file_name se nos indica cual es el nombre del datafile que nos interesa, con todo su path, y además vemos cuanto ocupa. El campo status podremos comprobar si el datafile está disponible (available).
Ahora que sabemos cual es el path y nombre completo de nuestro datafile, tenemos que evitar que se realicen operaciones que modifiquen los datos de los objetos de nuestro tablespace, para que así consigamos tener el contenido del datafile estático. Esto se consigue poniendo el tablespace en estado read only, como se explicó en el tema de los tablespaces.
alter tablespace nombre_tablespace read only;

Para comprobar que realmente está nuestro tablespace en estado read only, podemos consultar la vista dba_tablespaces. En estos momentos, los usuarios de la base de datos, pueden acceder y modificar la información de cualquier tablespace que no sea el que estamos manipulando, en el cual, solamente podrán realizar operaciones de lectura, nunca inserciones ni modificaciones ni borrados de datos.

Es en este instante, cuando sabemos que no se está modificando el contenido de nuestro tablespace y, por lo tanto, de nuestro datafile, cuando debemos ir al sistema operativo y hacer una copia de nuestro datafile con el nuevo nombre y la nueva ubicación. Una vez copiado, comprobamos también desde el sistema operativo que el nuevo datafile ocupa el mismo espacio que el antiguo, para estar seguros de que no ha habido ningún problema en la copia.
Hasta ahora, no le hemos indicado a Oracle que hemos movido de ubicación o de nombre a uno de sus datafiles, para poder indicárselo, debemos asegurarnos que no hay ningún usuario utilizando el tablespace, ni siquiera en modo consulta. Por lo tanto, debemos deshabilitar el tablespace.

alter tablespace nombre_tablespace offline;
Y una vez deshabilitado, indicamos a Oracle el cambio de nombre o de ubicación:
alter database rename file 'viejo_datafile_con_path' to 'nuevo_datafile_con_path';
En estos momentos Oracle ya sabe que cuando tenga que buscar la información de ese datafile debe buscarlo en el nuevo path indicado y con el nuevo nombre. Por lo tanto, si lanzamos la select para ver los datafiles de la base de datos, es decir, la select de la vista dba_data_files, comprobaremos que ha cambiado la información antigua por la nueva. Ahora solamente nos queda activar el tablespace y permitir operaciones de lectura y escritura en él.
alter tablespace nombre_tablespace online;
alter tablespace nombre_tablespace read write;

Por supuesto, antes de realizar cualquier operación que implique modificación de las estructuras de la base de datos, como el renombrado de un datafile, se debe hacer un backup completo de la misma. Una vez realizada la operación también se recomienda hacer un nuevo backup.
Nota: hay que resaltar una vez más, que no se debe mover el datafile desde el sistema operativo sin haber puesto con anterioridad su tablespace offline. De no ser así, si alguien manipula datos durante el tiempo que tarda en hacerse la copia en el sistema operativo, Oracle detecta problemas e invalida el datafile, lo que va a provocar que haya que poner en práctica alguna estrategia de backup para recuperar el datafile invalidado.

Renombrando datafiles del tablespace SYSTEM

El tablespace SYSTEM es especial, por lo tanto, para manipular sus datafiles, hay que hacerlo también de manera especial. Nadie puede trabajar con la base de datos. Por ese motivo, se debe apagar la base de datos y levantarla pero sin abrirla. Los conceptos de apagar la base de datos y levantarla no son objeto de este manual por lo que simplemente se indicarán las instrucciones.

Primeramente se debe apagar o, más coloquialmente, tirar abajo la base de datos. Esto lo hacemos desde el Server Manager, no desde SqlPlus. Nos conectamos al Server Manager como el usuario administrador y con privilegios especiales:
svrmgrl
connect internal
shutdown;

Después de esperar a que se terminen las transacciones activas, las base de datos se apaga y podemos volver a levantarla, también desde el Sever Manager, pero sin abrirla, solamente montándola.
startup mount;
Con esta instrucción hemos levantado la base de datos pero no la hemos abierto, por lo que nadie, excepto otro administrador, puede estar manipulando sus objetos. Ahora podemos realizar la copia de los datafiles del tablespace SYSTEM al nuevo directorio o con el nuevo nombre. Comprobamos que tanto el fichero nuevo como el antiguo tengan el mismo tamaño y a continuación indicamos a Oracle que hemos movido el datafile de la misma manera que en el apartado anterior:
alter database rename file 'viejo_datafile_con_path' to 'nuevo_datafile_con_path';
Finalmente podemos levantar la base de datos para que pueda volver a ser utilizada por todos los usuarios:
alter database open;

GESTIÓN DE USUARIOS EN ORACLE.
Es un nombre definido en la base de datos que se puede conectar a ella y acceder a determinados objetos según ciertas condiciones que establece el administrador.
Los objetos del diccionario de datos a los que un usuario puede acceder se encuentran en la vista DICTIONARY, que es propiedad del usuario SYS.

DESC DICTIONARY;

Con la orden:
SELECT TABLENAME FROM DICTIONARY;
Se visualizan los objetos del diccionario de datos a los que se puede acceder.
Creación deusuarios:
CREATE USER NOMBRE_USUARIO
IDENTIFIED BY CLAVE_ACCESO
[DEFAULT TABLESPACE ESPACIO_TABLA]
[TEMPORARY TABLESPACE ESPACIO_TABLA]
[QUOTA {ENTERO {K | M} | UNLIMITED } ON ESPACIO_TABLA]
[PROFILE PERFIL];

DEFAULT TABLESPACE= Asigna a un usuario el tablespace por defecto para almacenar los objetos que cree. Si no se asigna ninguno, el tablespace por defecto es SYSTEM.
TEMPORARY TABLESPACE= Especifica el nombre del tablespace para trabajar temporales. Si no se especifica ninguno, el tablespace por defecto es SYSTEM.
QUOTA= Asigna un espacio en megabites o kilobites en el tablespace asignado. Si no se especifica esta cláusula, el usuario no tiene cuota asignada y no podrá crear objetos en el tablespace. Para tener espacio y acceso ilimitad a un tablespace es:

GRANT UNLIMITED TABLESPACE NOMBRE_TABLESPACE;

PROFILE= Asigna un perfil a un usuario.

Modificación de usuarios:

ALTER USER NOMBRE_USUARIO
IDENTIFIED BY CLAVE _ACCESO
[DEFAULT TABLESPACE ESPACIO_TABLA]
[TEMPORARY TABLESPACE ESPACIO_TABLA]
[QUOTA {ENTERO {K | M } | UNLIMITED } ON ESPACIO_TABLA
[PROFILE PERFIL];
Borrado de usuarios:

DROP USER USUARIO [CASCADE];

CASCADE= Suprime todos los objetos del usuario antes de borrarlo.

PRIVILEGIOS DEL SISTEMA
Los roles de sistema se utilizan para distribuir la disponibilidad de los comandos del sistema utilizados para gestionar la BD. Los privilegios más comunes están en la siguiente tabla. En ella se distinguen entre privilegios de manejo de objetos y de gestión de la BD. La palabra clave ANY significa que ese usuario tiene el privilegio para todos los esquemas en la BD. Hay que hacer notar que ANY y PUBLIC no son sinónimos.
Privilegio Capacidades
Manejo de Objetos ...
CREATE ANY INDEX Crear cualquier índice.
CREATE [PUBLIC] SYNONYM Crear sinónimos [públicos].
CREATE [ANY] TABLE Crear tablas. El usuario debe tener cuota en el espacio de tablas, o ha de tener asignado el privilegio UNLIMITED TABLESPACE.
CREATE [ANY] VIEW Crear vistas.
ALTER ANY INDEX Alterar cualquier índice.
ALTER ANY TABLE Alterar cualquier tabla
DROP ANY INDEX Borrar cualquier índice.
DROP ANY SYNONYM Borrar cualquier sinónimo.
DROP PUBLIC SYNONYM Borrar sinónimos públicos.
DROP ANY VIEW Borrar cualquier vista.
DROP ANY TABLE Borrar cualquier tabla.
SELECT ANY TABLE Efectuar selecciones de cualquier tabla o vista.
INSERT ANY TABLE Insertar en cualquier tabla o vista.
DELETE ANY TABLE Borrar filas de cualquier tabla o vista, y también truncar.
ALTER SESSION Alterar los parámetros de la sesión.
CREATE SESSION Conectarse a la BD.
Gestión de la BD ...
CREATE PROFILE Crear perfiles de usuario.
CREATE ROLE Crear roles.
CREATE ROLLBACK SEGMENT Creación de segmentos de rollback.
CREATE TABLESPACE Crear espacios de tablas.
CREATE USER Crear usuarios.
ALTER PROFILE Alterar perfiles existentes.
ALTER ANY ROLE Alterar cualquier rol.
ALTER ROLLBACK SEGMENT Alterar segmentos de rollback.
ALTER TABLESPACE Alterar espacios de tablas.
ALTER USER Alterar usuarios.
DROP PROFILE Borrar un perfil existente.
DROP ANY ROLE Borrar cualquier rol.
DROP ROLLBACK SEGMENT Borrar un segmento de rollback existente.
DROP TABLESPACE Borrar un espacio de tablas.
DROP USER Borrar un usuario. Añadir CASCADE si el usuario posee objetos.
ALTER DATABASE Permite una sentencia ALTER DATABASE.
GRANT ANY PRIVILEGE Otorgar cualquiera de estos privilegios.
GRANT ANY ROLE Otorgar cualquier rol a un usario.
UNLIMITED TABLESPACE Puede usar una cantidad de almacenamiento ilimitada.
DROP PROFILE Borrar un perfil existente.
rupo Comandos Auditados
CLUSTER Todas las sentencias que afecten a clusters.
DATABASE LINK Todas las sentencias que afecten a enlaces de BD.
EXISTS Todas las sentencias que fallen porque ya existe un objeto en la BD.
INDEX Todas las sentencias que afecten a índices.
NOT EXISTS Todas las sentencias que fallen porque un determinado objeto no existe.
PROCEDURE Todas las sentencias que afecten a procedimientos.
PROFILE Todas las sentencias que afecten a perfiles.
PUBLIC DATABASE LINK Todas las sentencias que afecten a enlaces públicos de BD.
PUBLIC SINONYM Todas las sentencias que afecten a sinónimos públicos.
ROLE Todas las sentencias que afecten a roles.
ROLLBACK SEGMENT Todas las sentencias que afecten a segmentos de rollback.
SEQUENCE Todas las sentencias que afecten a secuencias.
SESSION Todas las sentencias de acceso a la BD.
SYNONYM Todas las sentencias que afecten a sinónimos.
SYSTEM AUDIT Todas las sentencias AUDIT y NOAUDIT.
SYSTEM GRANT Todas las sentencias afecten a privilegios.
TABLE Todas las sentencias que afecten a tablas.
TABLESPACE Todas las sentencias que afecten a espacios de tablas.
TRIGGER Todas las sentencias que afecten a disparadores.
USER Todas las sentencias que afecten a las cuentas de usuarios.
VIEW Todas las sentencias que afecten a vistas.
Valor Descripción
NONE Deshabilita la auditoría
BD Habilita la auditoría, escribiendo en la tabla SYS.AUD$.
OS Habilita la auditoría, dejando al SO su gestión.
Vista Contenidos
DBA_ROLES Nombres de los roles y su estado del password.
DBA_ROLES_PRIVS Usuarios a los que han sido otorgados roles.
DBA_SYS_PRIVS Usuarios a los que han sido otorgados privilegios del sistema.
DBA_TAB_PRIVS Usuarios a los que han sido otorgados privilegios sobre objetos.
DBA_COL_PRIVS Usuarios a los que han sido otorgados privilegios sobre columnas de tablas.
ROLE_ROLE_PRIVS Roles que han sido otorgados a otros roles.
ROLE_SYS_PRIVS Privilegios de sistema que han sido otorgados a roles.
ROLE_TAB_PRIVS Privilegios de tabla que han sido otorgados a roles.
Privilegio Capacidades Otorgadas
SELECT Puede consultar a un objeto.
INSERT Puede insertar filas en una tabla o vista. Puede especificarse las columnas donde se permite insertar dentro de la tabla o vista.
UPDATE Puede actualizar filas en una tabla o vista. Puede especificarse las columnas donde se permite actualizar dentro de la tabla o vista.
DELETE Puede borrar filas dentro de la tabla o vista.
ALTER Puede alterar la tabla.
INDEX Puede crear índices de una tabla.
REFERENCES Puede crear claves ajenas que referencie a esta tabla.
EXECUTE Puede ejecutar un procedimieto, paquete o función.
Recurso Descripción
SESSIONES_PER_USER El número de sesiones concurrentes que un usuario puede tener en una instancia.
CPU_PER_SESSION El tiempo de CPU, en centenas de segundos, que una sesión puede utilizar.
CONNECT_TIME El número de minutos que una sesión puede permanecer activa.
IDLE_TIME El número de minutos que una sesión puede permanecer sin que sea utilizada de manera activa.
LOGICAL_READS_PER_SESSION El número de bloques de datos que se pueden leer en una sesión.
LOGICAL_READS_PER_CALL El número de bloques de datos que se pueden leer en una operación.
PRIVATE_SGA La cantidad de espacio privado que una sesión puede reservar en la zona de SQL compartido de la SGA.
COMPOSITE_LIMIT El número de total de recursos por sesión, en unidades de servicio. Esto resulta de un calculo ponderado de CPU_PER_SESSION, CONNECT_TIME,LOGICAL_READS_PER_SESSION y PRIVATE_SGA, cuyos pesos se pueden variar con el comando ALTER RESOURCE COST.
Parámetro Significado
Username Nombre del Usuario (Esquema)
Password Palabra clave de la cuenta. Puede ser asociada directamente a una cuenta del sistema operativo.
Default Tablespace Espacio de tablas por defecto en el que los objetos de este usuario serán creados. Esto no da al usuario derechos de crear objetos.
Temporary Tablespace El espacio de tablas en el que se almacenarán los segmentos temporales de las ordenaciones.
Quota Espacio máximo que puede ocupar en un espacio de tablas.
Profile Asigna un perfil al usuario. Los perfiles se utilizan para restringir el uso de recursos como el tiempo de CPU.
CONEXIONES TNS Y CONFIGURACION DEL AGENTE
Las herramientas "Net Configuration Assistant" y "Net Manager" nos permiten configurar la red o los archivos de configuración del "SQL Net". "Fundamentos de Administración de las Bases de Datos del Oracle 2", pero debemos saber cómo iniciar estos programas. La herramienta "Net Configuration Assistant" nos permite configurar los receptores individuales, el acceso a los métodos para dar nombres y los servicios locales. Vamos a configurar los archivos de nombre "tns" u otros tipos de archivos de configuración de métodos para dar nombres.
La función del programa es dar nombres a los descriptores de la cadena de conexión en el equipo cliente e interpretarlos mediante el proceso de listado en el Servidor de la Base de Datos. Esto es todo lo que realiza el programa.
Una herramienta un poco más interesante es la "Net Manager", la cual nos brinda una descripción general, unos perfiles y un nombramiento de servicios. Todos los nombres de servicio "tns" en ésta computadora se seleccionan como "tns". Aquí tenemos muchos otros métodos de nombramiento disponibles y aquí tenemos el receptor de la computadora local. Obviamente, no existe ningún nombre en la opción "Oracle Name Servers" porque no estamos utilizando los nombres del Oracle. Veamos brevemente algunas definiciones. Ésta es la representación "GUI" o gráfica de la configuración "tns" de ésta base de datos. Éste es el nombre del servicio y éste es el tipo de conexión que puede ser "Database Default" o predeterminado, como en éste caso, "Shared Server" o con un servidor compartido… o "Dedicated Server" o con un servidor dedicado, el cual escogimos porque ésta conexión, a través de ésta base de datos o depósito de datos, se utiliza para propósitos de administración.
No podemos apagar de manera remota una base de datos con una conexión de servidor compartido a través de un despachador. Éste es el nombre del host, éste es el puerto por el cual se desplaza y éste es el protocolo que utiliza. Hacemos clic en el botón "Advanced" y podemos adicionar otros elementos. Por ejemplo, podemos cambiar el tamaño de la unidad de datos de la sesión, comunicarnos con otras bases de datos… y cambiar el nombre de la instancia. Estos aspectos pertenecen al siguiente curso, así que no profundizaremos en éste tema. Éstas dos herramientas de configuración de red crean un grupo de archivos de configuración o archivos de configuración del "SQL Net", los cuales se ubican en el directorio raíz del Oracle. En éste caso, estos se ubican en el directorio "e:\oracle\ora92\network\admin". Éste es un archivo de perfiles y su función es dar la orden de utilizar los nombres "tns" como la convención de nombramiento local predeterminada. En otras palabras, ingresamos y al conectarnos desde ésta computadora cliente a un Servidor de Base de Datos, se da la orden de ingresar a éste archivo y buscar ésta descripción. Si usted tiene conocimientos acerca de las redes, los cuales desafortunadamente yo no poseo, sabrá que éste es un nombre de red, así que al realizar una conexión a una base de datos con el nombre de red "test", éste nombre se convierte con ésta configuración, la cual en realidad es una cadena de caracteres. Se nos indica que utilicemos éste protocolo y que nos conectemos a éste servidor por éste puerto, en ésta computadora y con éste nombre de servicio. Sólo se nos indica que vayamos a esa computadora y nos conectemos a ésta base de datos. Éste es el servidor equivalente especializado que ordena la conexión a un proceso del servidor dedicado. Esto significa que ésta base de datos en particular llamada "test" en ésta computadora está configurada en un servidor compartido y la estamos forzando a ser una conexión dedicada, pues queremos administrarla utilizando éste nombre de red para esa base de datos. Ésta es la configuración del archivo receptor en ésta computadora, el cual corresponde a un proceso que recibe los datos que intentan comunicarse con él, por ejemplo, las conexiones "tns" que se comunican con éste receptor desde otras computadoras. Estos se comunican con ésta base de datos mediante éste nombre de host, es decir, el nombre de ésta computadora y a través de éste puerto. Aquí tenemos la definición de cada una de las bases de datos utilizadas por éste receptor. El archivo de configuración del "SQL Net" y los archivos de nombre "tns" son usualmente creados para administrar los elementos mediante las herramientas "Net configuration Assistant" y "Net Manager" del Oracle. Regresamos al Explorer y tenemos los archivos "SNMP", "RO" y "RW", los cuales son archivos de configuración para el agente del Oracle. Generalmente, no modifico estos archivos a menudo aunque es muy útil conocer que al crear una nueva base de datos en la computadora local, ésta se debe configurar mediante el agente. La manera más fácil de hacerlo consiste simplemente en eliminar estos dos archivos, detener y reiniciar el agente en el panel de servicios. De éste modo se rescribirán los dos archivos de configuración.