Temporary Tablesapce

[Rangi]

Hi folks,

The following error i got while i created a temporary tablespace.

CREATE TEMPORARY TABLESPACE TEMP
DATAFILE 'D:\ERP\TEMP.DBF' SIZE 1000M REUSE AUTOEXTEND ON
DEFAULT STORAGE ( INITIAL 100K
NEXT 100K
MAXEXTENTS UNLIMITED
PCTINCREASE 0) ;

I got the error:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE.

Can anyone point out whats wrong in this.

Thanx.

[kris109]

It should be TEMPFILE not DATAFILE. Your syntax is for locally managed temporary tablespace hence use extent management local uniform size 100K.

CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE 'D:\ERP\TEMP.DBF' SIZE 1000M REUSE AUTOEXTEND ON
EXTENT MANGEMENT LOCAL UNIFORM SIZE 100K;

[Rangi]

I am getting the same error msg,, even if i changed as u said.

[kris109]

What is your database version?

SQL> CREATE TEMPORARY TABLESPACE TEMP12
TEMPFILE 'C:\oracle\oradata\junk\TEMP12.DBF' SIZE 10M REUSE
AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K

Tablespace created.

[aspdba]

TRY

CREATE TABLESPACE TEMP
DATAFILE 'D:\ERP\TEMP.DBF' SIZE 1000M REUSE AUTOEXTEND ON
DEFAULT STORAGE ( INITIAL 100K
NEXT 100K
MAXEXTENTS UNLIMITED
PCTINCREASE 0) TEMPORARY ;

[Rangi]

Thanx Kris.

I created based on ur query and created successfully.

DB- Production 9.2
OS - Win2k.