AutoExtend Unlimited

I used to create uniform 2GB Data Files without AutoExtend. Reason being, I liked to know what was creating rows in my database and the "set it and forget it" attitude was OK for Test Systems. But if you don't keep on top of things, you might find yourself with a Table with 175m rows of unneeded data.

Let's talk about AutoExtend - Unlimited with a simple command:

alter database datafile 'c:\oradata\mysid\XYZ.dbf' autoextend on next 10m maxsize unlimited;

Turns out, Data files are not exactly unlimited in size (even though you may check the box or specify it like in the SQL above), so the term "Unlimited" refers to the maximun size your datafile is allowed to reach, and this depends on the Oracle Block Size. Oracle 10g does have a maximun Data File limit of 128GB by the way.

To find your real maximum file size, multiply block size by 4194303 (2 ^22). This is the actual maximum size:

Maximum datafile size = db_block_size * maximum number of blocks (which is 4194303)

A datafile cannot be oversized, otherwise it could get corrupted. Let's say if your database is 8k blocks (which most are) - that means that one file can not exceed approximately 34GB (34,359,730,176 bytes) without having database corruption.

Setting your Data Files to AUTOEXTEND and UNLIMITED growth could give you a false sense of well being as your Tablespace reaches a hard ceiling. Periodically check your Data Files for once approaching this hard-limit.