Wednesday, November 27, 2013

How to find size, free space, used space, allocated space for the tablespaces in your database

SELECT 
        F.FILE_ID,
        F.FILE_NAME,
        ROUND(F.BYTES/1024/1024) "Allocated(MB)",
        NVL(TO_CHAR(ROUND(F.BYTES/1024/1024 - SUM(E.BYTES/1024/1024),1)),'File full') "Used (MB)",
        NVL(TO_CHAR(ROUND(SUM(E.BYTES/1024/1024),1)), '0') "Free (MB)",
        LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Autoextend?",
        LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM    DBA_DATA_FILES F LEFT OUTER JOIN DBA_FREE_SPACE E
ON      E.FILE_ID = F.FILE_ID
GROUP BY F.FILE_ID,F.FILE_NAME,F.BYTES,AUTOEXTENSIBLE,MAXBYTES
ORDER BY 1;

For a specific tablespace:
alter session set nls_language='american';
SET LINES 400
COL "File ID" format 999
COL "Tablespace name" format a20
COL "File name" format a50
COL "Available space (MB)" format 9999999999
COL "Free space (MB)" format a20
COL "Percentage used" format a20
COL "Max size (MB)" format a20
COL "Can file grow?" format a20
 
WITH   free_spc AS (
                  SELECT FILE_ID, SUM(BYTES/1024/1024) AS MBFree
                  FROM DBA_FREE_SPACE fre
                  WHERE TABLESPACE_NAME = UPPER('&&tablespace')
                  GROUP BY FILE_ID
          )
SELECT  F.FILE_ID "File ID",
             F.TABLESPACE_NAME "Tablespace name",
             F.FILE_NAME "File name",
             ROUND(F.USER_BYTES/1024/1024) "Available space (MB)",
             NVL( TO_CHAR(ROUND(FS.MBFree)), 'NADA') "Free space (MB)",
             LPAD(NVL(ROUND(( ROUND(F.BYTES/1024/1024 - FS.MBFree)/ROUND(F.BYTES/1024/1024) )  * 100),100),15,' ') || ' %' "Percentage used",
             LPAD(DECODE(F.AUTOEXTENSIBLE,'YES', 'JA','NO','NEIN!'), 11,' ') "Can file grow?",
             LPAD(DECODE(ROUND(F.MAXBYTES/1024/1024),0,'N/A',ROUND(F.MAXBYTES/1024/1024)),15,' ') "Max size (MB)"
FROM DBA_DATA_FILES F LEFT JOIN free_spc FS
ON F.FILE_ID = FS.FILE_ID
WHERE F.TABLESPACE_NAME = UPPER('&&tablespace')
ORDER BY F.FILE_ID ASC;

No comments:

Post a Comment