RE: datafile size

David,

        Data files are not exactly unlimited in size. To find the absolute max file size multiply block size by 4194303. This is the real limit. See Metalink Note:112011.1.

        Now if your like me with 8k blocks that means that one file can not exceed approximately 34GB (34,359,730,176 bytes) without having database corruption. Not sure if they've fixed this in 10G or not, but 9i still suffers from this problem.

        Otherwise I would agree file size is a matter of how big is your sysadmin happy with. Mine asked us to limit them to 6G for reasons of backup/restore. Something about OmniBack having a problem with files greater than 6GB. Could be just the version we're running. Talk to your sysadmin & make sure he/she's happy as well.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: David Sharples [mailto:davidsharples_at_gmail.com]=20 Sent: Tuesday, November 02, 2004 5:02 PM To: oracle-l_at_freelists.org
Subject: Re: datafile size

there really is no practical limit for datafile sizes. The argrument of no more than 2Gb is crass at best.

however you dont mention version but recovery is a consideration - do you want to recover a 4gb file or a 20 gb file? (this if you arent doing a full database restore).

So up to you what you want keep it is, if you want to increase a file to stop the db from being bounced - i say go for it.

I have used 17Gb datafiles before quite happily with no troubles

On Tue, 2 Nov 2004 17:01:37 -0500, Daniel Garant <dgarant_at_gotcorp.com> wrote:
> Hi
>=20
> Last week, while creating a new datafile for an expanding tablespace I
> received the ORA-00059 (max number of db_file exceeded). The DB is
used
> in a 24/24 env. So I can't easily plan a bounce to change my db_files
> parameter from 151 to 250 for example.
>=20
> Now, I'm wondering if the actual datafile size set by the previous DBA
> on this DB, which is 4 Gb, is ok or if I could start increasing this
> size to 8Gb for example. I normally set my datafile size to be a
> portion of the disk size so that I can easily move datafiles around if
> needed. But here, the disks are stripped on a big 900 Gb array. So
the
> need to keep the datafile size down is not really relevant anymore.
>=20
> This DB is approx. 600 Gb and growing of approx. 1 Gb every 2 weeks.
>=20
> Here are my questions:
>=20
> - What would be the best max size for my datafiles?
>=20
> - What sizes are you guys using?
>=20
> - What should I take into consideration when deciding this?
>=20
> Thx in advance
>=20
> Daniel
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l