How To Recreate Temp Tablespace

When temp tablespace grows uncontrollably, it may be necessary to reduce its size. For this, the temp tablespace needs to be recreated in a smaller size. We can do this online.

First, let’s create a new temp tablespace. Suppose the current temp tablespace is called TEMP. We’re temporarily creating a new temp tablespace called TEMP1.

We set the system’s default temp tablespace as the new temp tablespace.

We’re dropping the old temp tablespace when there’s no session left using the old temp tablespace. We can find queries that use temp tablespace with the following query. You need to check all nodes if the database is RAC.

If you don’t have much time, you can kill related sessions with the following command.

When the sessions that use the old temp tablespace are cleared, we drop the old tem tablespace with the following command.

We’re creating temp tablespace that’s the same as the old name again.

We are setting newly created temp tablespace as the default.

We repeat what we did for TEMP1.

When all sessions that use TEMP1 are finished, we drop the TEMP1. If you attempt a drop while a session exists, you will wait until the session is closed.

Now we have created the tablespace TEMP, the size we want or the disk group we want.