How to check the used & free space in the tablespace

SELECT /* + RULE */ df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024)”Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

SELECT /* + RULE */ df.file_name ,df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024)”Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT file_name,tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY file_name,tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.file_name
UNION ALL
SELECT /* + RULE */ fs.file_name,df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used,fs.file_name
ORDER BY 1 DESC;

32 thoughts on “How to check the used & free space in the tablespace”

  1. In case you’re unable besides into Home windows 10 for
    any purpose, you can boot from the restoration drive and
    can get a menu of superior choices to help troubleshoot the issue and get your system up and operating.

  2. Amazing! This blog looks just like my old one!
    It’s on a totally different topic but it has pretty much the same layout and design.
    Excellent choice of colors!

  3. My brother suggested I may like this web site.
    He was once totally right. This publish actually made my day.
    You can not believe simply how much time I had spent for
    this info! Thanks!

  4. Incredible! This blog looks just like my old one!

    It’s on a totally different topic but it has pretty much the same
    page layout and design. Wonderful choice of colors!

  5. My coder is tгying to convince mᥱ to move to .net from PHP.
    Ι have always disliked the iⅾea because of the expenses.
    But he’s tryiong none the less. I’ve been using ԜoгdPress on various
    websiteѕ for about a yеar and am anxiоսs abօut switching to another
    platform. I have heard fantastic things about blogengine.net.
    Iѕ there a way I can import all my wordpress pօsts into
    it? Any help ᴡould be really аpprecіated!

  6. I’m as you could probably teach a category on how to produce a great blog. This really is fantastic! I need to say, what really got me was your design. You certainly understand how to make your website more than just a rant about a issue. Youve caused it to be feasible for visitors to connect. Good for you, because not that many individuals understand what theyre doing.
    شركة تنظيف موكيت بالمدينة المنورة

  7. Thanks for one’s marvelous posting! I actually enjoyed reading it, you will
    certainly be an incredible author. I will make sure that to bookmark your website and definitely will keep coming back from now on. I wish to encourage anyone to definitely continue your great posts,
    have got a nice day!

  8. Very nice post. I simply found your weblog and wished to express that I’ve really enjoyed surfing around your blog site posts.

    Whatever the case I’ll be subscribing to your feed and
    i also hope you write again immediately!

  9. Its like you read my mind! You appear to know a lot about this,
    like you wrote the book in it or something. I think that you can do with a few pics to
    drive the message home a little bit, but other than that, this is magnificent blog.
    A fantastic read. I’ll definitely be back.

Leave a Reply

Your email address will not be published. Required fields are marked *