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.