[hobbit] dbcheck.pl - Oracle - tablespace with AutoExtent

[Paul van Eldijk]

Henrik Stoerner schreef:
> I've started using Francesco's dbcheck script - very nice package,
> really. However, one of my DBA's is a bit unhappy with the tablespace
> check - it reports yellow/red status for tables that have been defined
> with AutoExtent enabled.
> 
> Right now I get a report like this:
> 
> Tablespace check (def. warning  Use% >= 90%, def. alert  Use% >= 95%)
> TableSpace/DBSpace           Size    Used    Free  Use% Autoextent(Size)
> BASIC_DATA2_1976_88        300.0M  215.0M   85.0M   72% YES (64.0G)    
> BASIC_DATA2_1989Q1         400.0M  382.0M   18.0M   96% YES (64.0G)    
> BASIC_DATA2_1989Q2         500.0M  483.0M   17.0M   97% YES (64.0G)    
> 
> with the last two tables causing a red status. My DBA thinks they should
> not trigger an alert, because they will automatically increase in size
> if needed.
> 
> Can anyone shed some light on this? My knowledge of Oracle is close to
> zero, so I really don't have any idea whether he is right or not.

It all depends. Auto-extendable tablesspaces often have a limit set for 
the maximum size the individual datafiles can grow to. So you have to 
take into account:
- is a datafile auto-extendable
- if so, what is the maximum size of the datafile; if it has a maximum 
size defined, you can calculate what the maximum size for the tablespace 
can become and based on that what the current usage is. If it has no 
limit set, the calculation of a usage-percentage is useless (or you 
should take into account the maximum diskspace available on the disks 
the datafiles reside on, but that's taking it to far, I guess).

Datafile settings can be different for each datafile, so it gets rather 
complicated.

greetings,
Paul