Re: Number of database files

From: Robert Klemme <>
Date: Mon, 10 Mar 2008 01:36:04 -0700 (PDT)
Message-ID: <>

On Mar 9, 12:45 pm, "A.ISS" <a..._at_gee-mail.kom> wrote:
> Hi,
> I want to check (Oracle and higher) if the current number of data
> files is nearing the number specified by the db_files parameter.
> To achieve this I have devised the following select:
> select 'Number of datafiles ('||a.records_used||') nears db_files parameter ('||b.value||') : '
> ||(b.value-a.records_used) message
> v$parameter b
> where a.type = 'DATAFILE'
> and = 'db_files'
> and b.value - a.records_used <= 10
> /
> To my understanding increasing the db_files parameter will automatically
> increase the maxdatafiles value in the control file.
> The purpose of above check is to anticipate when to increase db_files and
> plan a database bounce and avoid ORA-00059 : maximum number of DB_FILES
> exceeded errors.

I don't understand this requirement. Since you should be creating DB files ahead of time you could as well monitor DB size and check the init param when creating a new datafile. At that point in time there should be enough time left to adjust db_files and schedule a maintenance window.

If you have a constantly growing database you can as well set it to max. From 10.2 docs:

"The maximum valid value is the maximum number of files, subject to operating system constraint, that will ever be specified for the database, including files to be added by ADD DATAFILE statements."


robert Received on Mon Mar 10 2008 - 03:36:04 CDT

Original text of this message