Adding datafile

[Mariel Gonzales Faylona]

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,
Allan

[A. Clay Stephenson · Accepted solution]

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.

[Indira Aramandla]

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

[Yogeeraj_1]

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?

thanks
yogeeraj

[Christian Marquardt_1]

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.

regards
Christian