How to Check Oracle Database Tablespace for individual tablespace.

Check Individual tablespace allocated Quota, Free and used  details.


You can run the below query with DBA privileges.

Below query will give you the result in GB's

SELECT df.tablespace_name "Tablespace",

  totalusedspace "Used GB",

  (df.totalspace - tu.totalusedspace) "Free GB",

  df.totalspace "Total GB",

  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"

FROM

  (SELECT tablespace_name,

    ROUND(SUM(bytes) / (1024*1024*1024)) TotalSpace

  FROM dba_data_files

  GROUP BY tablespace_name

  ) df,

  (SELECT ROUND(SUM(bytes)/(1024*1024*1024)) totalusedspace,

    tablespace_name

  FROM dba_segments

  GROUP BY tablespace_name

  ) tu

WHERE df.tablespace_name = tu.tablespace_name

and df.tablespace_name='USERS'

You will get the Tablespace size in MB's by running the following :

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used GB",
  (df.totalspace - tu.totalusedspace) "Free GB",
  df.totalspace "Total GB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / (1024*1024)) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name
and df.tablespace_name='USERS'