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.
|
1 2 3 4 |
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; |
We set the system’s default temp tablespace as the new temp tablespace.
|
1 |
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1; |
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.
|
1 2 |
select tu.tablespace,tu.username,s.sid,s.serial# from v$tempseg_usage tu, v$session s where tu.session_addr=s.saddr; |
If you don’t have much time, you can kill related sessions with the following command.
|
1 |
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE |
When the sessions that use the old temp tablespace are cleared, we drop the old tem tablespace with the following command.
|
1 |
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; |
We’re creating temp tablespace that’s the same as the old name again.
|
1 2 3 4 |
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 30G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; |
We are setting newly created temp tablespace as the default.
|
1 |
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; |
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.
|
1 |
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES; |
Now we have created the tablespace TEMP, the size we want or the disk group we want.
