Ampliar tablespace Oracle

Ampliar tablespace

Para ver los tablespaces con mayor ocupación en una BBDD yo siempre utilizo esta consulta:

set linesize 170
set pages 60
column  pct_used format 999.9       heading "%|Used" 
column  ts_name    format a33     heading "Tablespace Name" 
column  Mbytes   format 999,999,999,999    heading "MBytes" 
column  used    format 999,999,999,999   heading "Used" 
column  free    format 999,999,999,999  heading "Free" 
column  largest    format 999,999,999,999  heading "Largest" 
break   on report 
compute sum of Mbytes on report 
compute sum of free on report 
compute sum of used on report


select  nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN')) ts_name
, kbytes_alloc/1024      Mbytes
, (kbytes_alloc-nvl(kbytes_free,0))/1024     used
, nvl(kbytes_free,0)/1024      free
, ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used
, nvl(largest,0)/1024       largest
from (select sum(bytes)/1024   Kbytes_free
 ,  max(bytes)/1024  largest
 ,  tablespace_name
 from   dba_free_space
 group by tablespace_name)   a
, (select  sum(bytes)/1024  Kbytes_alloc
 ,  tablespace_name
 from  dba_data_files
 group by tablespace_name)   b
where a.tablespace_name (+) = b.tablespace_name 
order by 5,1;

Esta nos da los tablespaces ordenados por porcentaje de nivel de ocupación. Una vez que tenemos el tablespace que queremos ampliar tenemos dos posibilidades:

1- Ampliar un tablespace añadiendo espacio a un datafile que ya existe.
2- Creando un nuevo tablespace.

1- Ampliar un tablespace añadiendo espacio a un datafile que ya existe.
Para sacar los datafiles de los que consta un tablespace ejecutamos:

set linesize 140
set pages 130
column  FILE_NAME    format a100
select FILE_ID, FILE_NAME, BYTES/1024/1024, AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'NOMBRE_TABLESPACE';
Una vez que tenemos los datafiles que componen el tablespace, tendremos que ver en qué filesystem de ellos hay espacio suficiente para ampliar, veamos un ejemplo:
El tablespace tablespace_ejemplo tiene estos datafiles:
   FILE_ID FILE_NAME                                                         BYTES/1024/1024 AUT
---------- ----------------------------------------------------------------- --------------- ---
       104 /oracle_datos/dat_1/tablespace_ejemplo01.dbf                             1010 YES
       146 /oracle_datos/dat_6/tablespace_ejemplo02.dbf                             1500 NO
       147 /oracle_datos/dat_6/tablespace_ejemplo03.dbf                             1000 NO

pues bien, tendremos que ver si en los filesystems /oracle_datos/dat_6/ y /oracle_datos/dat_1/ hay espacio para ampliar. Para ello nada mas sencillo que hacer un

df -k /oracle_datos/dat_6/

y un

df -k /oracle_datos/dat_1/

Esto nos dirá si hay espacio en alguno de ellos y cuanto podemos ampliar.
por ejemplo para

$ df -k /oracle_datos/dat_6/
Filesystem            kbytes    used   avail capacity  Mounted on
/dev/vx/dsk/oracle_datos/dat_6
                     47185920 43087346 3842419    92%    /oracle_datos/dat_6/

Esto nos dice que tenemos el filesystem /oracle_datos/dat_6/ al 92% y tenemos 3842419 Kb disponibles, por lo tanto podríamos ampliar un datafile en este filesystem en 3,6 Gb (Pero lo dejaríamos lleno). Así que para ampliar el tablespace que nos ocupa a partir del datafile tablespace_ejemplo02.dbf en 500Mb harímos:

ALTER DATABASE  DATAFILE  '/oracle_datos/dat_6/tablespace_ejemplo02.dbf' RESIZE  2000M;

*Notad que en el tamaño ponemos el tamaño total es decir 1500Mb que tenía ya + 500Mb que queremos amppliar. Si nos confundiésemos y pusiésemos menos espacio del que tenía la BBDD nos diría:

ERROR en línea 1: ORA-03297: el archivo contiene datos utilizados más allá del valor RESIZE solicitado

2-Ampliar un tablespace añadiendo un nuevo datafile.
Para crear un nuevo datafile dentro del tablespace lo podremos ubicar en cualquier sitio y con cualquier nombre siempre, aunque sería conveniente seguir la política de la BBDD :).

ALTER TABLESPACE NOMBRE_TABLESPACE ADD DATAFILE '/oracle_datos/dat_6/tablespace_ejemplo04.dbf ' SIZE 3000M;

Con esto creamos un nuevo datafile de 3Gb en el filesystem /oracle_datos/dat_6/ perteneciente al tablespace "NOMBRE_TABLESPACE"