How to Increase Datafiles on a Tablespace When Hitting "ORA-01686: max # files (1023) reached for the tablespace"

Problem:

ORA-01686 error was encountered when adding files to a tablespace.

Is there a way to add new data files to this tablespace?

SQL> begin
for i in 1..96
loop
execute immediate 'alter tablespace data add datafile size 32767M autoextend on maxsize unlimited' :
end loop:
end;
/

begin
ERROR at line 1:
ORA-01686: max # files (1023) reached for the tablespace DATA
ORA-96512: at line 1

Solution:

Unfortunately, there are no parameters or commands to lift this limitation to add more than 1022 files to a tablespace.

Maximum number of data files per tablespace (1022) is a physical database limit, and this cannot be changed.

Database Reference documentation:
https://docs.oracle.com/database/121/REFRN/GUID-939CB455-783E-458A-A2E8-81172B990FE9.htm#REFRN0042

A.2 Physical Database Limits

Database files (Maximum per tablespace) : Operating system dependent; usually 1022

No more data files can be added to this tablespace, but following actions can be tried:

- Resize the existing files in the tablespace, or make them auto extensible.

- Move some objects to different tablespace, to make space in the current tablespace.

- Partition the objects among multiple tablespaces.

Reference:

How to Increase Datafiles on a Tablespace When Hitting "ORA-01686: max # files (1023) reached for the tablespace" (Doc ID 2706122.1)