Skip to content

How to Resolve ORA-01542: tablespace is offline, cannot allocate space in it

  • by

ORA-01542

Tried to move a table to another tablespace, but we got ORA-01542.

SQL> alter table employees move tablespace users online;
alter table employees move tablespace users online
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01542: tablespace 'EXAMPLE' is offline, cannot allocate space in it

ORA-01542 means that the tablespace involved in the statement is offline now, there's no way to access or re-allocate space for the segment.

Let's see the status of the tablespace by querying DBA_TABLESPACES.

SQL> select status from dba_tablespaces where tablespace_name = 'EXAMPLE';

STATUS
---------
OFFLINE

Then check the data files.

SQL> column name format a60;
SQL> select name, status from v$datafile order by file#;

NAME                                                         STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/system01.dbf         SYSTEM
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/sysaux01.dbf         ONLINE
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/undotbs01.dbf        ONLINE
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/users01.dbf          ONLINE
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf        OFFLINE
/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example02.dbf        OFFLINE

6 rows selected.

As we can see, data files that belong to tablespace EXAMPLE are offline.

Solution

To enable users to access the tablespace, we should bring it online.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select status from dba_tablespaces where tablespace_name = 'EXAMPLE';

STATUS
---------
ONLINE

Now we can do our job.

SQL> alter table employees move tablespace users online;

Table altered.

Leave a Reply

Your email address will not be published.