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:
- Take the tablespace offline.
- Rename the physical data file in OS.
- Rename the data file in database.
- Take the tablespace online.
You may take a look.