Problem:

You would like to find out quickly how much space your tablespaces are utilizing and how much space is being wasted below the high watermark with in a tablespace, how would you look for it ? While there are multiple ways to pull out that information but ever since MON_GET functions were introduced I rarely have to look past them.

Enough with the talk, lets take a look at the SQL…

Solution:

Simply running below SQL will provide you with high level tablespace utilization and space wastage report for top 20 of the tablespaces with in your database.

with tbsp_size as (select distinct substr(TBSP_NAME,1,30) as TBSP_NAME, TBSP_PAGE_SIZE, TBSP_TOTAL_PAGES,
case when TBSP_PAGE_SIZE=32768 then
(TBSP_USED_PAGES*32768)/(1024*1024*1024) when TBSP_PAGE_SIZE=16384 then
(TBSP_USED_PAGES*16384)/(1024*1024*1024) when TBSP_PAGE_SIZE=8192 then
(TBSP_USED_PAGES*8192)/(1024*1024*1024) when TBSP_PAGE_SIZE=4096 then
(TBSP_USED_PAGES*4096)/(1024*1024*1024) end as TBSP_USED_GB,
case when TBSP_PAGE_SIZE=32768 then
(TBSP_TOTAL_PAGES*32768)/(1024*1024*1024) when TBSP_PAGE_SIZE=16384 then
(TBSP_TOTAL_PAGES*16384)/(1024*1024*1024) when TBSP_PAGE_SIZE=8192 then
(TBSP_TOTAL_PAGES*8192)/(1024*1024*1024) when TBSP_PAGE_SIZE=4096 then
(TBSP_TOTAL_PAGES*4096)/(1024*1024*1024) end as TBSP_TOTAL_GB
FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t order by TBSP_TOTAL_GB desc fetch first 20 rows only with ur)
select TBSP_NAME, TBSP_PAGE_SIZE, TBSP_TOTAL_PAGES, TBSP_USED_GB, TBSP_TOTAL_GB, 
(TBSP_TOTAL_GB-TBSP_USED_GB) as WASTED_SPACE_GB from tbsp_size

Running the above SQL will provide you with an output that looks something like below. Note that here I am only asking for the top 20 tablespaces based on the space utilization. You could ask for more or less depending on what you need. Also don’t think that we are crazy by naming all the tablespaces as TABLESPACE* 🙂 I am just trying to be safe by not disclosing any of my employers business related information.

As you can see from the above output, there are a few tablespaces where space could be reclaimed from them. Depending on the type of tablespace you may be able to reclaim all of the wasted storage. If you would like to know how to reclaim the unused storage from a tablespace, please take a look at the below blog post.

How To Reclaim Wasted Storage From A DMS/AS Tablespace

Hope you enjoyed this short blog post from me, I will try to blog more frequently as I am getting more requests from the readers.