Max Limit in No. of DataFiles in Oracle 8.0.3

Hello,

Currently I'm running Oracle 8.0.3 in UNIX platform. Recently have hit the max limit on the no. of datafiles of 80. 80 was specified in our init.ora earlier.

1. What is the maximum limit indicated by Oracle (if any)? Seems to me that this limit can be increased via the init.ora file. So can I always increase whenever it has hit the limit that I have specified earlier?

2. If I don't increase the no. of datafiles and I resort to increasing the datafile size, what is the limit in the datafile size again? Noting that my HDD size is 6.1G. My default datafile size has been set to 200MB. Due to the recent limit, have increased to 400MB. But I can't simply keep increasing this datafile size infinitely right? Any better solution?

3. 'coz of the above stated problem, I'm thinking of exporting the tables into *.dmp. But with the huge amount of records, it would definitely take days to export or even hang the system. Think there are about a few millions records in each table.
Any better suggestion(s) on how this can be taken care of? I cannot increase the storage space due to some constraints. Could only off-load the data to other media devices or export the data out and reduce the number of records in the database.

Please advise.

Thanks a lot.

When you run CREATE DATABASE, you often have a parameter called MAXDATAFILES. The value that is entered for this parameter or some default value is stored in the CONTROL FILE upon DB creation. This is the so called "hard" limit on the number of datafiles which can be associated with this particular database. Remember DB, not instance!

In the init.ora you have DB_FILES. This is the limit on the total number of files associated with a particular INSTANCE of a database. As you know several instances can be associated with a single DB. This is called the "soft limit".

In your case, the default for MAXDATAFILES is 30 (Oracle 8 on UNIX) and your limit is 1022 per TS and 65536 per DB.

When creating you DB, you may of course set MAXDATAFILES to the maximum. I, for example, set it to the year I was born in :-) Too high value makes the CF unnesessary big. On the other hand, if you set DB_FILES to a high number, it increases the PGA.

hello julian

i don't quite understand your 1st paragraph. Since u've mentioned that the MAXDATAFILE limit set upon the creation of the database is the "hard" limit. Then in your last paragraph, how could you still set the maximum to your year that you were borned?

I don't quite see the relationship.

Care to enlighten? Or could I have mis-interpreted your statements?

btw, what does PGA stand for?

Thanks.

oh to add on, but I meant was that in my init.ora, if the MAXDATAFILE = 80, does it mean that during the creation of my database, it was already specified as 80 and as such this is the "hard" limit and I could not increase already?

Also, how do I associate "your limit is 1022 per TS and 65536 per DB. " with this issue?

Thanks

Quote:

---

Originally posted by cspie
oh to add on, but I meant was that in my init.ora, if the MAXDATAFILE = 80, does it mean that during the creation of my database, it was already specified as 80 and as such this is the "hard" limit and I could not increase already?

Also, how do I associate "your limit is 1022 per TS and 65536 per DB. " with this issue?

Thanks

---

1. I set MAXDATAFILES to 1965 when I create the DB. Thus I set a hard limit for all files associated with the DB. This number is stored in the CONTROL FILE upon database creation.

2. PGA means Program Global Area. It is allocated for every user process connected to ORACLE.

3. In the init.ora file you have DB_FILES, i.e., the soft limit for the instance, not the DB. Do you understand the difference between instance and database?

You can increase the value of 80 to the max of 65536 by recreating your CONTROL FILE. You may have at most 1022 files in a given tablespace.