Resizing the Temp tablespace in Oracle

In much the same way as with the Undo tablespace, you may want to reduce the size of the Temp tablespace in Oracle to reclaim space.

Unfortunatly, if the temp tablespace is full (as will be the case if it has been expanding) you will not be able to reduce he size directly. Also, restarting the database will not clear out the data.

The process or doing this is similar to that of reducing the size of the undo tablespace.

——

1. First, find the location of the temp tablespace datafile:

select * from dba_temp_files;

If there is more than one, you can check which one is the default with the following query:

select * from database_properties
where property_name like ‘%TABLESPACE’;

2. Next, generate a script to create the existing temp tablespace:

SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’, ‘TEMP’) from dual;

i.e.
CREATE TEMPORARY TABLESPACE “TEMP” TEMPFILE
‘/TESTDB/oradata/TESTDB/temp01.dbf’ SIZE 104857600
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

save this.

3. Use that script to create a new temporary tablespace. Alter the tablespace anme, file name and size as appropriate.

You only need to create a small file which you will be deleting.

CREATE TEMPORARY TABLESPACE “TEMP2” TEMPFILE ‘/TESTDB/oradata/TESTDB/temp02.dbf’ SIZE 50M AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;

4. Make the new temporary tablespace the default:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

5. Make sure that the new tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

*** If Database version is < 9i see below ***

6. Drop the old tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

7. Re-create the old tablespace using the script from step 2 – changing the size as appropriate.

8. Set the re created tablespace to be the default

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE “[Tablespace Name]”;

9. Make sure that the correct tablespace is now the default:

select * from database_properties
where property_name like ‘%TABLESPACE’;

10. Drop the other tablespace:

DROP tablespace [Tablespace Name] including contents and datafiles;

If you are using a database version below 9i then you will need to do this step before step 6:

Use the below query to identify any residual dead connections still alloctaed to the first TEMP tablespace. Dropping the TEMP tablespace (Step 7) will not complete until these dead session dissapear or are killed. The dead connections can be killed using the ALTER SYSTEM KILL command with the sid and Serial# returned from the query.

SELECT b.tablespace
, b.segfile#
, b.segblk#
, b.blocks
, a.sid
, a.serial#
, a.username
, a.osuser
, a.status
FROM v$session a
, v$sort_usage b
WHERE a.saddr = b.session_addr