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.