ORA-03217
ORA-03217 means that the statement looks syntactically correct, but the action you performed is invalid for a temporary tablespace.
Let's check the content of ORA-03217.
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
Cause: invalid option for alter of temporary tablespace was specified
Action: Specify one of the valid options: ADD TEMPFILE, TEMPFILE ONLINE, TEMPFILE OFFLINE
Which means, the only valid options for a temporary tablespace in ALTER TABLESPACE statement are:
- ADD TEMPFILE
- TEMPFILE ONLINE
- TEMPFILE OFFLINE.
In this post, I list 2 error patterns of ORA-03217.
1. Offline Whole Temporary Tablespace
SQL> alter tablespace temp offline;
alter tablespace temp offline
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
You can't do this option on a temporary tablespace. All you can do is to offline all tempfiles in the temporary tablespace.
2. Coalesce Whole Temporary Tablespace
SQL> alter tablespace temp coalesce;
alter tablespace temp coalesce
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
Apparently, you can't do this option on a temporary tablespace either. If you want to make the temporary tablespace look smaller, you can shrink tempfiles.