Free Up Oracle Temporary Tablespace

1 year ago
Isabella Edwards
2 minutes

In Oracle database, insufficient temporary tablespace may be due to insufficient size or excessive usage by other sessions. To free up temporary tablespace, you can take the following measures:

  1. Increase the size of temporary tablespace: You can increase the size of temporary tablespace using the ALTER TABLESPACE statement to accommodate more temporary data.
  2. Free up the occupied temporary table space can be done by executing the following statement:
ALTER TABLESPACE TEMP SHRINK SPACE;

This statement will release the free space in the temporary tablespace, reducing the amount of space used in the temporary tablespace.

  1. Identify sessions consuming excessive temporary tablespace: You can use the following statement to find sessions that are using too much temporary tablespace:
SELECT s.sid, s.serial#, p.spid, s.username, s.program, SUM(t.blocks) * t.block_size / 1024 / 1024 mb_used
FROM v$sort_usage t, v$session s, v$process p
WHERE s.saddr = t.session_addr
AND s.paddr = p.addr
GROUP BY s.sid, s.serial#, p.spid, s.username, s.program
ORDER BY mb_used DESC;

After identifying sessions that are using too much temporary tablespace, you can try terminating those sessions or adjusting their temporary tablespace usage.

  1. If the above methods do not solve the issue, consider restarting the database instance to free up temporary table space. It is important to note that before restarting the database instance, make sure important data in the database has been backed up.
bannerAds