If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Size of datafiles

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....

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.

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

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

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.