ORACLE-dbfile exceede

 

How to Resolve ORA-00059: maximum number of DB_FILES exceeded

https://logic.edchen.org/how-to-resolve-ora-00059-maximum-number-of-db_files-exceeded/

ORA-00059

Found ORA-00059 when creating a new tablespace or creating a pluggable database, it told us that the maximum number of data files (DB_FILES) had been reached and was likely going to be exceeded. You have to do something to lift the restriction.

ORA-00059: maximum number of DB_FILES exceeded

According to the error message, the problem is related to an initialization parameter called DB_FILES. Let's see current setting of DB_FILES.

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

200 is the default allowable and maximum number of DB_FILES.

Solution

We should raise the number, say 500 to avoid ORA-00059 and allow more data files (DB_FILES) within SPFILE scope. This is because DB_FILES cannot be modified online, so we have to change it in SPFILE only.

SQL> alter system set db_files=500 scope=spfile;

System altered.

Then we bounce the database.

SQL> shutdown immediate;
...
SQL> startup;
...

Check current DB_FILES again.

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     500

Now we can proceed our jobs. We see no more ORA-00059, but there might be more restrictions on adding data files should be taken care of.