Oracle db_files and maxdatafiles parameters Don Burleson

The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. Historically (before Oracle8i) you need to be careful not to set db_files too high, else you would have DBWR (database writer) issues.

The maxdatafiles parameter is a different "hard limit" parameter.  When you issue a "create database" command, the value you specify for maxdatafiles is stored in your Oracle control files.  The default value of 32 is usually sufficient, but after Oracle8i there is no downside to using a larger value.

In practice, many Oracle DBA with large databases will segregate important tables and indexes into isolated tablespaces and datafiles to give them more control and detailed statistrics.

Fixing a maxdatafiles limit problem

In practice, the ORA-1118 occurs when your database has hit the MAXDATAFILES limit, usually during database maintenance.  Here are instructions from "Rhubarb" Stewart McGlaughlin, one of the best Oracle DBA's in North Carolina:

1. Shutdown database; Backup database
2. Start up database
3. From sqlplus as sysdba, type: alter database backup controlfile to trace;
4. Type: shutdown immediate:
5. Go to the operating system and go to the USER_DUMP_DEST directory
6. Find the newest trace file
7. Edit the trace file and change MAXDATAFILES to the new value. You will also need to delete all of the lines prior to the line that begins: STARTUP NOMOUNT. See sample text below (the example is for a database using ARCHIVELOG):

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE" RESETLOGS ARCHIVELOG

8. From sqlplus as sysdba, run the edited trace file from step 7.
9. Shutdown database and backup database