This article is about a recovery of temporary tablespace. As we know that Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

Let’s look on how to proceed when we lose a temporary tablespace. First of all, considering We have just a temporary tablespace formed by one single datafile.

Now let’s shutdown the instance.

Simulate a loss of your temporary datafile

Now start again your database and look at the alert log.

Here are some lines… look at each of them carefully…

After the process of mounting the database is completed and during the opening of it you can find an interesting line… I know you already have noticed it, the alert log traces:

Starting from version 10g, Oracle is able to detect the missing temporary datafile and recreate it on the same location. Indeed you can notice there’s a new temporary datafile under my default location:

Let’s see what happens when we have two temporary datafiles and we lose one of them. Is Oracle still able to verify a temporary datafile is missing or it would let you open the database using just the other temporary datafile ? Let’s add a new temporary datafile to the same temporary tablespace.

Now simulate again the loss of temp01.dbf datafile and kill the instance. Here is described my situation:

Startup the instance

Again have a look at the alert log

You can find again Oracle detects the missing temporary datafile and recreate it in the same location.

Querying the database you can verify the two temporary datafiles are there.

Thank you for giving your valuable time to read the above information.


For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply