MAXIMUM datafiles added to single tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: MAXIMUM datafiles added to single tablespace

  1. #1
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    what is the maximum number of datafiles u have added for a single tablespace

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    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.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width