Re: control files growing big

"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:

select name from v$tablespace
where ts#=(select ts# from v$datafile where name ='sys1per1.ora');

Then...

select segment_name from dba_segments where tablespace_name='xxxxx';

Hopefully that might make things a bit clearer for you as to what this is and why it is there.

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

alter database datafile '/xxx/yyyy/sys1per1.ora' autoextend off;

And then to try and shrink the file back down in size,

alter database datafile '/xxx/yyyy/sys1per1.ora' resize 50M;

...or whatever size you deem appropriate of course. If there's data encountered on the way down in size, the operation fails. You may end up having to export whatever objects are listed as being inside that data file, drop them, resize the file, and then import them.

For future posts, always include an operating system and an Oracle version, please.

Regards
HJR

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