ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

Issue :   Error while adding another file   to the existing temporary tablespace

  ALTER TABLESPACE temp ADD datafile '<path>/temp.dbf' size 1G reuse;

  ALTER TABLESPACE temp ADD datafile '<path>/temp.dbf' size 1G reuse
  *
   ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

Solution :

                you need to use the TEMPFILE clause, not the DATAFILE clause when working with temporary tablespaces, otherwise you will receive the error ORA-03217

         ALTER TABLESPACE temp ADD TEMPFILE  '<path>/temp.dbf'  size 1G reuse;

                




Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post