Re: control files growing big

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:40716325$0$3310$ed9e5944_at_reading.news.pipex.net...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:406de6fa$0$438$afc38c87_at_news.optusnet.com.au...
> >
> > "vikram" <hemasudhaind_at_hotmail.com> wrote in message
> > news:5ce659ec.0404021251.45bd8b2_at_posting.google.com...
> > > hi,
> > >
> > > In the oracle server there is a system file "sys1per1.ora" this file
> > > is growing to the size of 2GB. But the actually data contained there
> > > is just 10mb. Can anybody advice why this is growing and what to do to
> > > reduce the size of the file.
> > >
> > > thanks
> > > vikram
> >
> > A good post. It illustrates nicely the reason why we have file naming
> > conventions. God knows what that file of yours is, because things that
end
> > with ".ora" are supposed to be configuration and initialisation files,
not
> > actual components of the database. Datafiles should have '.dbf'
extensions
> > and control files should have '.ctl' extensions, and then we can all
talk
> > the same language. Your thread title says it is a control file, yet
> control
> > files shouldn't have ".ora" extensions, nor can they grow that big.
> >
> > So the first thing is to sharpen up your definitions. What exactly is
this
> > file? A data file, a control file, a redo log or what?
> >
> > select name from v$datafile;
> > select name from v$controlfile;
> > select member from v$logfile;
> >
> > If you determine that it is a data file (as is most likely), first shoot
> the
> > person that named it as a ".ora" file, and then find out what is
actually
> > inside it. You'll first need to know what tablespace it is a part of:
>
> well it is not entirely beyond the realms of possibility that the person
to
> be shot would be someone at Oracle Corp. My money is on this being an 8.0
or
> early 8.1 database on Windows in which case the sys1per1.ora naming
> 'convention' is what dbca comes up with for a datafile belonging to the
> system tablespace of an instance called per1 using this tool. Obviously
this
> can be changed, but what a daft 'feature'. This doesn't happen with any
> currently supported version of dbca though (just checked against 817).

I don't remember that being the case, but I'll take your word for it.

Not as bad as calling redo logs "*.log", however, and having an enthusiastic SA who periodically deletes all "*.log" files on the grounds the system doesn't need all those text files hanging around..

Ooops.

:-(

>
> > On the question as to why it has grown to 2GB, the answer is: because
some
> > DBA somewhere was foolish enough to let it. Data files (and I am still
> > assuming that something of that size is indeed a data file) don't just
> grow
> > by themselves unless somebody has set the autoextend flag on it, which
is
> a
> > (generally) daft thing to do. So the first thing you ought to do is
>
> Much as I'm opposed to autoextend in general, ISTM that there is a case
for
> autoexend - with a max size clause - for the SYSTEM tablespace.

Hence the "(generally)" in my reply. SYSTEM is a law unto itself, and those 'laws' shouldn't be tinkered with. And yes, SYSTEM should (must) be autoextensible.

Regards
HJR

-- 
-------------------------------------------
Dizwell Informatics: http://www.dizwell.com
  -A mine of useful Oracle information-
          -Windows Laptop Rac-
    -Oracle Installations on Linux-
===========================================