Utility Script for Drop and Recreate of Temp Tablespaces in Oracle Database

Had a need for this today so did a few google searches and found this nifty guide written at DBAClass.com

So using their initial template I’ve gone ahead and created a reusable utility SQL Script with comments as the instructions for portability.

-- Purpose:
-- If you want to recreate your temp tablespace, then follow below steps. For changing the default tablespace also, below steps can be used.

-- Instructions:
-- 1. Open SQL Developer
-- 2. Open a new SQL Worksheet
-- 3. Login as SYSTEM with the DEFAULT role targeting the intended SID 
-- 4. Follow the below steps that are detailed in the inline comments

-----------------------------------------------
-- Find the existing temp tablespace details --
----------------------------------------------- 

select tablespace_name, file_name from dba_temp_files;

--------------------
-- Example Output --
--------------------

-- TABLESPACE_NAME FILE_NAME
-- TEMP			   /opt/oracle/oradata/MYSID/temp01.dbf

-- We will use the tablespace name here for our delete/drop statements later on and also it's filepath structure

-----------------------------------------------------------
-- Create another Temporary Tablespace TEMP_DELETE_AFTER --
-----------------------------------------------------------

CREATE TEMPORARY TABLESPACE TEMP_DELETE_AFTER TEMPFILE '/opt/oracle/oradata/MYSID/temp_delete_after.dbf' SIZE 2G;

-------------------------------------------
-- Move Default Database temp tablespace --
-------------------------------------------

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_DELETE_AFTER;

COMMIT;

-----------------------------------------------------------
-- If any sessions are using temp space, then kill them. --
-----------------------------------------------------------

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;

-- Use the SID and Serial Number of each session in the output and populate in the below SQL command for each open session using the TEMP tablespace.

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

-----------------------------------------------
-- Disconnect your SQL Session and Reconnect --
-----------------------------------------------

----------------------------------------
-- Drop the original temp tablespace. --
----------------------------------------

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

----------------------------
-- Create TEMP tablespace --
----------------------------

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/oracle/oradata/MYSID/temp01.dbf' SIZE 10G;

-------------------------------------
-- Make TEMP as default tablespace --
-------------------------------------

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

COMMIT;

-----------------------------------------------
-- Disconnect your SQL Session and Reconnect --
-----------------------------------------------

-----------------------------------------------------
-- Drop temporary for tablespace TEMP_DELETE_AFTER --
-----------------------------------------------------

DROP TABLESPACE TEMP_DELETE_AFTER INCLUDING CONTENTS AND DATAFILES;

COMMIT;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s