How To Move The Temp Tablespace to Another Diskgroup On Oracle

In some cases, you may need to move the TEMP tablespace to another diskgroup if there is no space left in the ASM or because of other reasons.

In this article we will do this step by step.

First we query the empty spaces in the diskgroups in ASM by using the following script.

In this way, you can decide which diskgroup the TEMP tablespace will be moved to.

 

We then create a new temp tablespace on the new DiskGroup using the following script.

 

Then we query the default temp tablespace of the database using the following script.

 

And we use the following script to set the newly created temp tablespace as the default temp tablespace.

 

With the help of the following script, we look at the users whose default tablespace is the old temp tablespace.

 

If so, we change the default temp tablespace of these users with the following script.

 

The old temp tablespace will be used for a while.

We use the following query to check whether the old temp tablespace is being used.

If there is a session using the old temp tablespace, you can kill the sessions using the kill script in the query.

Before you kill the session, you need to contact the owner of the session.

 

Finally, we delete the old temp tablespace using the following script.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *