Why can’t I create temporary tablespace

oracletablespaces

I'm trying to create dictionary managed tablespace with the TEMPORARY clause and TEMPFILE clause of CREATE TABLESPACE statement. But oracle says I can't use the DEFAULT STORAGE clause doing that… What is the error here ?

CREATE TABLESPACE myTempTableSpace
TEMPFILE '/home/folder/tempfile.dbf' size 1M
DEFAULT STORAGE 
(
INITIAL 1M
PCTINCREASE 0
)
TEMPORARY
EXTENT MANAGEMENT DICTIONARY;

The error is :

ORA-02180: invalid option for CREATE TABLESPACE

Best Answer

To create a temp tablespace, the syntax is CREATE TEMPORARY TABLESPACE, not CREATE TABLESPACE.

CREATE TEMPORARY TABLESPACE myTempTableSpace
  TEMPFILE '/home/folder/tempfile.dbf' SIZE 1M
;

I wouldn't set any storage clauses, unless you have a good reason to do so.

Documentation link here.

Related Question