Quote Originally Posted by dba-k
Should there be a threshold for free space on the disk where the datafiles are located. As the SYSTEM
and UNDO tablespaces are on AUTOEXTEND mode, is it possible to find how much space they would swell
on at the worst peak.
I agree to Davey's statement not to use autoextend. When unusual jobs (yearly journals / batch jobs, or mass-updates during application-software upgrades) are tested on fully sized pre-production then just watch the rollback segments and the archivelog-volume (that archivelog destination doesn't run full).

Quote Originally Posted by dba-k
And lastly, is it better to increase the datafile size or add another datafile to increase tablespace size.
The database in question is an OLTP Production database on Oracle 9205 & Solaris 5.8
Regarding file size: As most of my databases medium OLTP-databases (300 GB to 2 TB) I limit filesize to 4 GB, in history rather 2 GB.

*) Running backup in 5 streams and 7 files per backup-set requires at least 35 equal big datafiles. Having a few small files for the small and tiny tablespaces and all the usage in 10 x 30 GB the 5 backup streams will work only a few minutes, and the last one will run very long...
==> What's your current volume and backup-duration ?

*) when copying the data files for creating your fully-sized pre-production system the same rules apply.
*) The situations when filesizes greater than 2 GB or 4 GB cause problems are decreasing year by year, but e.g. tar will for longer time be limited to 8 GB files. In history I remember a copy-tool for secure file transfer stopped ad 2 GB and a compression tool too.
*) If you really want to use excessive file sizes, then make sure that all tools used and the backup system support such huge files.