Maximum no. of datafile with parameter DB_FILES and size of datafile

[Former Member]

I am working on sap 4.6C with oracle 9.2.0.7.0 and my OS is AIX 5.3

my initSID.ora file consist fo DB_FILES = 1022 which means I can add maximum to 1022 datafiles for a tablespace. Now we are nearing to the figure and worried for increasing the parameter DB_FILES value but as oracle document it is mentioned that the value for parameter is OS dependent. I want suggestion regarding the how to increase the value of parameter and can it be given ulimited value.

The other query is that now we have a limit of 2GB size for a datafile so every time when a datafile is added to a tablespace it size is 2GB can I increase the size of the datafile to 4GB/6GB/8GB with AIX 5.3 as os system if possible how can it be achieved.

Thanks

Regards

Jairaj

[fidel_vales · Accepted solution]

Hello,

I think you are a little confused.

DB_FILES is a "soft" value, and indicate the maximun number of datafiles on the <b>database</b> not tablespace. You can check the oracle documentation:

[quote=Oracle Reference]

<i>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.</i>

[quote=Oracle Reference]

There is a hard limit ( regardless of the value of DB_FILES ) You can see the note 606395 for details:

<quote>

1. Physical database limits

o Database files

<b> maximum per tablespace : on most operating systems 1022 </b>

<b> maximum per database : 65533 total, further limited by the

db_files init<SID>.ora parameter </b>

Database file size: see note 129439 for OS-specifics

<quote>

This means that there is NO posibility of having MORE than 1022 files per tablespace, no matter what.

Also it is not possible to have more than 65533 files in total.

For your second query the answer is on the note 129439 "Maximum file sizes with Oracle" ( also mentioned on the previous note )

PS: Looks like stefan was faster than me

Note 1: MAXDATAFILES is not a hardlimit in 9i either

Note 2: The limites mentioned in the note 606395 are also valid for 10g

Message was edited by:

Fidel Vales

[stefan_koehler]

Hello Jairaj,

the init parameter db_files is just a "soft limit".

The really important thing is... when you created your database what did you specify for the parameter MAXDATAFILES ?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5004.htm#i2061233

If you don't know - you can dump the controlfile by executing the following command:

alter database backup controlfile to trace;

After that you have a tracefile under /oracle/SID/saptrace/usertrace which includes that information.

As you said that you have Oracle 9i ... if you reach this limit you are not able to add more datafiles... in this case you have to recreate your controlfile with a higher MAXDATAFILES.

If you would have Oracle 10gR2 .. this value is also not a "hard limit"... it is expanded dynamically... have a look here:

http://flyhorse127.blogspot.com/2005/10/oracle-database-10g-release-2-online.html

Btw. we have datafiles until 10 GB on AIX 5L (5.3)

Regards

Stefan