Consulta de espacios en datafiles y tablespaces.

Consulta de espacios en datafiles y tablespaces. 

A continuación se indican algunos comandos de administración de los espacios que alojan los datos de la base de datos, en los datafiles.

Esta revisión debe ser continua y permanente, del diario, y se deben de guardar registros del crecimiento a lo largo de los periodos vitales de la empresa, esto debido a que hay que recordar que nos indicará de qué manera hay que ir incrementado los espacios de datafiles y tablespaces en la base de datos.

Conformamos esta información de vital importancia ya que nos indica cómo crece la base de datos, y entonces podremos decidir cómo agregar más espacios para el almacenamiento de datos.

Consulta compacta de espacio libre

 SELECT a.tablespace_name,
       b.size_MB,
       a.free_MB,
       Trunc((a.free_MB/b.size_MB) * 100) "FREE_%"
FROM   (SELECT tablespace_name,
               Trunc(Sum(bytes)/1024/1024) free_MB
        FROM   dba_free_space
        GROUP BY tablespace_name) a,
       (SELECT tablespace_name,
               Trunc(Sum(bytes)/1024/1024) size_MB
        FROM   dba_data_files
        GROUP BY tablespace_name) b
WHERE  a.tablespace_name = b.tablespace_name
order by 4
/
Consulta de los datafiles, es necesaria por si hay cambios.

SELECT * FROM dba_data_files ORDER BY  FILE_ID, TABLESPACE_NAME;


Consulta detallada del espacio libre de almacenamiento

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
      ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM (bytes) FREE_SPACE,
       ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

Consulta de espacios libres por tablespace y bloques.
SELECT * FROM dba_free_space;


Consulta de espacio libre a un tablespace, en este caso SYSTEM

select (select sum(bytes)/1024/1024  from dba_data_files where tablespace_name = 'SYSTEM') MB,
(select sum(bytes)/1024/1024 from dba_segments where tablespace_name = 'SYSTEM') MB_USED,
(select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'SYSTEM') MB_FREE
from dual;
Consulta por segmentos del SYSTEM, por menos de 10 segundos.

with segs as (select owner, segment_type, segment_name, partition_name, ceil(bytes/1024/1024) mb
--SELECT *
from dba_segments where tablespace_name = 'SYSTEM'
order by 5 desc)
select * from segs where rownum <= 10;


Otra consulta de espacio libre


SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM (fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;


Consulta de detallada del espacio libre
SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Una forma global de ver el espacio de toda la base de datos
SELECT
        TABLESPACE_SIZE/(1024*1024) TABLESPACE_SIZE,
        ALLOCATED_SPACE/(1024*1024) ALLOCATED_SPACE,
        FREE_SPACE/(1024*1024) FREE_SPACE
FROM   dba_temp_free_space
/

Verificar los tablespaces para detalles
SELECT *FROM dba_tablespaces;

Otra forma de consulta

-- ALMACENAMIENTO:
SELECT tablespace_name,
ROUND(sum(bytes)/1024/1024,0)
FROM dba_free_space
WHERE tablespace_name NOT LIKE 'TEMP%'
GROUP BY tablespace_name
UNION
-- MÁXIMO ALMACENAMIENTO:
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 ;

-- ESPACIO DISPONIBLE:
(se hace la operación del resultado para el valor de un tablespace((almacenamiento*100)/maximo)

select (1308*100)/ 28990
    from dual ;