Max Datafile Size

[kwil38]

A little background....I'm using Oracle 9i on a Windows 2003 server

It seems like I've read somewhere that it is a good rule of thumb not to make datafiles over 2 GB for performance reasons. Can someone explain this to me in a little more detail?

Currently, I have a datafile that is 4 GB, of which 2.2 GB are being used. I'm wondering if I should decrease the size of this file and add another datafile to the tablespace.

Thanks

 

[SantaMufasa]

Kwil,

Actually, the "2GB" recommendation is not Oracle-performance related. The recommendation comes from the fact that some operating systems (both present and past) have/had problems when files exceed the 2GB threshold. Having run into this problem, myself, I can explain the symptoms very explicitly: The table whose block lies on the 2GB boundary becomes unreadable from that block to the end of that table. All other tables in the tablespace are still readable, both in front of and behind the 2GB threshold.

To avoid this issue altogether, I have all of my database data files in AUTOEXTEND mode to a "maxsize 2000M". Some of my tablespaces possess dozens of datafiles with no performance degradation at all.

If you wish to go to a 2GB strategy, you can
1) create a new tablespace with multiple 2GB-maximum files,
2) move all of the original (4GB) tablespace's objects to the new tablespace. (If you wish to see the fastest-by-far method to move objects, refer to the code appearing in the "Locally Managed Tablespaces" thread (thread186-900459), post from "16 Aug 04 18:36".
3) confirm that the original tablespace is empty
4) "DROP TABLESPACE <original_name>;" (If you get a "...tablespace must be empty..." error with this command, it is because the tablespace is not empty.)
5) rm/erase the datafile(s) that supported the dropped tablespace. (On some systems, you cannot drop the supporting datafiles until after a db shutdown.)

Another benefit of a 2GB file limit is that logistics are simpler (backup, moving, et cetera) for a 2GB file than a 20GB file.

Does this answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[kwil38]

Thanks Santa - just what I needed to know.

One other question for you - We're in the process of converting our data over to an Oracle DB and just completed the first "test" conversion. We'll be doing another "test" conversion in a few months, so I'm thinking that I'll leave it as a 4 GB file for now. Then, when I drop the data before second conversion, I'll decrease the datafile size to 2 GB and add another 2 GB file to the tablespace. Does that sound reasonable?

 

[alexhu]

I'd change it now if I were you.

Although Oracle will fill the file with segments 'in order' if you go above your 2GB desired size or Oracle allocates a segment above this 'limit', then you will have to go through extra steps (moving or exporting tables) to be able to shrink the file back to 2GB

Not really a pain, but you don't know what else you'll be doing that day

Alex

 

[kwil38]

Alex - It already has 2.2 GB of data in it, so that's why I was thinking about waiting to shrink it (along with adding another datafile) until I drop the data for the next test conversion.

As far as I know, all the data is accessable at this point.