Oracle Schema Level Space Statistics

Respected Readers,

It is relatively simple to find space utilization of your application in Unix (df -h ).

But if you want to check schema level stats in Oracle, various dictionary tables can be used. Below query would report space used and space available of a schema by tablespace.

SELECT x.tablespace_name,
  ROUND(y.used_space_in_gb, 2) used_space_in_gb,
  ROUND(x.free_space_in_gb, 2) free_space_in_gb
FROM
  (SELECT TABLESPACE_NAME,
    SUM(bytes)/1024/1024/1024 free_space_in_gb
  FROM user_free_space
  WHERE tablespace_name IN
    (SELECT DISTINCT tablespace_name FROM user_segments
    )
  GROUP BY TABLESPACE_NAME
  )x,
  (SELECT SUM(bytes)/1024/1024/1024 used_space_in_gb,
    tablespace_name
  FROM user_segments
  GROUP BY tablespace_name
  ) y
WHERE x.tablespace_name = y.tablespace_name
;

Output would report summary of all tablespaces in the schema:

TABLESPACE_NAME USED_SPACE_IN_GB FREE_SPACE_IN_GB
Tablespace_1 2.89 1.11
Tablespace_2 200.14 1.86

Thanks,
Your’s Truly

Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , , , , , . Bookmark the permalink.

One Response to Oracle Schema Level Space Statistics

  1. Manoj says:

    thanks !!! really helpful 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s