Querying Tablespace Usage

Hello friends, In this article, we are going to learn how to querying tablespace usage. In Oracle Database the tablespace management is important and Oracle DBA will need to do quite frequently. The below query will help you to get the tablespace usage details.

Query to check the tablespace usage

The below query will help you to check tablespace usage.

set pages 999
 set lines 400
 SELECT df.tablespace_name tablespace_name,
  max(df.autoextensible) auto_ext,
  round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
  round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
  round(df.bytes / (1024 * 1024), 2) curr_ts_size,
  round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
  round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
  round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
  nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
 FROM dba_free_space fs,
  (select tablespace_name,
  sum(bytes) bytes,
  sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
  max(autoextensible) autoextensible
  from dba_data_files
  group by tablespace_name) df
 WHERE fs.tablespace_name (+) = df.tablespace_name
 GROUP BY df.tablespace_name, df.bytes, df.maxbytes
 UNION ALL
 SELECT df.tablespace_name tablespace_name,
  max(df.autoextensible) auto_ext,
  round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
  round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
  round(df.bytes / (1024 * 1024), 2) curr_ts_size,
  round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
  round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
  round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
  nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
 FROM (select tablespace_name, bytes_used bytes
  from V$temp_space_header
  group by tablespace_name, bytes_free, bytes_used) fs,
  (select tablespace_name,
  sum(bytes) bytes,
  sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
  max(autoextensible) autoextensible
  from dba_temp_files
  group by tablespace_name) df
 WHERE fs.tablespace_name (+) = df.tablespace_name
 GROUP BY df.tablespace_name, df.bytes, df.maxbytes
 ORDER BY 4 DESC;

Tablespace Usage Query – Oracle Database

Definitions of above query Columns

TABLESPACE_NAME: This is the Name of Tablespace.
AUTO_EXT: Datafiles are in auto-extensible MODE.
MAX_TS_SIZE: This is indicated the maximum Tablespace Size if all the datafiles reach their max_size.
MAX_TS_PCT_USED: This is the percent of MAX_TS_SIZE reached and is the most important value in the query, as this reflects the true usage before DBA intervention is required.
CURR_TS_SIZE: This is the currently available size of the Tablespace.
USED_TS_SIZE: This is currently used tablespace size.
TS_PCT_USED: This is the percent of the currently used tablespace.
FREE_TS_SIZE: This is the currently free size of tablespace.
TS_PCT_FREE: This is how much is percentage free in the tablespace.

Hope you understand how to Querying Tablespace Usage.

Read - How to check active sessions in Oracle database

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube: https://www.youtube.com/ocptechnology