Skip to content

How to Resolve ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

  • by

ORA-25139

Tried to create a temporary tablespace, but it failed with ORA-25139.

SQL> create temporary tablespace temp1 datafile '/u01/app/oracle/oradata/ORCL/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited;
create temporary tablespace temp1 datafile '/u01/app/oracle/oradata/ORCL/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited
                                  *
ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

ORA-25139 means that the temporary tablespace we want to create should consist of temp files instead of data files.

Solution

To solve ORA-25139, we should use TEMPFILE to specify the file type of the tablespace in the statement.

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ORCL/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

Leave a Reply

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