There are two major restrictions must be considered before you add a datafile to a tablespace. One is about the number limit of datafiles, the other is about the size limit of a datafile.
Allowable Number of Datafiles
DB_FILES in spfile
You can regards the initialization parameter as a soft limit, if you add a datafile and hit ORA-00059, you can increase the value to fit your requirement. To check current number limit of datafiles in spfile, please issue the following:
SQL> show parameter db_files
NAME TYPE VALUE
------------------------------------ ----------- ----------------------
db_files integer 200
MAXDATAFILES in controlfile
This parameter resides in the controlfile. Before 8i, the parameter is a hard limit relative to DB_FILES, if an attempt to add a datafiles exceeding the limit, it will fail. Increasing DB_FILES won't save you a thing, you must recreate a new controlfile with larger value of MAXDATAFILES.
But since 8i , if an attempt to add a datafile under DB_FILES limit, MAXDATAFILES will increase the value automatically to accommodate and manage more datafiles. To check current number limit of datafiles in controlfile, please issue the following:
SQL> SELECT records_total FROM v$controlfile_record_section WHERE type = 'DATAFILE';
RECORDS_TOTAL
-------------
1024
Operating System Restrictions
If a database adopts file system rather than raw devices or ASM as a storage, you need to think over OS limit. But I think most of databases never hit the limit all their life cycle.
The size limit of a datafile
Database limitation
Since Oracle can manage a small file no more than 4194302 (2^22 - 2) blocks, so the maximum size of a datafile in a small file tablespace depends on DB_BLOCK_SIZE. You may refer to the post: How Big a Data File Can Be.
Operation system limitation.
On older Unix like Solaris 8, the maximum file size is 2 GB, if you are maintaining 8i or 9i database that is running on those platform, you should be aware of the limitation.