Printable View
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
This is an Operating System restriction.
On Solaris, the max file size is 2G.
Cheers.
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
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.
See Note 62427.1 on MetaLink, good discussion of why/why not to use 2GB (or larger) file sizes.
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.
Can you explain some of the reasons why 100 x 2Gb files are unreasonable?
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
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.Quote:
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 hope you're not accusing Mr OracleDoc of being a whiner!Quote:
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.
Haa haa.