Monday, June 8, 2009

Temporary tablespace

What is temporary tablespace?

Temporary tablespace is used to store temporary information which is used for sort operation. Data that is only used for the duration of a session is stored in a temporary tablespaces. Let us say, we are joining two large tables, then sorting may not be able to complete in memory. Apparently oracle use the temporary segment to complete the sorting. Here are some SQL statement which might require disk sorting.

CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc

How do we create temporary tablespace?

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF'
SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. RMAN never backup the temporary tablespace data files.

How do we monitor temp tablespace?

Temp files are listed in V$TEMPFILE and DBA_TEMP_FILES. V$SORT_SEGMENT and V$SORT_USAGE can be used to find out who occupies the space in temporary Tablespaces. DBA_FREE_SPACE does not record free space for temporary tablespaces. We can use V$TEMP_SPACE_HEADER to find out the free space and used space.

Default temporary tablespace(Oracle9i feature)

When DBA create user, he/she should assign temporary tablespace. Prior to oracle9i, if DBA forgot to assign the temporary tablespace, then oracle use SYSTEM tablespace for sorting. To avoid overloading SYSTEM tablespace, oracle9i introduced default temporary tablespace. So every database has default temporary tablespace since Oracle9i. In case, if we forgot to assign temporary tablespace while creating user, then oracle use default temporary tablespace for sorting. Here is the way, we can assign default temporary tablespace for entire database.

SQL> alter database default temporary tablespace temp1;

To see the default temporary tablespace for a database, execute the following query:

SQL> select property_value from DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Restriction on Default temporary tablespace:

1. You can drop a default temporary tablespace until after you have
created new default temporary tablespace.

2. You can not take default temporary tablespace offline

3. You can not change a default temporary tablespace into a permanent tablespace

Temporary tablespace Groups(Oracle10g feature)

In nutshell, temporary tablespace group is a shortcut or synonym for a list of temporary tablespaces. A temporary tablespace group can have only temporary tablespaces as members. A temporary tablespace group consist of at least one temporary tablespace. A temporary tablespace group can not be empty. After last member of a temporary tablespace group has been dropped, the temporary tablespace group no longer exists. The temporary tablespace group is created when the first temporary tablespace is added to the group.

When ever a temporary tablespace can be referenced, a temporary tablespace group can be referenced as well. Therefore, temporary tablespace, temporary tablespace group share the same namespace. Temporary tablespace can not have the same name as a temporary tablespace group. The new view DBA_TABLESPACE_GROUPS shows the members of each temporary tablespace.

The advantage of this feature is, we can tailor user load to individual temporary tablespaces. We can allow large sort operations to span and consume multiple temporary tablespaces if needed. We can Reduce contention when multiple temporary tablespaces are defined.

How temporary tablespace group works?

Temporary tablespaces allow a single user with multiple session to potentially use a different temporary tablespace in each session. The user SCOTT is assigned the temporary tablespace group TEMPGRP consisting of TEMP1, TEMP2, TEMP3. The user SCOTT in session #1 may use actual temporary tablespace TEMP1, and user SCOTT in session#2 may use the actual temporary tablespace TEMP3. Not only does this prevent large tablespace operations from running out of temporary space, it also allows parallel operations within a single session to potentially use more than one actual temporary tablespace, even though in all the previous scenarios, SCOTT was assigned the TMPGRP temporary tablespace group. Logically the same temporary tablespace was used in every session.

Creating and Dropping temporary tablespace Groups:

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP1.DBF' SIZE 20M;

CREATE TEMPORARY TABLESPACE temp3 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP3.DBF' SIZE 20M;

CREATE TEMPORARY TABLESPACE temp4 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP4.DBF' SIZE 20M;

alter tablespace temp1 tablespace group tempgrp;
alter tablespace temp3 tablespace group tempgrp;
alter tablespace temp4 tablespace group tempgrp;
alter database default temporary tablespace tempgrp;

You can not drop the temporary tablespace group, but however, we can drop one of the members of the group as below.

alter tablespace temp3 tablespace group '';

We can also create temporary tablespace and we can immediately add it to an existing group, or create new group with one member as below.

CREATE TEMPORARY TABLESPACE temp6 TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF' SIZE 20M
tablespace group tempgrp2;

Assigning temporary tablespace group to users:

Assigning a temporary tablespace group to a user is identical to assigning a temporary tablespace to user.

Create user test identified by test
Default tablespace users
Temporary tablespace tempgrp;

Note that, if you did not specify a temporary tablespace for TEST, then we can still assign the temporary tablespace group.

Alter user test temporary tablespace tempgrp;

How do we resize the temporary data file?

In many database configurations, the DBA will choose to allow their temporary tablespace to auto extend. A bad query can easily chew up valuable space on the disk. The DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF' resize 250M;
alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP6.DBF'
resize 250M
*
ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value

Ooops.... It did not work!!!. There are several other method exists to resize the temp files. But there is obvious method exists in all the version which is dropping and recreating temp files. Dropping and recreating the temp file is straight forward when the temporary tablespace is not a default temporary tablespace.

Since there are several method exists, i am going to discuss few ways about how to drop and recreate the temp files when temp file becomes bigger..... It is recommended to do this operation in off peak hours...

Method1a... Let us assume, some one ran the bad query and the temp file is bigger. Now how do we resize the temp file to smaller size. The temporary tablespace is default temporary tablespace. In this scenario, temporary tablespace is not part of tempoary tablespace group. The temporary tablespace name is TEMP.

Step1: create a new temporary tablespace with reasonable size.

CREATE TEMPORARY TABLESPACE tempspace TEMPFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMPSPACE.DBF' SIZE 20M;

Step2: Modify the newly created temporary tablepsace as default temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPSPACE

Step3: Drop the bigger temporary tablespace. But before dropping, make sure, no one is using the temporary tablespace. Just make sure, the below query returns zero records.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

Step4: Once the above(step3) query returns zero records, then drop the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Method1b... Let us assume, some one ran the bad query and the temp file is bigger. Now how do we resize the temp file to smaller size. The temporary tablespace is default temporary tablespace. In this scenario, temporary tablespace is part of temporary tablespace group. The temporary tablespace name is TEMP and group name is TEMPGRP.

Step1: Unlink the bigger tablespace from temporary tablespace group.

alter tablespace temp tablespace group '';

Step2: Just make sure, no one is using temporary tablespace. Below query should return zero records.

SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
SELECT COUNT(*) FROM v$tempseg_usage WHERE tablespace = 'TEMP'

Step3: Once the above(step3) query returns zero records, then drop the temporary tablespace.

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Step4: Create new tablespace and add the tablespace to group.

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP.DBF' SIZE 20M tablespace group tempgrp;

How do we recover the temporary data file?

When ever, we the temporary tablespace temp file is corrupted or deleted accidently, then it is easy to recover. RMAN never backup the temporary tablespace. Till oracle9i, we make the temp file offline and drop the temp file at the database level(alter database datafile 'c:/oracle/oradata/temp.dbf' offline drop). Once it is dropped, then drop the temporary tablespace and recreate new one. Oracle10g introduced new feature which we will create the temp file automatically when we restart the database.

Here is the steps in oracle10g. The database is running in windows OS.

Step1: Let us delete the temp file to simulate that the temp file is corrupted.

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 08:50 PM 20,979,712 TEMP01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
6 File(s) 917,553,152 bytes
0 Dir(s) 15,348,064,256 bytes free

D:\oracle\product\10.2.0\oradata\orcl>del TEMP01.DBF

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
5 File(s) 896,573,440 bytes
0 Dir(s) 15,369,043,968 bytes free

D:\oracle\product\10.2.0\oradata\orcl>

Step2: Let us restart the database. In oracle9i, the database will not open. But in oracle10g, when we start the database, it creates the temp file automatically and open the database. Just click to know more info.

D:\oracle\product\10.2.0\oradata\orcl>set oracle_sid=orcl

D:\oracle\product\10.2.0\oradata\orcl>sqlplus sys/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 14 21:41:51 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1248552 bytes
Variable Size 88081112 bytes
Database Buffers 188743680 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>

Step 3: Database opened successfully. Let us check the DB directory...

D:\oracle\product\10.2.0\oradata\orcl>dir *.dbf
Volume in drive D has no label.
Volume Serial Number is 70AE-6E52

Directory of D:\oracle\product\10.2.0\oradata\orcl

06/14/2009 08:55 PM 104,865,792 EXAMPLE01.DBF
06/14/2009 08:55 PM 251,666,432 SYSAUX01.DBF
06/14/2009 08:55 PM 503,324,672 SYSTEM01.DBF
06/14/2009 09:42 PM 20,979,712 TEMP01.DBF
06/14/2009 08:55 PM 31,465,472 UNDOTBS01.DBF
06/14/2009 08:55 PM 5,251,072 USERS01.DBF
6 File(s) 917,553,152 bytes
0 Dir(s) 15,348,047,872 bytes free

D:\oracle\product\10.2.0\oradata\orcl>


No comments: