Resizing a temporary tablespace TEMP

If you want to increase/resize the temporary tablespace TEMP which is having a temporary datafile temp01.dbf, then follow the steps below

1. Find out the tempfile name and size

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
-------------------------------------------------------------------------------

1 550491 27-JUN-08 3 1 ONLINE READ WRITE
20971520 2560 20971520 8192
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF


The following info is available from the above command

Tempfile name is C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF
size --> 2560 * 8192 = 20MB
2. So if you want to increase the tempfile size to 50MB use the following SQL

SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF'
resize 50m;

Database altered.

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------

1 550491 27-JUN-08 3 1 ONLINE READ WRITE
52428800 6400 20971520 8192
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF