creating temporary tablespace in oracle 7.2

[cindynwl]

I need some urgent help on creating of temporary tablespace in oracle
7.2

when i exec this statement
CREATE TABLESPACE TEMP
DATAFILE '/u03/oradata/F/TempF_01.dbf' SIZE 1950M
DEFAULT STORAGE ( INITIAL 1000M NEXT 480M MINEXTENTS 1 MAXEXTENTS
121 PCTINCREASE 0 )
TEMPORARY;
---it says invalid create tablespace statement

so I

CREATE TABLESPACE TEMP
DATAFILE '/u03/oradata/F/TempF_01.dbf' SIZE 1950M
DEFAULT STORAGE ( INITIAL 1000M NEXT 480M MINEXTENTS 1 MAXEXTENTS
121 PCTINCREASE 0 );
----without the temporary word and I am able to create

when I want to alter

alter tablespace temp temporary; ----it says invalid alter statement.

can I know what i did wrong. Please help

Regards,
Cindy

[gandolf989]

If you really want help then you should give the exact error message that you got when you tried it. Also you should not create a 2gb tablespace with a default initial extent of 1gb. When you try this what happens. Oh and is there any reason why you don't upgrade to 9i SR2 or 10g?

DROP TABLESPACE temp INCLUDING CONTENTS;

CREATE TABLESPACE TEMP
DATAFILE '/u03/oradata/F/TempF_01.dbf' SIZE 1950M REUSE
DEFAULT STORAGE ( INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS
121 PCTINCREASE 0 ) TEMPORARY;

[cindynwl]

hi,

I am not able to do an upgrade as this is client db and they are not willing to perform an upgrade

below is the error

SQL> CREATE TABLESPACE TEMP
D 2 ATAFILE '/u03/oradata/F/TempF_01.dbf' SIZE 1950M
3 DEFAULT STORAGE ( INITIAL 1000M NEXT 480M MINEXTENTS 1 MAXEXTENTS 121 P
CTINCREASE 0 )
4 TEMPORARY;
TEMPORARY
*
ERROR at line 4:
ORA-02180: invalid option for CREATE TABLESPACE


SQL> CREATE temporary TABLESPACE TEMP
2 DATAFILE '/u03/oradata/F/TempF_01.dbf' SIZE 1950M
3 DEFAULT STORAGE ( INITIAL 1000M NEXT 480M MINEXTENTS 1 MAXEXTENTS 121 P
CTINCREASE 0 ) ;
CREATE temporary TABLESPACE TEMP
*
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> alter tablespace TEMP temporary;
alter tablespace TEMP temporary
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

[hrishy]

Hi

True temporary tablepsace with the tempfile option were not available in 7.2 thats why the error.

You cannot use the temporary keyword in 7.2 at all.

regards
Hrishy