Querying Tablespace Usage

Querying Tablespace Usage

If you are an Oracle DBA then you know the importance of tablespaces in the oracle database. In Oracle Database the tablespace management is important and Oracle DBA will need to do quite frequently. So here is a query that providing the tablespace uses details.

Query to check the tablespace usage

The following 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;

In my case output is below:

Tablespace Usage QueryOracle Database

Definitions of above 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.

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

Share this
Share

1 thought on “Querying Tablespace Usage”

Comments are closed.

Share