Información de todos los tablespaces que componen la base de datos

set linesize 132
set pagesize 200
–*****************************************************************************
SELECT tablespace_name,
SUM (mbtotal) / 1024 / 1024 mbtotal,
SUM ( mbtotal – mblibre) / 1024 / 1024 mbusado,
SUM (mblibre) / 1024 / 1024 mblibre,
round(((SUM ( mbtotal – mblibre) / 1024 / 1024) * 100) / (SUM (mbtotal) / 1024 / 1024), 2) Porc_ocu,
round(((SUM (mblibre) / 1024 / 1024) *100) / (SUM (mbtotal) / 1024 / 1024),2) Porc_libre
FROM (SELECT tablespace_name, bytes mbtotal, 0 mblibre
FROM dba_data_files
UNION ALL
SELECT tablespace_name, 0 mbtotal, bytes mblibre
FROM dba_free_space)
GROUP BY tablespace_name
order by 6
/

Creación del tablespace:

CREATE TABLESPACE TAB
DATAFILE
‘/PROD/data1/tab_PROD_01.dbf’ SIZE 16G AUTOEXTEND OFF
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK Off;

Borrar un tablespace

Primero debemos asegurarnos de que el contenido de los archives de datos no es necesario, o se ha movido a otro tablespace.

En Segundo lugar, debemos achicar el tamaño de cada datafile lo más que se pueda antes de proceder al borrado, ya que se corre el riesgo de que el espacio ocupado no se libere completamente en el sistema operativo.

SELECT /*+ rule */ ‘alter database datafile »’ || name || »’ resize 10M;’
FROM v$datafile
WHERE ts# = (SELECT TS# FROM v$TABLESPACE WHERE name = UPPER(‘&tbs_a_borrar’))

En tercer lugar, procedemos al borrado del tablespace.

DROP TABLESPACE tbs_a_borrar INCLUDING CONTENTS AND DATAFILES;

Reemplazar el nombre del tablespace si fuera necesario
Es posible, que algunos scripts referencien al tablespace que consideramos borrar, en este ejemplo, se trata de un HP-UX itanium, donde varios procesos (scripts) creaban tablas y/o índices en el tablespace que se quería borrar.
Lo que se hizo fue, buscar por patrones el nombre del tablespace, y reemplazar por otro tablespace existente en la base para tal fin.

Buscar los patrones en los scripts

grep -i TBS_A_BORRAR *.sql | awk -F: {‘print $2’} | awk {‘print $2’} | sort –u

Dependiendo de los patrones correr el siguiente sh

for k in `grep -i TBS_A_BORRAR *.sql | awk -F: {‘print $1’}`
do
sed ‘s/tbs_a_borrar/tbs_existente/g’ $k > $k.tmp
cp $k.tmp $k
rm -f $k.tmp
done

Lo que hace el sh es, por cada archivo sql que contiene el patrón de búsqueda, reemplaza el patrón buscado (tbs_a_borrar)por la cadena especificada (tbs_existente), el resultado del reemplazo lo envía a un archivo temporal que se llama igual al archivo seguido de “.tmp”.
Luego se copia este nuevo archivo, a su nombre original, y por último se borra el archivo temporal.

Redimensionar un tablespace

Primero vemos cuantos datafiles conforman el tablespace, y que tamaño tienen, para en base a esta información, añadir uno nuevo, siguiendo el estándar de nombres, y el tamaño apropiado.

Set lines 200
Set pages 999
Col espacio format 9999999
Col file_name format a50
–**************************************************************
Select /*+ parallel (a,4) */ bytes/1024/1024 espacio,file_name
From Sys.Dba_Data_Files a
Where Tablespace_Name = UPPER(‘&tbs_a_dimensionar’)
Order By File_Name;

FILE SYSTEM

ALTER TABLESPACE tbs_a_dimensionar ADD DATAFILE ‘/PROD/oradata01/tbs_PROD_02.dbf’ Size 16G Autoextend Off;

ASM

ALTER TABLESPACE tbs_a_dimensionar ADD DATAFILE ‘+DG_PROD_1/prod/oradata01/indx_02_prod.dbf’ SIZE 16G AUTOEXTEND OFF;

TABLESPACE TEMPORAL

ALTER TABLESPACE TEMP ADD TEMPFILE ‘/PROD/oradata01/temp_02_PROD.dbf’ Size 1G Autoextend Off;

Resize a un tablespace

También podemos modificar un datafile existente, de la siguiente manera:

ALTER DATABASE DATAFILE ‘/PROD/oradata01/temp_02_PROD.dbf’ RESIZE 3G;

Mover datafiles de un FS a otro

En los ambientes de DW es muy común que las BD sean de gran tamaño, superior a los 10Tb, y en determinadas ocasiones hay que ordenar la estructura de FS, y como DBA tenemos la tarea de liberar todo un FS para ocupar uno nuevo.
Sabemos que un FS puede tener datafiles de varios tablespaces, de hecho, esto es deseable. Y también sabemos que la disponibilidad de la base implica que toda re-estructuración sea hecha en caliente.
A través de esta consulta, obtenemos una salida con el paso a paso necesario para liberar en este caso el FS data1 de la base BASE_A; y traspasarlo por completo al data2 de la misma base.

1. SQL: Sacamos de línea al tablespace involucrado.
2. Por cada datafile del mismo tablespace hacemos:
a. SIST OPERATIVO: Verificamos si el datafile está siendo utilizado en el sistema operativo, si lo está, esperar que sea liberado o si podemos, matamos la sesión que lo está utilizando.
b. SIST OPERATIVO: Verificamos si existe el mismo nombre de datafile en el nuevo destino. Este punto parace sin importancia, pero créanme! En bases con miles de datafiles, a menudo nos encontramos con nombres de dbf repetidos en distintos FS. Una vez que comprobamos que NO existe, continuamos con el siguiente paso. (Si existe, tendremos que renombrar el datafile a traspasar)
c. SIST OPERATIVO: Movemos el archivo del data1 al data2.
d. SQL: Renombramos el datafile para que la BD se entere de la nueva ubicación.
3. SQL: Ponemos en línea el tablespace.

Select ‘alter tablespace ‘ || Tablespace_Name || ‘ offline;’ A1_Sql_Tbs_Offline,
‘/etc/fuser ‘ || File_Name A2_Sop_Fuser,
‘ll ‘ || Replace(File_Name, ‘data1’, ‘data2’) A3_Sop_Existe,
‘mv ‘ || File_Name || ‘ ‘ || Replace(File_Name, ‘data1’, ‘data2’) A4_Sop_Mv_Dbf,
‘alter database rename file »’ || File_Name || »’ to »’ ||
Replace(File_Name, ‘data1’, ‘data2′) || »’;’ A5_Sql_Rename_Dbf,
‘alter tablespace ‘ || Tablespace_Name || ‘ online;’ A6_Sql_Tbs_Online,
File_Id,
File_Name,
Tablespace_Name,
Bytes / 1024 / 1024 / 1024 Tamanio_Gb
From Dba_Data_Files
Where File_Name Like ‘%/BASE_A/data1%’
Order By Tablespace_Name, File_Name

Mover el tablespace system

El siguiente procedimiento, es útil para mover el datafile a otra ubicación conservando el mismo nombre, o simplemente renombrarlo en la misma ubicación.

Caso A)
SQL> shutdown immediate;
SQL> alter database mount;
SQL> alter database rename file ‘/PROD/oradata01/system_01_PROD.dbf’ to ‘/PROD/oradata02/system_01_PROD.dbf’;
SQL> alter database open;

Caso B)
SQL> shutdown immediate;
SQL> alter database mount;
SQL> alter database rename file ‘/PROD/oradata01/system_01_PROD.dbf’ to ‘/PROD/oradata01/system_01_TEST.dbf’;
SQL> alter database open;

Tablespaces por Gisela Velazco se encuentra bajo una Licencia Creative Commons Atribución-NoComercial-SinDerivadas 3.0 Unported.

4 Comentarios

Unknown el septiembre 2, 2013 a las 8:14 am

como habilito el envio de emails en oracle, deseo verificar el tamaño de los tablespace con un SP luego con un job programarlo y enviarlo por email???
Gisela Velazco el marzo 18, 2014 a las 7:28 am

Hola Jonathan, puedes hacerlo con la utilidad utl_smtp provista por oracle.
Saludos!
Unknown el marzo 19, 2014 a las 1:51 pm

huuy ya fue! ahora otra pregunta tengo clusterware de oracle mi filesystem donde recide /crs se incrementa hay alguna forma de eliminar o de purar para reducir su tamaño?
Unknown el marzo 22, 2017 a las 3:14 pm

Les pido que me ayuden con esta consulta que tengo:
Crear un ejemplo como se mueve las tablas de un tablespace a otro y que objeto se daña al realizar el movimiento?

TABLESPACES