MAXDATAFILES和db_files的关系

DB：10.2.0.5

OS：windows 2008 64 bit

MAXDATAFILES和db_files都是设定datafile数量的上限值。MAXDATAFILES是在controlfile里的，而db_files是在初始化文件里面设定的。

那datafiles的上限值到底是由谁决定呢，

1 查看contrifle的MAXDATAFILES

select records_total,records_used from v$controlfile_record_section where type = 'DATAFILE'

records_total  records_used

45                   44

show parameter db_files

200

再添加两个datafile，系统并没有报错，发现controlfile自动扩展了

alter tablespace SMTRTIDX add datafile 'E:\oradata\sfcdb\SMTRTIDX02.dbf' size 10m
Mon Apr 14 20:39:20 China Standard Time 2014
Completed: alter tablespace SMTRTIDX add datafile 'E:\oradata\sfcdb\SMTRTIDX02.dbf' size 10m
Mon Apr 14 20:39:26 China Standard Time 2014
alter tablespace SMTRTIDX add datafile 'E:\oradata\sfcdb\SMTRTIDX03.dbf' size 10m
Expanded controlfile section 4 from 45 to 90 records
Requested to grow by 45 records; added 1 blocks of records
Completed: alter tablespace SMTRTIDX add datafile 'E:\oradata\sfcdb\SMTRTIDX03.dbf' size 10m

但是如果db_files达到上限就无法再增加datafiles了

SQL> alter tablespace TBSXXWB add datafile 'E:\oradata\sfcdb\TBSXXWB02.dbf' size
 10m;
alter tablespace TBSXXWB add datafile 'E:\oradata\sfcdb\TBSXXWB02.dbf' size 10m
*
ERROR at line 1:

ORA-00059: maximum number of DB_FILES exceeded

数据库中真正的最大的datafiles 的个数由DB_FILES 参数决定，虽然控制文件中也有限制，但是这个控制文件中的限制参数会自动的增加，直到到达DB_FILES的值

参考:

kccrsz: expanded controlfile message in "alert.log" (文档 ID 101020.1) 转到底部 修改时间:2013-4-11类型:PROBLEM Problem Description ------------------- The "alert.log" file shows the following message: kccrsz: expanded controlfile section 4 from 17 to 18 records number of logical blocks in section remains at 2 Completed: alter tablespace tb1 add datafile Solution Description -------------------- Starting in Oracle8 the controlfile is automatically expanded. A new datafile is added beyond the maxdatafiles limited specified in the control file. This also happens if new records are added to the "log_history" section of the control files and there are no entries that could be replaced. The routine that performs the expansion writes this message to the alert log. The messages specifies the section that was expanded and the amount of the expansion. Please note that this message cannot be turned off. The automatic expansion only occurs up to the limit of the "init.ora" parameter "DB_FILES". References: ----------- Server SQL Reference: create controlfile Additional Search Words ----------------------- kccrsz controlfile expanded

kccrsz: expanded controlfile message in "alert.log" (文档 ID 101020.1) 转到底部 修改时间:2013-4-11类型:PROBLEM Problem Description ------------------- The "alert.log" file shows the following message: kccrsz: expanded controlfile section 4 from 17 to 18 records number of logical blocks in section remains at 2 Completed: alter tablespace tb1 add datafile Solution Description -------------------- Starting in Oracle8 the controlfile is automatically expanded. A new datafile is added beyond the maxdatafiles limited specified in the control file. This also happens if new records are added to the "log_history" section of the control files and there are no entries that could be replaced. The routine that performs the expansion writes this message to the alert log. The messages specifies the section that was expanded and the amount of the expansion. Please note that this message cannot be turned off. The automatic expansion only occurs up to the limit of the "init.ora" parameter "DB_FILES". References: ----------- Server SQL Reference: create controlfile Additional Search Words ----------------------- kccrsz controlfile expanded