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 Answer 1

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, 2017 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, 2017 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, 2017 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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