Adding temp datafile to default temp tablepsace

First we need to determine what is the current default temp tablespace and that can be done using


SQL> col PROPERTY_NAME for a40
SQL> col PROPERTY_VALUE for a30
SQL> col DESCRIPTION for a70
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE                 DESCRIPTION
---------------------------------------- ------------------------------ ---------------------------------------
DEFAULT_TEMP_TABLESPACE                  TEMP                           Name of default temporary tablespace

SQL>

to add the datafile to temp tablespace:

alter tablespace temp add tempfile '/u01/app/oracle/oradata/new_location/tempnew.dbf' size 700M reuse  autoextend on maxsize 700M;