Tablespace

How to Check Oracle Tablespace Usage

After having the whole picture about the size of the database, we can further check Oracle tablespace usage solely by SQL.

Oracle Tablespace Usage

Of course, you can use GUI tools to check the space usage of tablespace in Oracle, but if someday, the only tool you got is sqlplus, a text-based tool, everything must be back to basic. How basic? Every routine jobs including monitoring, maintenance and troubleshooting must be composed as statements and scripts one by one.

Checking all tablespaces usage is one of such jobs, very routine, very basic. Then, how to compose a statement that can check tablespaces usage?

In this post, I will show you the SQL script that can check tablespace usage thoroughly.

All Tablespace Usage

We have to format the output report on the console first.

--Format Usage Report
set linesize 200;
column "Tablespace" format a15;
column "Used GB" format 999,999.99
column "Free GB" format 999,999.99
column "Allocated GB" format 999,999.99
column "Used % of Allocated" format 99.99;
column "Maximum GB" format 999,999.99
column "Used % of Maximum" format 99.99;

Then execute the following SQL statement.

--Check Tablespace Usage
SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKNOWN')) "Tablespace",
  gbytes_alloc - NVL (gbytes_free, 0) "Used GB",
  NVL (gbytes_free, 0) "Free GB",
  gbytes_alloc "Allocated GB",
  ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_alloc) * 100 "Used % of Allocated",
  gbytes_max "Maximum GB",
  CASE
    WHEN gbytes_max > 0
    THEN ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_max) * 100
  END "Used % of Maximum"
FROM
  (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT SUM (free_space) / 1024 / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_temp_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)    / 1024 / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_data_files
  GROUP BY tablespace_name
  UNION
  SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)   / 1024 / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_temp_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name(+) = b.tablespace_name
AND ( b.tablespace_name IS NULL
OR INSTR (LOWER (b.tablespace_name), LOWER (b.tablespace_name)) > 0)
ORDER BY 7 DESC NULLS LAST;

Please note that, the SQL statement also includes temporary tablespaces.

Let’s see an example of output result.

Tablespace          Used GB     Free GB Allocated GB Used % of Allocated  Maximum GB Used % of Maximum
--------------- ----------- ----------- ------------ ------------------- ----------- -----------------
ERPTBS1              133.44        3.20       136.63               97.66      160.00             83.40
ERPTBS2               78.12        3.58        81.70               95.62      128.00             61.03
OGGTBS                 7.89         .41         8.30               95.05       64.00             12.33
SYSAUX                 2.31         .24         2.56               90.47       32.00              7.23
SYSTEM                  .88         .07          .96               92.35       32.00              2.76
UNDOTBS1                .32       31.68        32.00                1.00       32.00              1.00
USERS                   .00         .00          .00               33.75       32.00               .01
UNDOTBS2                .00         .02          .02               16.25       64.00               .00
...

14 rows selected.

The second last column shows that we can expect the tablespace to auto extend the space to the maximum size of owned data files. Therefore, the last column shows us current usage percentage with respect to maximum capacity of tablespace.

Please note that, the SQL statement also includes temporary tablespaces.

Over 80% Tablespace Usage

You may like to monitor only tablespaces that are over a specific threshold of maximum capacity, say 80%. You can add some condition for this SQL statements.

SELECT *
FROM
  (SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKNOWN')) "Tablespace",
    gbytes_alloc - NVL (gbytes_free, 0) "Used GB",
    NVL (gbytes_free, 0) "Free GB",
    gbytes_alloc "Allocated GB",
    ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_alloc) * 100 "Used % of Allocated",
    gbytes_max "Maximum GB",
    CASE
      WHEN gbytes_max > 0
      THEN ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_max) * 100
    END "Used % of Maximum"
  FROM
    (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_free,
      tablespace_name
    FROM sys.dba_free_space
    GROUP BY tablespace_name
    UNION
    SELECT SUM (free_space) / 1024 / 1024 / 1024 gbytes_free,
      tablespace_name
    FROM sys.dba_temp_free_space
    GROUP BY tablespace_name
    ) a,
    (SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
      SUM (maxbytes)    / 1024 / 1024 / 1024 gbytes_max,
      tablespace_name,
      COUNT (*) data_files
    FROM sys.dba_data_files
    GROUP BY tablespace_name
    UNION
    SELECT SUM (bytes) / 1024 / 1024 / 1024 gbytes_alloc,
      SUM (maxbytes)   / 1024 / 1024 / 1024 gbytes_max,
      tablespace_name,
      COUNT (*) data_files
    FROM sys.dba_temp_files
    GROUP BY tablespace_name
    ) b
  WHERE a.tablespace_name(+)                                      = b.tablespace_name
  AND ( b.tablespace_name                                        IS NULL
  OR INSTR (LOWER (b.tablespace_name), LOWER (b.tablespace_name)) > 0)
  ORDER BY 7 DESC NULLS LAST
  )
WHERE "Used % of Maximum" > 80;

The above statement will only show tablespace that are over 80%:

Tablespace          Used GB     Free GB Allocated GB Used % of Allocated  Maximum GB Used % of Maximum
--------------- ----------- ----------- ------------ ------------------- ----------- -----------------
ERPTBS1              133.44        3.20       136.63               97.66      160.00             83.40

For megabyte grade of units, you can use the following statement:

--Format Usage Report
set linesize 200;
column "Tablespace" format a15;
column "Used MB" format 999,999.99
column "Free MB" format 999,999.99
column "Allocated MB" format 999,999.99
column "Used % of Allocated" format 99.99;
column "Maximum MB" format 999,999.99
column "Used % of Maximum" format 99.99;

--Check Tablespace Usage
SELECT NVL (b.tablespace_name, NVL (a.tablespace_name, 'UNKNOWN')) "Tablespace",
  gbytes_alloc - NVL (gbytes_free, 0) "Used MB",
  NVL (gbytes_free, 0) "Free MB",
  gbytes_alloc "Allocated MB",
  ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_alloc) * 100 "Used % of Allocated",
  gbytes_max "Maximum MB",
  CASE
    WHEN gbytes_max > 0
    THEN ( (gbytes_alloc - NVL (gbytes_free, 0)) / gbytes_max) * 100
  END "Used % of Maximum"
FROM
  (SELECT SUM (bytes) / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT SUM (free_space) / 1024 / 1024 gbytes_free,
    tablespace_name
  FROM sys.dba_temp_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT SUM (bytes) / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)    / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_data_files
  GROUP BY tablespace_name
  UNION
  SELECT SUM (bytes) / 1024 / 1024 gbytes_alloc,
    SUM (maxbytes)   / 1024 / 1024 gbytes_max,
    tablespace_name,
    COUNT (*) data_files
  FROM sys.dba_temp_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name(+) = b.tablespace_name
AND ( b.tablespace_name IS NULL
OR INSTR (LOWER (b.tablespace_name), LOWER (b.tablespace_name)) > 0)
ORDER BY 7 DESC NULLS LAST;

To drill down to the details of UNDO space, you need to know how to check UNDO tablespace usage.

Leave a Reply

Your email address will not be published. Required fields are marked *