User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

The temporary tablespace can reach a huge size, sometimes because of a big batch operation.

You may want to reduce it to save some disk space. If so, this article will present you some ways to do it.

-- Get temporary datafiles size

SELECT name TempFile, bytes/1024/1024 MB
FROM v$tempfile ;
/*
TEMPFILE                                                  MB
-----------------------------------------------------     -----
/u01/app/oracle/oradata/db01/oradata/temp/TEMP01.dbf      2800
...
*/

Resize method

The RESIZE option can be used for temporary datafile but it may not be very efficient sometimes. For example, a 2800MB temporary datafile which has only 21MB in use, a RESIZE COMMAND to 2600MB may give you this kind of error:

-- Resize temporary datafiles with resize option

ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/db01/oradata/temp/TEMP01.dbf' RESIZE 2600M ;
/*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
*/

(Read this article about RESIZE : shrink-datafiles-with-resize.html) In case of the Resize option is not enough efficient, see the alternative way below.

Drop and recreate method

-- Create a new temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP2DROP
TEMPFILE '/u01/app/oracle/oradata/db01/oradata/temp/TEMP2DROP01.dbf'
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M ;
/*
Tablespace created.
*/

-- Check database default temporary tablespace

SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
/*
DEFAULT_TEMP_TABLESPACE
-----------------------
TEMP
*/

-- Change database default temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2drop;
/*
Database altered.
*/

-- Change default user temporary tablespace

SELECT 'ALTER USER '||username||' TEMPORARY TABLESPACE temp2drop ;' SqlCmd
FROM dba_users
WHERE temporary_tablespace = 'TEMP';
/*
SQLCMD
-------------------------------------------------
ALTER USER USER1 TEMPORARY TABLESPACE temp2drop ;
ALTER USER USER2 TEMPORARY TABLESPACE temp2drop ;
...
*/

-- Check sessions using TEMP

SELECT vss.status, vss.username, vss.sid, vss.serial#
FROM V$SORT_USAGE vsu
JOIN v$session vss
ON vsu.session_num = vss.serial#
WHERE tablespace = 'TEMP';
/*
STATUS   USERNAME          SID    SERIAL#
-------- ---------- ---------- ----------
INACTIVE USER1             119      36564
...
*/

-- Kill inactive session using TEMP

SELECT 'ALTER SYSTEM KILL SESSION '''||vss.SID||','||vss.SERIAL#||''' IMMEDIATE ;' SqlCmd
FROM v$sort_usage vsu
JOIN v$session vss
ON vsu.session_num = vss.serial#
WHERE tablespace = 'TEMP'
AND vss.status = 'INACTIVE' ;
/*
SQLCMD
-------------------------------------------------
ALTER SYSTEM KILL SESSION '119,36564' IMMEDIATE ;
...
*/

-- Drop TEMP tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
/*
Tablespace dropped.
*/

Then you can recreate TEMP by repeating all theses steps and finally drop TEMP2DROP at the end.

HTH, Michel.

Related articles:
- dbahome.blogspot.com

Enjoyed this article? Please like it or share it.

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh