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
    SUM(bytes)/1024/1024/1024 free_space_in_gb
  FROM user_free_space
  WHERE tablespace_name IN
    (SELECT DISTINCT tablespace_name FROM user_segments
  (SELECT SUM(bytes)/1024/1024/1024 used_space_in_gb,
  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_1 2.89 1.11
Tablespace_2 200.14 1.86

Your’s Truly


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s