Skip to content

How to Resolve ORA-01641: tablespace is not online - cannot add data file

  • by

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

1. 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

2. 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.

Leave a Reply

Your email address will not be published.