Skip to Main Content
  • Questions

  • Max datafile size for 8.1.6.1.1 db on NT4?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Eric.

Asked: August 04, 2000 - 9:34 pm UTC

Last updated: May 05, 2005 - 8:11 am UTC

Version: 8.1.6.1.1

Viewed 1000+ times

You Asked

What is the max datafile size for 8.1.6 db on NT4? The db_block_size for this particular db = 2k but I'd like to know all sizes and reference to where I can find this info as well.

Are there other factors to consider? - e.g., what's the recommended largest size for optimal performance? manageability?
- especially I/O concerns?

This NT4 server is, of course, NTFS.

Thanks,

Eric Myers


and Tom said...

With 8.1.6 as you have, the limits are:

Windows NT Maximum 4Gb files on FAT
Theoretical 16Tb on NTFS

(note that regardless of the file size, the maximum extent size is always 2gig)

Some advantages of files larger than 2Gb:

Less files to manage for smaller databases.
Less file handle resources required


Some disadvantages of files larger than 2Gb:

The unit of recovery is larger. A 2Gb file may take between 15 minutes and 1 hour to backup / restore depending on the backup media and disk speeds. An 8Gb file may take 4 times as long.

Parallelism of backup / recovery operations may be impacted.

There may be platform specific limitations - Eg: Asynchronous IO operations may be serialised above the 2Gb mark.

As handling of files above 2Gb may need patches, special configuration etc.. there is an increased risk involved as opposed to smaller files. Eg: On certain AIX releases Asynchronous IO serialises above 2Gb.

Your backup tool might not operate with >2gig files.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

data file size

A reader, May 05, 2005 - 3:14 am UTC

Hi Tom,
It's been a while since the original question was asked ...

someone here said - we can go for larger data file size because AIX enables that
I tried to challange this by asking for criteria but wasn't in position to get an answer or to have a discussion on its selection - was just told they'll do it that way
can you please give your view as of critieria for selecting the data file size
thanks!

Tom Kyte
May 05, 2005 - 7:24 am UTC

the criteria should be

what are you comfortable with and what makes your life easier.

A 100 gig database, -- won't matter much. Pick whatever makes your administrative responsibilities easier.

A 5 terabyte database, you better (to make your life easier) consider some larger file sizes... but balance that with your backup/recovery process. If the file is your unit of recovery, you need to consider that as well (but it could be the block level for some types of errors)

A reader, May 05, 2005 - 7:40 am UTC

so to sum up:
-database size (from which we can get # of data files for selected max size)
-backup/recovery consideration
-operational tasks

ok?


Tom Kyte
May 05, 2005 - 8:11 am UTC

- os support
- tools you want to use against the file (can your thing that copies deal with a 16 gig file)


the are all practical concerns

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.