Jump to content


Photo

Oracle Tablespace Check Autoextend?


  • Please log in to reply
3 replies to this topic

#1 Edward.Moscardini

Edward.Moscardini

    Member

  • Members
  • Pip
  • 8 posts

Posted 28 August 2009 - 08:18 PM

Hi,

I was wondering if you could provide the pl/sql query used to monitor Oracle tablespace usage. I'm testing it out, and it doesn't seem to take into account Oracle's autoextend feature.


Thanks,

Edward Moscardini

#2 Joel Pereira

Joel Pereira

    Guru

  • Members
  • PipPipPipPip
  • 174 posts
  • Gender:Male
  • Location:Toronto, Canada

Posted 31 August 2009 - 03:54 AM

Hey Edward,

Oracle tablespaces with autoextend on will simply return how much free space they have at that moment (when the monitor runs). It doesn't try to perform any estimate of how much free space it "may" have if/when it auto-extends. So this means that a tablespace with auto-extend turned on will always appear to look almost full.

Here's the query used in the Oracle Tablespace monitor:

CODE
SELECT DISTINCT 'zz', df.tablespace_name, SUM (df.BYTES) "Total Bytes", MAX( fs.total_bytes) "Bytes Free",
(MAX(fs.total_bytes)/SUM (df.BYTES))*100 "% Free"
FROM SYS.dba_data_files df, SYS.dba_free_space_coalesced fs
WHERE ((df.tablespace_name = fs.tablespace_name))
GROUP BY df.tablespace_name
ORDER BY df.tablespace_name

Joel Pereira
Solutions Architect
uptime software ...because downtime is not an option

#3 Edward.Moscardini

Edward.Moscardini

    Member

  • Members
  • Pip
  • 8 posts

Posted 31 August 2009 - 02:07 PM

Hi Joel,

All most all of our tablespaces have autoextend turned on, so we're getting false positives returned from uptime continuously. Could we work with you to create a monitor that takes autoextend into account when monitoring the oracle tablespaces? If you need a query to perform this:

CODE
select distinct
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc


Thanks,

Edward Moscardini

#4 Edward.Moscardini

Edward.Moscardini

    Member

  • Members
  • Pip
  • 8 posts

Posted 01 September 2009 - 07:21 PM

I opened case 00025148 for this enhancement(fix).

As a work around we created an oracle function from the query below and used "Oracle Query Monitor" to run that function and report on the returned text.

We're in the process of altering the query to add the names of the tablespaces in crit/warn status.

CODE
select
decode(
max(round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024))))),
100, 'CRIT',
99, 'CRIT',
98, 'CRIT',
97, 'CRIT',
96, 'CRIT',
95, 'CRIT',
94, 'WARN',
93, 'WARN',
92, 'WARN',
91, 'WANN',
90, 'WARN',
89, 'WARN',
88, 'WARN',
87, 'WARN',
86, 'WARN',
85, 'WARN',
  'OK') STATUS
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users