ORA-01641
Tried to add a data file to a tablespace, but it failed with ORA-01641.
SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf' size 10m autoextend on next 10m maxsize unlimited;
alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf' size 10m autoextend on next 10m maxsize unlimited
*
ERROR at line 1:
ORA-01641: tablespace 'EXAMPLE' is not online - cannot add data file
ORA-01641 means that the tablespace you want to add data file to is not online (i.e. writable), you have no way to add the file when the tablespace is OFFLINE or READ ONLY.
Solutions
For Offline Tablespace
First, we checked the status of the tablespace.
SQL> select status from dba_tablespaces where tablespace_name = 'EXAMPLE';
STATUS
---------
OFFLINE
Please make sure that all operations on this tablespace have been completed before doing anything. Otherwise, you might see errors when you try to online it.
To bring the tablespace online, we alter the tablespace like this.
SQL> alter tablespace example online;
Tablespace altered.
SQL> select status from dba_tablespaces where tablespace_name = 'EXAMPLE';
STATUS
---------
ONLINE
For Read Only Tablespace
We usually make tablespace READ ONLY to protect it from changing.
SQL> select status from dba_tablespaces where tablespace_name = 'EXAMPLE';
STATUS
---------
READ ONLY
To make a READ ONLY tablespace writable, we revert it back to READ WRITE by altering it.
SQL> alter tablespace example read write;
Tablespace altered.
SQL> select status from dba_tablespaces where tablespace_name = 'EXAMPLE';
STATUS
---------
ONLINE
Now, you can add the data file to the tablespace.