Skip to content

How to Resolve ORA-01537: cannot add file

  • by

ORA-01537

Tried to add a data file to a permanent tablespace, but it failed with ORA-01537.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/ORCL/example01.dbf' - file already part of database

ORA-01537 means that the data file you want to add to the tablespace is already in the directory you specified. Furthermore, the data file is in use by the database. At least, the control file still keeps it in the data file list.

Solution

To solve ORA-01537, you have 2 options:

1. Use Different Name

The different name means different data file name or different directory name. In practice, we recommend to use a different file name.

SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example02.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace altered.

We solved it.

2. Use Same Name

If you insist the use the filename which is already in use, you have to rename or move the original data file, then add the data file again.

The main steps of renaming a data file are:

  1. Take the tablespace offline.
  2. Rename the physical data file in OS.
  3. Rename the data file in database.
  4. Take the tablespace online.

You may take a look.

Leave a Reply

Your email address will not be published.