Script - Infotablespaces

Información de los distintos tablespaces de nuestra base de datos.

 (versión mejorada, antes no tenía en cuenta datafiles sin espacio libre)

PROMPT TABLESPACES
column tablespace format a25

SELECT SUBSTR(D.FILE_NAME,1,45) "ARCHIVO",
SUBSTR(T.tablespace_name,1,25) "TABLESPACE",
(D.BYTES/1024)/1024 "TAM(Mb)",
((D.BYTES/1024)/1024 - SUM((NVL(F.BYTES,0)/1024)/1024)) "OCUP(Mb)",
SUM((NVL(F.BYTES,0)/1024)/1024)"LIBRE(Mb)",
((D.BYTES - SUM(NVL(F.BYTES,0))) * 100 / D.BYTES) "% OCUP",
(SUM(NVL(F.BYTES,0)) * 100 / D.BYTES) "% LIBRE"
FROM DBA_FREE_SPACE F,DBA_DATA_FILES D,DBA_TABLESPACES T
WHERE
    D.FILE_ID=F.FILE_ID(+) AND
    D.TABLESPACE_NAME=T.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME,
    D.BYTES,
    SUBSTR(D.FILE_NAME,1,45)
ORDER BY 1;

SET LINESIZE 1000


PROMPT TABLESPACES


SELECT SUBSTR(D.FILE_NAME,1,45) "ARCHIVO",
SUBSTR(F.tablespace_name,1,15) "TABLESPACE",
(D.BYTES/1024)/1024 "TAM(Mb)",
((D.BYTES/1024)/1024 - SUM((F.BYTES/1024)/1024)) "OCUP(Mb)",
SUM((F.BYTES/1024)/1024)"LIBRE(Mb)",
((D.BYTES - SUM(F.BYTES)) * 100 / D.BYTES) "% OCUP",
(SUM(F.BYTES) * 100 / D.BYTES) "% LIBRE"
FROM DBA_FREE_SPACE F,DBA_DATA_FILES D,DBA_TABLESPACES T
WHERE D.FILE_ID=F.FILE_ID AND F.TABLESPACE_NAME=T.TABLESPACE_NAME
GROUP BY F.TABLESPACE_NAME,
    D.BYTES,
    SUBSTR(D.FILE_NAME,1,45),
    T.TABLESPACE_NAME
ORDER BY 1;


SET LINESIZE 1000