How to Resolve ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

  • by

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.

Leave a Reply

Your email address will not be published. Required fields are marked *