Diferencias entre DBA_FREE_SPACE y DBA_TABLESPACE_USAGE_METRICS

He estado durante muchísimo tiempo realizando consultas nivel de ocupación de los tablespaces en Oracle 9 que utilizan las vistas del sistema DBA_FREE_SPACE y DBA_DATA_FILES , lo cual esta muy bien, pero recientemente he sabido que hay personas que en Oracle 11 están usando otra vista del sistema para conocer el nivel de ocupación de los tablespaces que es muy rápida: DBA_TABLESPACE_USAGE_METRICS .

Esta vista contiene información sobre espacios de tablas permanentes, temporales y de deshacer, y tiene solo cuatro columnas:

• TABLESPACE_NAME TABLESPACE_NAME
• USED_SPACE ESPACIO USADO
• TABLESPACE_SIZE TABLESPACE_SIZE
• USED_PERCENT USED_PERCENT

Hay tres cosas importantes acerca de esas columnas:

1. Los números de USED_SPACE y TABLESPACE_SIZE están en bloques , no en bytes, por lo que necesitamos unir esta vista a la vista del sistema DBA_TABLESPACES para terminar con números en bytes. ( ellos la ofrecen tal cual sin hacer la conversión a bytes , de modo que lo que nos están dando son el nº de bloques y no el de bytes)
2. La columna TABLESPACE_SIZE muestra el tamaño total posible , no el tamaño actual
3. USED_PERCENT muestra el uso del tamaño total posible de un espacio de tabla.

Es decir , la información de esta vista no es la que muestra el tamaño real , sino el posible tamaño máximo lo cual explica la gran diferencia que nos encontraremos entre cálculos realizados sobre la vista DBA_FREE_SPACE o sobre la vista DBA_TABLESPACE_USAGE_METRICS

Algo muy importante es que la vista DBA_TABLESPACE_USAGE_METRICS nos ofrece la volumetría en bloques lo cual no es muy útil , por lo que para traducirlo a bytes tendremos que saber el tamaño del bloque . Con esta consulta podemos ver el tamaño del bloque:

show parameter block_size

Ahora , lo mejor es hacer la conversión usando el valor de Block_size de modo que obtengamos en la consulta el tamaño de los Tablespaces en MB y no en bloques

select * from(
SELECT
a.tablespace_name,
ROUND((a.used_space * b.block_size) / 1048576, 2) AS "Used space (MB)",
ROUND((a.tablespace_size * b.block_size) / 1048576, 2) AS "Tablespace size (MB)",
ROUND(a.used_percent, 2) AS "Used %"
FROM dba_tablespace_usage_metrics a
JOIN dba_tablespaces b ON a.tablespace_name = b.tablespace_name) where TABLESPACE_NAME LIKE '%EJEMPLO_TB%'

También podemos usar el método "antiguo " basado en las vista DBA_FREE_SPACE uniéndolas con las vistas DBA_DATA_FILES y s.DBA_TABLESPACES tanto en oracle 9, 10 y 11:,

select 'EJEMPLO2_TB', usados, total ,100*usados/total used_percent,libre from

(select sum(total)total , sum(usados) usados, sum(libre) libre

from (
Select t.tablespace_name Tablespace, t.status Estado,
ROUND(MAX(d.bytes)/1024/1024,2) Total,
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) Usados,
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) Libre,
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM sys.DBA_FREE_SPACE f, sys.DBA_DATA_FILES d, sys.DBA_TABLESPACES t
WHERE

t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id

GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC )

where Tablespace='EJEMPLO2_TB')

El resultado de ambos consultas sobre la vista DBA_TABLESPACE_USAGE_METRICS o sobre la vista DBA_FREE_SPACE con datos de ejemplo las podemos ver reflejados en la siguiente tabla:

Es decir, en los cálculos anteriores teniendo en cuanta que la vista dba_tablespace_usage_metric solo muestra el tamaño total posible , como vemos como la información anterior , solo nos están dando la volumetría máxima posible y no la real dba_tablespace_usage_metrics

Como puede apreciar querido lector en este ejemplo las diferencias son notables entre los resultados arrojados por ambas consultas :para el caso del primer da una ocupación del 79'3% para el TB de datos y 79,5 % de ocupación para los TB de los indices usando la vista dba_tablespace_usage_metric, lo cual da resultados esperanzadores ( aun quedaría mas del 20% por ocupar) mientras en el segundo caso la query da una ocupación del 98'9 para el TB de datos y 97,1 % de ocupación para los TB de los indices usando la vista dba_fee_space, lo cual son resultados reales bastantes alejados de los resultados obtenidos en la primer vista

Espero que encuentre útil la consulta. Aquí está el enlace de la documentación de Oracle sobre la vista: Oracle Documentation - DBA_TABLESPACE_USAGE_METRICS