Step:2 ,3 are not runing . it display error
"SQL> Alter tablespace temp coalesce ;
Alter tablespace temp coalesce
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE



SQL> alter tablespace temp
2 default storage (pctincrease 1);
alter tablespace temp
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
--------------------------------------------------------"
Regs
ramji


Originally posted by julian
The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.

In his case, the best solution is to:

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata1/.../temp02.dbf' size 15M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

drop tablespace temp including contents and datafiles;

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;