This is all about the Relation B/W MAXDATAFILES and DB_FILES In Oralce Database.
–This will Give Us db_files value. max no of data files that we can add.
select value from v$parameter where name = ‘db_files’;
This will give the MAXDATAFILES specified at Control file level while creating Database.
select records_total from v$controlfile_record_section where type = ‘DATAFILE’;
Lets Start with a small case. I have Created My database with DB_FILES as 10.
C:\Windows\System32>sqlplus system/tejajun20
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
Let us have a look at the no of data files I have.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF</pre>
Let me add some data files to users tables
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
8
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
10
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
As I have already reached the max limit 10,Adding one more file will raise an Error.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ; alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m * ERROR at line 1: ORA-00059: maximum number of DB_FILES exceeded
ORA-00059:
maximum number of DB_FILES exceeded Cause: The value of the DB_FILES initialization parameter was exceeded. Action: Increase the value of the DB_FILES parameter and warm start.
Lets Try to increase the value to a small number.
SQL> alter system set db_files=20 scope=spfile;
System altered.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
20
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
Tablespace altered.
SQL>
See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).
Lets do that And verify what `ll happen.
SQL> alter system set db_files=150 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
14
--- In another window I am adding datafiles upto the limit "100"
SQL> /
COUNT(*)
----------
48
SQL> /
COUNT(*)
----------
55
SQL> /
COUNT(*)
----------
82
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;
Tablespace altered.
SQL>
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;
Tablespace altered.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
200
----Observe it Automatically changed..
SQL>
As Per Oracle Documents :
Consider Possible Limitations When Adding Datafiles to a Tablespace:
- You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
- Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
- Operating systems impose limits on the number and size of datafiles.
- The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.
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, then 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,then they should have the same value for this parameter.
For More Information Please Read:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343
Sriram Sanka - My Experiences with Databases & More 