Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: control files growing big

Re: control files growing big

From: Niall Litchfield <>
Date: Mon, 5 Apr 2004 14:46:12 +0100
Message-ID: <40716325$0$3310$>

"Howard J. Rogers" <> wrote in message news:406de6fa$0$438$
> "vikram" <> wrote in message
> > 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
> 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
> 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).

> 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
> by themselves unless somebody has set the autoextend flag on it, which is
> (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.

Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Mon Apr 05 2004 - 08:46:12 CDT

Original text of this message