dbcheck.pl - Oracle - tablespace with AutoExtent 🔗 link

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.


Regards,
Henrik

Hi Henrik,

My knowledge of Oracle is not too much higher, but IMO you DBA is right.
If the autoextend property is set on a tablespace, these cases should
not cause a red status. I think the best way would be to check the Used
size as a percentage of the "Autoextent(Size)" size, not the current
"Size" size. I've experienced the same issue with BB's Oracle montoring
scripts.

As a secondary monitoring point, disk monitoring should be enabled on
the disks containing the tablespaces. They may well fill up before the
max size is reached, preventing Oracle from extending the tablespaces to
the maximum size allowed. Of course, if the Oracle data files do not
reside on a filesystem, this cannot be done.


Cheers,

Eric.

Henrik Stoerner wrote:

Henrik Stoerner schreef:

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

I am still introducing Francesco's dbcheck script to my DBAs. I have
multiple DBA groups, and setting monitoring up with thresholds &
settings that they can all agree on is difficult sometimes. But since
they are the ones getting page, I can understand their concern on having
monitoring setup right. I still have a todo to email them the exact SQL
queries being used by the checks, I have had reports from one group that
some of the checks can be Database intensive. Currently I have all my DB
checks running at 5 min intervals, so I am looking forward to
identifying the 'intensive' queries and moving them to a much longer run
interval (great feature). For now I have just stopped those particular
test from running.

As to TableSpace checks, my DBAs take into account 3 parameters to
determine if diskspace is an issue. FreeSpace, PercentFree, and Extend
(I think). Some of our TableSpaces are very small, and when thresholds
are just set to check Percents we get alot of false-positives. So I have
made use of dbcheck's Custome query option to replace the TblSpace
check. Basically the SQL performs all the check logic, and sets a field
flag with a 1 (green), 2 (yellow), or 3 (red) value.  dbchecks does a
check only on this field to set the status of the test. Below I have
provided the CUSTOM TblSpace check. Pardon the very long, one line SQL.
It does not provide for great readability.

[CUSTOM TblSpace]
Dbtype=Oracle
Query=select a.tablespace_name, nvl(b.total_mb,0) tsize, nvl((b.total_mb
- c.free_mb),0) used, nvl(c.free_mb,0) free, nvl(round((((b.total_mb -
c.free_mb)*100)/b.total_mb),2),0) Usedpct, d.autoextensible Autoextent,
CASE WHEN nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0)>97
AND c.free_mb < 200 THEN '2' WHEN nvl(round((((b.total_mb -
c.free_mb)*100)/b.total_mb),2),0)>94 AND c.free_mb < 400 THEN '1' ELSE
'0' END Status  from (select tablespace_name, (sum(bytes)/1024)/1024
total_mb from dba_data_files group by tablespace_name) b, (select
tablespace_name, nvl(round(((sum(bytes)/1024)/1024),2),0) free_mb from
dba_free_space group by tablespace_name) c, (select distinct
tablespace_name,autoextensible from dba_data_files where
autoextensible='NO') d, dba_tablespaces a where a.tablespace_name =
b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) and
a.tablespace_name = d.tablespace_name and a.contents not in
('TEMPORARY','UNDO') and a.tablespace_name not like '%RBS%' and a.status
<> 'READ ONLY' UNION ALL select a.tablespace_name, nvl(b.total_mb,0)
tsize, nvl((b.total_mb - c.free_mb),0) used, nvl(c.free_mb,0) free,
nvl(round((((b.total_mb - c.free_mb)*100)/b.total_mb),2),0) Usedpct,
d.autoextensible Autoextent, CASE WHEN fr < 200 OR ex < 2 THEN '2' WHEN
fr < 400 OR ex < 5 THEN '1' ELSE '0' END Status  from (select
tablespace_name, (sum(bytes)/1024)/1024 total_mb from dba_data_files
group by tablespace_name) b, (select tablespace_name,
nvl(round(((sum(bytes)/1024)/1024),2),0) free_mb from dba_free_space
group by tablespace_name) c, (select distinct
tablespace_name,autoextensible,(maxbytes - bytes)/1024/1024 fr,(maxbytes
- bytes)*blocks/(increment_by*bytes) ex from dba_data_files where
autoextensible='YES') d, dba_tablespaces a where a.tablespace_name =
b.tablespace_name (+) and a.tablespace_name = c.tablespace_name (+) and
a.tablespace_name = d.tablespace_name and a.c
ontents not in ('TEMPORARY','UNDO') and a.tablespace_name not like
'%RBS%' and a
.status <> 'READ ONLY' order by 1
Valtocheck=7:1:2:=:1
ColumnName=TableSpaceName;Size(MB);Used(MB);Available(MB);Used%;AutoExte
nt
Columnsize=;;;;;;0
Columnalign=l;r;r;r;r;r
displaylegend=no
docheckdefault=yes
showmatchedrule=no