How to shrink temporary tablespace and temporary file in Oracle

How to shrink temporary tablespace and temporary file in Oracle

After the database is used for a period of time, especially when frequent imp/exp operations and sorting operations exist, the temporary tablespace will become larger and larger, and the corresponding file will also be large.

From Oracle 11g, you can use the Shrink command to Shrink temporary tablespace and temporary files. Oracle help document: Alter and Maintaining Tabelspaces

ALTER TABLESPACE SHRINK SPACE

ALTER TABLESPACE SHRINK TEMPFILE

The Shrink command can use the keep option to specify the lower limit of shrinkage.

You can also use the Resize command to adjust the data file size. Oracle help document: Changing Datafile Size

ALTER DATABASE TEMPFILE RESIZE

Of course, the lower limit of narrowing down and adjusting cannot be smaller than the actual data size.