Status de TABLESPACES en la base de datos ORACLE

status-tablespace-oracle.sql

-- Con el siguiente query podemos ver el status de los tablespaces o unidades lógicas de almacenamiento de la base de datos:
SELECT A.tablespace_name tablespace_name,
to_char(TRUNC(NVL(SUM(B.BYTES/1048576),0) / (A.BYTES/1048576),4)*100)||'%' pct_free,
100 - to_char(TRUNC(NVL(SUM(B.BYTES/1048576),0) / (A.BYTES/1048576),4)*100)||'%' pct_used,
NVL(TRUNC(SUM(B.BYTES/1048576),2),0)||'MB' FREE_SPACE,
TRUNC((A.BYTES/1048576)-NVL(SUM(B.BYTES/1048576),2),0)||'MB' USED_SPACE,
TRUNC(A.BYTES/1048576,2)||'MB' FILE_SIZE,
autoextensible, status
FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID (+)
AND A.tablespace_name IN (select distinct tablespace_name from user_tablespaces)
GROUP BY A.tablespace_name, A.file_name, A.bytes/1048576, autoextensible, status, A.BYTES
UNION
SELECT AA.tablespace_name,
to_char(TRUNC(NVL(SUM(BB.BYTES/1048576),0) / (AA.BYTES/1048576),4)*100)||'%' pct_free,
100 - to_char(TRUNC(NVL(SUM(BB.BYTES/1048576),0) / (AA.BYTES/1048576),4)*100)||'%' pct_used,
NVL(TRUNC(SUM(BB.BYTES/1048576),2),0)||'MB' FREE_SPACE,
TRUNC((AA.BYTES/1048576)-NVL(SUM(BB.BYTES/1048576),2),0)||'MB' USED_SPACE,
TRUNC(AA.BYTES/1048576,2)||'MB' FILE_SIZE,
autoextensible,
status
FROM DBA_TEMP_FILES AA, DBA_FREE_SPACE BB
WHERE AA.FILE_ID=BB.FILE_ID (+)
GROUP BY AA.tablespace_name, AA.FILE_NAME, AA.BYTES/1048576, autoextensible, status, AA.BYTES;