Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
Showing results for 
Search instead for 
Did you mean: 

Adding datafile

Go to solution

Adding datafile

I have added a new datafile on my db. The problem is that i have overlooked the number of datafiles allowed to the db. My alter tablespace statement returns an error message. I have also checked the alert.log and have the same error. My question is, What would be the impact to the database?

best Regards,
A. Clay Stephenson
Acclaimed Contributor

Re: Adding datafile

If I understand your question, all you need to do is shutdown the database and modify the "db_files" value in your init.ora file. I assume this is Oracle although you didn't specify it. After changing the init.ora file, restart the database and you should be good to go.
If it ain't broke, I can fix that.
Indira Aramandla
Honored Contributor

Re: Adding datafile

Hi Allan,

In the syntax for CREATE DATABASE, you will find a parameter called "MAXDATAFILES". The value that is entered for this parameter (or some default if the parameter is left out) is stored in the CONTROL FILE upon database creation. This is the "hard" limit on the number of datafiles which can be associated with this particular database.

In the init.ora init.ora, there is a parameter "DB_FILES". This is the limit on the total number of files associated with a particular INSTANCE of a database. Since this parameter can be changed simply by editing the init.ora and shutting down and restarting the database, it is considered a "soft" limit.

DB_FILES specifies the maximum number of database files that can be opened for this database. 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.

If you increase the value of DB_FILES, you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database, they should have the same value for this parameter.

Attached is a document explaining about MAXDATAFILES and DB_FILES parameter.

Indira A
Never give up, Keep Trying
Honored Contributor

Re: Adding datafile

hi allan,

what version of database are you running?

can you also post the command and error you are getting when you are trying to alter the tablespace?

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Christian Marquardt_1
Regular Advisor

Re: Adding datafile

It's not so easy to increase the value for MAXDATAFILES. One way is to recreated the entire database another way is to recreate the controlfiles only.
If you have to recreate the controlfile(s) look into the attached file how to do this.