Size of datafiles
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Size of datafiles

  1. #1
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340

    Size of datafiles

    Hi All,

    I am using Locally managed tablespaces in Oracle 8i (64 Bit) / Solaris (64 Bit) environment.

    What should be the max size of datafiles allocated to a locally managed tablespace. Currently I hv tekpt them in Autoextend Mode. But I need to keep a check on the size so that I may not end up into problems later.

    This is for our DW project.

    Please help me understand.

    Could anyone point me to some good article about Locally Managed Tablespaces....

    Regards
    Vijay
    --------------------------
    The Time has come ....

  2. #2
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    This is an Operating System restriction.

    On Solaris, the max file size is 2G.

    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    The max file size on Solaris in not 2Gb.

    The max size should be what you think they would grow to without blowing your filesystem

  4. #4
    Join Date
    Aug 2003
    Location
    Dhahran
    Posts
    33
    Why don't you just try an experiment?

    Make a big file. Make a copy of the big file. Copy the big file onto the end of the copy again and again until it breaks.

    Check it on your file systems to make sure they are all the same.

    That way you don't get misled by an answer that depends on the local configuration of someone else's file systems.

    My system has a number of file systems that were added over the past few years and have not been rationalized in some time. This morning I discovered a file system that had not been converted to handle 2+ GB files. I was taken by surprise since I don't often use 2+ GB files but haven't run into any problems with them for some time.

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    See Note 62427.1 on MetaLink, good discussion of why/why not to use 2GB (or larger) file sizes.

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Good input fellas,

    Here's more to spark the debate..

    I know there are some backup tools that wont accept files larger than 2gigs for an example; up until the latest release of Netbackup you couldn't backup files larger than 2 gig.

    The main thing that I take into consideration when creating tablespaces is how big are they going to grow and the managability of the datafiles associated with them. Speaking from past experience, at one time I had a tablespace that was over 202 gigs in size with individual tables of 24 gigs. If I were to create that tablespace using 2 gig files it would be totally unmanageable. Even if I put some tables of that size (24gig) into their own tablespace you'd have 12 or more datafiles associated with that tablespace.

    In a data warehouse enviornments it's not uncommon to have datafiles bigger than 2 gigs. Could you imagine having a 200 gig tablespace with 100 2 gig datafiles?

    What it basically boils down to is size and manageablity of the database and the ability of OS to accept large file sizes.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you explain some of the reasons why 100 x 2Gb files are unreasonable?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Don't get me wrong 100 datafiles for an entire database is not unreasonable or unmanageable. I was simply implying that 100 some odd datafiles for a single tablespace is. Especially when it comes to transportablity (if that's a word). Case in point... Say if I have to clone my database to another server. If I've got thousands of 2 gig datafiles spread out over the server, writing a script to make sure all of them get to the proper place on the new server would be a real pain in the ding ding.

    I guess it comes down to whatever it is that "you" consider unreasonable or unmanageable. I know for me trying to manage a 1.2 terrabyte database working with 2 gig datafiles isn't.

    Just my $.02
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by OracleDoc
    Case in point... Say if I have to clone my database to another server. If I've got thousands of 2 gig datafiles spread out over the server, writing a script to make sure all of them get to the proper place on the new server would be a real pain in the ding ding.
    I can see why you think it's a pain. Personally, I automated that process in 1996, so I don't even think about it anymore.
    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."

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by marist89
    I can see why you think it's a pain. Personally, I automated that process in 1996, so I don't even think about it anymore.
    I hope you're not accusing Mr OracleDoc of being a whiner!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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