ORA-27038
Tried to add a data file to a tablespace, but it failed with ORA-27038.
SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-01119: error in creating database file '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf'
ORA-27038: created file already exists
Additional information: 1
ORA-27038 means that the data file you want to add to the database is already existing in the operating system, although it's not in the database.
For adding a redo logfile, we might also see ORA-27038 if there already has a file with the same name.
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/ORCLCDB/redo04.log' size 1g;
alter database add logfile group 4 '/u01/app/oracle/oradata/ORCLCDB/redo04.log' size 1g
*
ERROR at line 1:
ORA-00301: error in adding log file '/u01/app/oracle/oradata/ORCLCDB/redo04.log' - file cannot be created
ORA-27038: created file already exists
Additional information: 1
Solutions
The file you specified in the statement may be formerly a data file. In such situation, you have several options:
- Drop the file from the operating system.
- Use another file name for the data file.
- Reuse the old file.
In this case, we add REUSE keyword to indicate that Oracle can safely take over and reuse the file.
SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf' size 10m reuse autoextend on next 10m maxsize unlimited;
Tablespace altered.
As you can see, we added REUSE keyword right after SIZE clause.
In Oracle documentation, there're more variations about file_specification.