Re: max datafile size

I can think of two reasons to allow files to go beyond 2 gigabytes:

(1) Management: There are fewer files to maintain in larger tablespaces.

(2) Design: Tablespaces can be more easily designed with one datafile each.

The fact that there is no 2-gigabyte limit for most systems that would like to go beyond this really places the burden on those who want to keep this obsolete restriction to justify it. I have not yet heard a good justification.

The issue raised about backing-up a large file is no different from the issue of backing up a tablespace with many smaller files each under 2 gigabytes.

However, the objection presented may really relate to tablespace size. If that is the case, I would agree that a well-designed database has many tablespaces with one datafile per tablespace. It also would have little free space both within extents as well as outside extents in the datafiles.

What I suspect is typical with databases that arbitrarily impose the 2-gigabyte limit is the following:

(1) Less than 7 tablespaces counting system, temp and rbs.

(2) Hundreds of equally sized 2-gigabyte files split among about 3 tablespaces.

(3) Lots of free space outside extents in the datafiles due to inadequately planned direct-path loads.

(4) Lots of free space within large locally managed tablespace extents.

Is that how your database looks?

Frank Hubeny

Howard J. Rogers wrote:

>Why on Earth you'd *want* a data file bigger than 2Gb beats me! You;ve got
>to back it up in its entirety, restore it in its entirety, and online and
>offline it in its entirety. More than 2Gb I would not go!
>
>Regards
>HJR
>--
>
>Oracle Resources : http://www.geocities.com/howardjr2000
>========================================
>
>
>"Ragnar Hark" <ragnar.hark.no.spam_at_mail.ee> wrote in message
>news:9rr0sd$fh0$1_at_kadri.ut.ee...
>
>>Hei !
>>I have 20GB reiserfs volume on the RH7.2, block size is 6K , but i cannot
>>create datafile larger tahn 2GB ?
>>my Oracle version is 8.1.7 and reiserfs supports filesize over 20GB.
>>
>>
>>
>>
>>
>
>
Received on Sat Nov 03 2001 - 14:17:35 CST