Toad 13.3.0.181 x64 (client 19.3 x64)
When you want to see free space in tablespace Toad is showing data from DBA_FREE_SPACE, what is wrong...as tablespace may be enlarged...and if is not, this is misleading data.
Example from Toad:
Real situation:
TABLESPACE_NAME MAX_MB CURR_alloc_MB CURR_used_MB CURR_free_MB % CURR_free_MB MAX_free_MB % MAX_free_MB
---------------------------------------- ---------------- ---------------- ---------------- ---------------- -------------- ---------------- -------------
SOLVIT 819.200 641.024 640.876 **148** 0,023 **178.324** 21,768
So free space has to be calculated as max_size-curr_occupied and not curr_allocated-curr_occupied.
Hope this helps.
P.S.
Where has courier font gone for formatting bar in post?
Hi Damir,
Thanks for the feedback. Can you show me the SQL that produced the output below? Also, you are making reference to curr_occupied and curr_allocated that I don't see anywhere.
TABLESPACE_NAME MAX_MB CURR_alloc_MB CURR_used_MB CURR_free_MB % CURR_free_MB MAX_free_MB % MAX_free_MB
---------------------------------------- ---------------- ---------------- ---------------- ---------------- -------------- ---------------- -------------
SOLVIT 819.200 641.024 640.876 **148** 0,023 **178.324** 21,768
And I have no idea about Courier. Toad World has been behaving weird lately (when scrolling at least), that's for sure.
clear computes
clear breaks
break on report;
compute sum of curr_alloc_MB free_MB Max_MB used_MB max_free_MB on report;
col free_MB for 999G999G999G990 heading CURR_free_MB
col used_MB for 999G999G999G990 heading CURR_used_MB
col curr_alloc_MB for 999G999G999G990 heading CURR_alloc_MB
col max_MB for 999G999G999G990
col max_free_MB for 999G999G999G990 heading MAX_free_MB
col perc_free_MB for 9990D990 HEADING "% MAX_free_MB"
col perc_CURR_free_MB for 9990D990 HEADING "% CURR_free_MB"
PROMPT Tablespace size without TEMP
with F as (
select f.tablespace_name,
round(SUM(f.Bytes)/1024/1024) free_MB
from DBA_FREE_SPACE f
group by f.tablespace_name
),
d as (
select d.tablespace_name,
round(SUM(d.Bytes)/1024/1024) curr_alloc_MB
from DBA_DATA_FILES d
group by d.tablespace_name
),
a as (
select d.tablespace_name,
round(SUM(greatest(d.maxBytes,d.Bytes))/1024/1024) max_MB
from DBA_DATA_FILES d
group by d.tablespace_name
)
select d.tablespace_name,
a.MAX_MB,
d.curr_alloc_MB,
d.curr_alloc_MB-nvl(f.free_MB,0) used_MB,
nvl(f.free_MB,0) free_MB,
(1-(curr_alloc_MB-nvl(f.free_MB,0))/curr_alloc_MB)*100 perc_CURR_free_MB,
a.MAX_MB-d.curr_alloc_MB+nvl(f.free_MB,0) max_free_MB,
(1-(d.curr_alloc_MB-nvl(f.free_MB,0))/a.MAX_MB)*100 perc_free_MB
from d,f,a
where d.tablespace_name=f.tablespace_name (+)
and a.tablespace_name=d.tablespace_name
order by d.tablespace_name;
Oh I see what you mean.
Yes, in the Schema Browser, the "Free" column shows only free space of what is currently allocated. It does not include autoextend.
However, there is a column "% Used of Max" that does include autoextend. I realize that is percent and not MB.
I have sent another picture where this is wrongly presented...in toad
Lp Damir
I don't think it is wrong. It shows the sum of all unused space which is currently allocated.
I understand that you find it misleading because you expect it to include space that could be allocated via auto extend, but I think it could be equally misleading if it included all of the potentially allocated space (via tablespace auto extend).
I could add another line in that display to include that information too.
To be honest, I never have been a fan of that "free space" tab. That whole business of "# of pieces", smallest, largest, etc, goes back to the dictionary-managed tablespace days, when fragmentation mattered (or, some thought it did).
Good Morning from lockdown 2!
I once wrote an sql script as part of a Nagios monitoring system, to do exactly this -- how much space is used out of everythingvthat can be allocated, including autoextend.
It was a nightmare. There were tablespaces where only some files were in autoextend mode which needed to be considered -- and all in one single sql statement. I eventually got it working.
Then a database ran out of space! Some of he various databases using this particular array had been set up to autoextend the full amount of the array, as if they were the only database. My sql happily reported that they were fine, until the crash.
If two or more databases share an array, all bets are off with autoextend!
Right, back to lockdown!
Cheers,
Norm.
I do not understand what this has to do with what I wrote.
OS file space is something that must be separately monitor or setup but certainly correctly calculated.
But if you allow that tablespace grow in size more that there is real OS file space, this is wrong setup and nothing can help you.
Also if you overload file system (saw even much more often than pure space problem) it is again bad file space setup-nothing with Oracle neither Toad.
Hi Damir,
it was an anecdotal tale of how it's possible to be monitoring something to the n'th degree, and still get borked by something outwith your monitoring.
At the time, we had a huge array of disc for database use. Every database lived in it. Multiple DBAs set up the databases with autoextend but some used unlimited maximum size.
Turned out that while we had monitoring of the array as well as the databases, there was (allegedly) a problem/bug in the array monitoring so the databases extended enough to fill it.
Monitoring the database usage, while considering autoextend didn't prevent the failure.
We switched to OEM back then to monitor both.
Cheers,
Norm. [TeamT]
@Norm,
Ok I see. I was a little bit too direct...sorry for that.
Now for the end of all scenarios where "dba_free_space " is used to see free space.
From oracle docu:
If a data file (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information.
If an object has extents in an online file of the tablespace, u will see extent information about the offline data file.
However, if the object is entirely in the offline file, a query of this view will not return any records.
If a data file does not have any free space, you will not see a row for the data file in this view.
Do you see how far it can goes and make calculation of tablespace free space complicated (with or without extensible)
1 Like
