How to Resolve ORA-27038: created file already exists

  • by

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/ORA19C1/ORCLPDB1/example03.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORA19C1/ORCLPDB1/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/ORA19C1/ORCLPDB1/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/ORA19C1/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/ORA19C1/ORCLPDB1/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.

Leave a Reply

Your email address will not be published. Required fields are marked *