Re: autoextend system

From: Michael Ray <Michael.Ray_at_trw.com>
Date: Tue, 22 Aug 2000 07:34:34 -0700
Message-Id: <10597.115222@fatcity.com>

Kip,
We used AUTOEXTEND in 7.3.4 with no issues (I THINK!) but it was set at = UNLIMITED then (this was before I took over). We did have some mysterious = NT server crashes that we couldn't explain, which produced NO Oracle OR NT = errors. So I'm hoping to see if it was perhaps a datafile too large issue = (one file was over 16GB) like that Note I mentioned once we get another = server set up. Perhaps it blew the server away so quickly that Oracle and = NT couldn't write out anything?

Thanks for that script! I was going to ask where the maxsize and autoextend=  was kept so I could modify the scripts everyone else uses. Imagine my = surprise when I ran a "normal" script, and it told me nearly all my files = would not be able to allocate their next extent!

Michael Ray
DBA in training

>>> Kip Bryant wrote >>>

 From: Kip.Bryant_at_Vishay.com=20
 Date: Mon, 21 Aug 2000 14:40:43 -0700
 Subject: Re: autoextend system

Michael,

I get the impression that autoextend was kind of buggy pre V8. I finally started using it this year and so far so good. And I would definitely set = max
size, too. Anyway, here's a script for monitoring datafiles on autoextend= =20
that someone else posted. I added one minor revision to it to drop out = fully=20
extended autoextend datafiles.

column cur heading "Data|File|Size"
column max heading "Max|Auto|Extend"
column inc heading "Auto|Extend|Increm"
column ext heading "No Of|Free|Extnts"

column hifre heading "High|Free|Extent"
column totfre heading "Tot|Free|Space"
select substr(d.tablespace_name,1,11) tablespace,

        substr(d.file_name,20,17) filename,
        to_char((d.blocks * 8192)       /1048576,'9,999')||'M'  cur,
        to_char((f.maxextend * 8192)    /1048576,'9,999')||'M'  max,
        to_char((f.inc * 8192)          /1048576,'9,999')||'M'  inc,
        to_char(count(*),'9,999')                               ext,
        to_char(max(s.bytes)            /1048576,'9,999')||'M'  hifre,
        to_char(sum(s.bytes)            /1048576,'9,999')||'M'  totfre
from sys.dba_data_files d,sys.filext$ f,sys.dba_free_space s
where (d.file_id         =3D f.file#
  and s.file_id(+)      =3D f.file#) and

  not (d.blocks =3D f.maxextend)
group by d.tablespace_name,d.file_name,d.blocks,f.maxextend,f.inc Received on Tue Aug 22 2000 - 09:34:34 CDT