Sunday, June 17, 2012

How many Maximum datafiles can i create in oracle database (MAXDATAFILES , DB_FILES)

Today i have got a question regarding error ORA-00059: maximum number of DB_FILES exceeded and MAXDATAFILES , DB_FILES

so here i just want to explain it.

showing the the values of datafiles

SQL> select value from v$parameter where name = 'db_files'

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE'

[For oracle database 8i and above)

if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.
so, you have to change the value onle DB_FILES init parameter if you want to create more datafiles from existing. you don't have to think about MAXDATAFILES 'S VALUE . I

The DB_FILES parameter limits the maximum number of datafile can exist in oracle database.We can't change this parameter dynamically.

MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command.

So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.

No comments: