Re: Oracle Datafile size in Windows NTFS

"Yareck" <jpalka_at_interia.KILLSPAM.pl> wrote in message news:asf9l5$o71$1_at_news.telbank.pl...
> Użytkownik shafeek napisał:
> > Hi all
> >
> > Is there any limitation of the size of the datafile for NTFS.
> >
> > Thanks in advance.
>
> Windows2000Server + NTFS + Oracle 9i2, limitation is 4GB.
>
> Beyond this size Oracle reports an error in alert.log:
> ORA-01114: IO error writing block to file 201 (block # 524041)
> ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file

Out of interest is this *exactly* 4GB. There was a bug in 8i where attempts to resize a datafile to exactly 4gb (or 8,12,16 etc) would encounter this error. It wouldn't at all surprise me if this bug remains in 9i (I don't have 4GB of space to clear for an experiment until I get home). There wasn't however a file size limit. I know this because I was resizing a file down from an incorrect and inappropriate size (11gb or so the result of autoextend) to a size I was comfortable with. I picked 4gb - result one dead financial system :(. Similar bugs with 2gb and 4gb datafiles have existed on various operating systems and various versions of Oracle.

Up to 8i, and I'd be amazed if it didn't apply to 9i, there was no 4gb limit as such for a datafile. There is however a limit to the number of blocks that an Oracle datafile can hold so any one datafile is limited to a size of db_block_size*max_number_of_block_per_datafile. For an 8k block size this limit is 32gb. All this of course presumes that the operating system can handle files of this size, which NTFS can but other file systems may not.

In terms of what is a *sensible* datafile size, the primary consideration is probably backup and recovery as without RMAN a single datafile is the smallest unit to backup or recover. FWIW I use files uniformly of size 2049mb (2gb+1mb) on the basis that backup and recovery is pretty fast and that a uniform file size is a good idea if you ever wish to move datafiles about to balance IO. It also avoids any future bugs with a round 2gb file size, the only thing that might leap out would be that since I use locally managed tablespaces a size of 2gb+64k for the bitmap would make a little bit more sense.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************