Applies to:Oracle Server - Enterprise Edition - Version: 220.127.116.11 to 18.104.22.168
Information in this document applies to any platform.
The TEMP tablespace was created with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid Error: ORA-1652 Text: unable to extend temp segment by %s in tablespace %s
Attempts have been made to "alter database datafile .. resize" which always fails with:
Error: ORA 3297 : file contains <
> blocks of data beyond requested RESIZE value
SQL> create temporary tablespace TEMP1 tempfile 'c:\temp01.dbf' size 100M extent management local uniform size 128K;
2) If the original tablespace is a default temporary tablespace, set the new tablespace as default temporary tablespace for all users in the database:
SQL> alter database default temporary tablespace TEMP1;
3) If necessary, explicitly re-assign specific users to the new tablespace:
SQL> alter user
temporary tablespace TEMP1;
4) Drop the old tablespace.
SQL> drop tablespace temp including contents.
Note: In Oracle 9i and on, the drop command will also drop datafiles at OS level:
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Note: Temporary tablespaces should appear "full" after a while in a normally running database. Extents are allocated once and then managed by the system. Rather than doing the rather expensive operation of "space management" (data dictionary updates), the system will allocate an extent in TEMP and then keep it and manage it itself. This is normal and to be expected and is not an indication that there is a lack of temporary space.
Reference Note: How to Shrink the datafile of Temporary Tablespace