Re: File limit reached

From: Erwin Dondorp <erwindon_at_wxs.nl>
Date: Sat, 04 Sep 1999 12:12:07 +0200
Message-ID: <37D0F076.C16EC1FE@wxs.nl>

The number of files that can be open depends on 3 things:

- 1) how many files can your operarating system handle
- 2) The parameter db_files
- 3) The size of the controlfile:

[from the 7.3.4 Server Reference Guide] For example, the maximum number of database files allowed by the Oracle7 Server can be reduced for a particular database by specifying a lower value for the MAXDATAFILES option of the CREATE DATABASE command. This limit is then recorded in the control file and cannot be exceeded for the life of that database. You can alter the value of the initialization parameter DB_FILES to a value less than MAXDATAFILES for a particular instance. The maximum number of database files is then limited to the number specified by DB_FILES for the life of that instance.

[from the 8.0 Administrators Guide]
control file upper bound: When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an ini-tial size of the datafile portion of the control file. Later, if you add a file whose number exceeds MAXDATAFILES but is less than or equal to DB_FILES, the control file automati-cally expands to allow the datafile portion to accommodate more files.

My conclusion is that you run a 7.x database and that the number of files that can be recorded in the controlfile has reached its limit of 30.
The bad news is that you have to recreate your database or upgrade to at least 8.0.
You can also resize a datafile (if your 7.x database supports it 7.0 does not, 7.3 does, not sure about 7.1 or 7.2) Or recreate only 1 tablespace with less (and larger) datafiles.

    Erwin

nightwalker_ru_at_my-deja.com wrote:

> Recently I encountered strange problem.
> Whenever I try to add datafile to any tablespace, I get error:
> ORA-01118 cannot add any more datafiles: limit of 30 exceeded
> In my init<SID>.ora file there is parameter db_files = 64
> so I thought that limit is 64 files. Or I am wrong?

--
Erwin Dondorp
<http://www.wxs.nl/~erwindon> Received on Sat Sep 04 1999 - 05:12:07 CDT