0

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

1

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.

3
  • I know that syntax. But for learning purpose, I want to create a temporary dictionnary managed tablespace using the CREATE TABLESPACE statment with its TEMPORARY clause because I want to manually edit the DEFAULT STORAGE clause and make the tablespace DICTIONNARY MANAGED – murielle Mar 13 '17 at 11:48
  • [The Fine Manual] (docs.oracle.com/cd/E11882_01/server.112/e41084/…) says why you cannot make it dictionary managed. See the restrictions bit. – Raj Mar 13 '17 at 20:25
  • For what learning purposes? It's something you would never have reason to do. Dictionary managed TS is obsolete, and only kept around for backward compatibility. – EdStevens Mar 14 '17 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.