Oracle temp tablespace alter statement

Question

What is wrong with my query? I had look here but could not resolve the problem.

SQL> CREATE TEMPORARY TABLESPACE TEMPRM;

Tablespace created.

SQL> ALTER  TABLESPACE  TEMPRM ADD DATAFILE   '+TEMPDATA/rm/datafile/TEMPRM_temp01.dbf';
ALTER  TABLESPACE  TEMPRM ADD DATAFILE   '+TEMPDATA/rm/datafile/TEMPRM_temp01.dbf'
*
ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

is +TEMPDATA a directory?? try once with size option in command. — Shravan Yadav, Jul 30 '15 at 10:35
use :- size 2M autoextend off; instead of 2M give value accordingly — Shravan Yadav, Jul 30 '15 at 10:42
Same error.ALTER TABLESPACE TEMPRM ADD DATAFILE '+TEMPDATA/RM/DATAFILE/TEMPRM_temp01.dbf' SIZE 2M AUTOEXTEND OFF * ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE — Malatesh, Jul 30 '15 at 10:44
is the file name correct in ASM TEMPRM_temp01.dbf? check if this file exist in dba_temp_files table or in v$datafile. — Shravan Yadav, Jul 30 '15 at 10:48

Mureinik · Accepted Answer · 2015-07-30T14:13:36.487

11

A temporary tablespace is made up of tempfiles, not datafiles, so:

ALTER  TABLESPACE TEMPRM ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_temp01.dbf' SIZE 2G;
----------------------------- Here -^

edited Jul 30 '15 at 14:13

answered Jul 30 '15 at 10:57

Mureinik

297,002
52
306
350

It worked " ALTER TABLESPACE TEMPRM ADD TEMPFILE '+TEMPDATA/RM/DATAFILE/TEMPRM_temp01.dbf' SIZE 2M;" – Malatesh Jul 30 '15 at 14:10

Oracle temp tablespace alter statement

1 Answers1