Drop default TEMP tablespace and recreate !!!

Home »  ORACLE »  Drop default TEMP tablespace and recreate !!!

On March 13, 2017, Posted by , In ORACLE, By , , With No Comments

There may be scenarios when you want to resize your default temporary tablespace because of multiple reasons. In this article, we would be dropping the default TEMP tablespace since we want to resize it.

You can perform below steps to drop default TEMP tablespace and recreate.

======================================================================================================

 

– Create a new temporary tablespace

The first step you need to perform is create a new temporary tablespace (lets call it TEMP2).

======================================================================================================

 

– Make the new TEMP2 tablespace the default one

Next step is to change the default temporary tablespace to the new one we created.

======================================================================================================

 

– Drop the old TEMP tablespace

The next step would be to remove the temporary tablespace you want to resize. (In our case, this is a tablespace named TEMP)

If the TEMP tablespace is still in use by some Oracle sessions, you won’t be able to drop the tablespace unless the transactions are completed. You can check the sessions which are still accessing the TEMP tablespace by using below query.

You can kill those sessions or wait until the transactions are completed.

======================================================================================================

 

– Create the TEMP tablespace again

Finally, create the TEMP tablespace again to the new size you want. Make the new TEMP tablespace the default temporary tablespace for the database and drop the TEMP2 tablespace.

 

Thats’s it, we have completed dropping and recreation of default TEMP tablespace.

======================================================================================================

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">