Saturday, August 4, 2012

Calculate actual free space of datafiles and tablespaces

Let's say, we have a 10GB datafile, autoexendible to 20GB. Querying DBA_FREE_SPACE we see this datafile has 2GB free space.
The actual free space is MAX_SIZE - SIZE + FREE_SPACE, so for our example is 20GB - 10GB + 2GB = 12GB.
The following query will display this datafiles' free space [TOTAL FREE SPACE GB] per tablespaces.
  SELECT B.TABLESPACE_NAME,
         B.FILE_NAME,
         ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
         ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
         ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
         CASE
            WHEN MAXBYTES = 0
            THEN
               ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
            ELSE
               ROUND (
                    ( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
         END
            "TOTAL FREE SPACE GB"
    FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
   WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
         B.FILE_NAME,
         B.BYTES,
         B.MAXBYTES
ORDER BY 1, 2;

The output is:   
TABLESPACE_NAMEFILE_NAMESIZE GBMAX SIZE GBFREE SPACE GBTOTAL FREE SPACE GB
CBAM/oradb/SOADB/oradata1/cbam.dbf9.77000
CBAM/oradb/SOADB/oradata6/cbam14.dbf19.5319.5300
CBAM/oradb/SOADB/oradata6/cbam18.dbf19.5319.530.010.01
..................
CBAM/oradb/SOADB/oradata6/cbam27.dbf12.52511.9224.42
CBAM/oradb/SOADB/oradata7/cbam25.dbf25250.030.03
CBAM/oradb/SOADB/oradata7/cbam28.dbf12.1124.4111.824.1

This query will not return all tablespaces' datafiles, only those with at least a few KB of free space and ,thus, at least 1 row in DBA_FREE_SPACE.

Another query to sum this free space per tablespace:
  SELECT B.TABLESPACE_NAME,
         ROUND (SUM (B.BYTES) / 1024 / 1024 / 1024, 2) "SIZE GB",
         ROUND (SUM (B.MAXBYTES) / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
         A.FREE_SPACE_GB "FREE SPACE GB",
         ROUND (
            (SUM (B.MAXBYTES - B.BYTES) / 1024 / 1024 / 1024) + A.FREE_SPACE_GB,
            2)
            "TOTAL FREE SPACE GB"
    FROM DBA_DATA_FILES B,
         (  SELECT TABLESPACE_NAME,
                   ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) FREE_SPACE_GB
              FROM DBA_FREE_SPACE
             WHERE TABLESPACE_NAME IN ('CBAM')
          GROUP BY TABLESPACE_NAME) A
   WHERE     B.TABLESPACE_NAME IN ('CBAM')
         AND B.TABLESPACE_NAME = A.TABLESPACE_NAME
         AND B.MAXBYTES != 0
GROUP BY B.TABLESPACE_NAME, A.FREE_SPACE_GB
ORDER BY 1;

The output:
TABLESPACE_NAMESIZE GBMAX SIZE GBFREE SPACE GBTOTAL FREE SPACE GB
CBAM284.58309.3823.8648.66

Again, this query will not show you the tablespace's full size. In "SIZE GB" column are added the datafiles' sizes which have free space.