MAXIMUM datafiles added to single tablespace

Folks

I wanted to know how many datafiles in numbers u have added for a single tablespace , i mean adding the datafile with the alter tablespace add datafile option.

I have oracle 8.1.7 on linux and we started the project 1 year back and in Production DB so far i have added 6 datafiles.

SQL> select file_name from dba_data_files where tablespace_name='PRONTOMSP';
FILE_NAME
----------------------------------------------------------------------/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP01.dbf
/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP02.dbf
/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP03.dbf
/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP04.dbf
/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP05.dbf
/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP06.dbf
/buildback/ora/app/oracle/oradata/HNS/PRONTOMSP07.dbf

but each time i added a new datafile with size 1 Gb which is ok for months , and then on third month i have to add a new datafile.

so this thing comes in my mind what to do , so far 6 i have added down the time future days to come what would be the number it will reach :)

whats the maximum number of datafiles u have added so far

thanks in advance
Gajanan

I believe it is some large number like 1024. However, by the time you get to about 32, you will probably want to start distributing your datafiles on multiple mount points for better performance. You can RTFM at http://tahiti.oracle.com to get the exact number.

what is the maximum number of datafiles u have added for a single tablespace

What good will this information do for you??????

What difference does it make to you if I tell you that I have never had a tablespace with more than 1 datafile? Or if I tell you that I allways have my tablespaces containing 1,000 datafiles?

jurij The Oracle master and one of my favourites here in this forum :)

I just wanted to know and see , that what i have done is correct ( ie by manually adding the datafile each time when the tablespace gets full) or i should have given autoextend option in the begining itself.

I am worried about the growing number of datafiles in the future days to come.

Quote:

---

Originally posted by gajananpkini
I just wanted to know and see , that what i have done is correct ( ie by manually adding the datafile each time when the tablespace gets full) or i should have given autoextend option in the begining itself.

---

well let's do some mathematics, maximum size of a datafile normally is 2gb unless you enable large fs support, a tablespace can have at least 1024 datafiles, multiply 2gb and 1024 get you 2 TB, do you have a tablespace which is gonna be 2TB?

Plus mariest has already told you where to look this sort of things, how about make some effort yourself and look the doc?

gajananpkini,

You need to estimate the table growth properly, and size your tablespace accordingly.

Did you consider 2 GB data file instead of 1 GB datafile?

Did you talk to the appln team about what this tablespace (tables) is being used for?

How long do you want to keep the data in those tablespace?

Tamil