Temporary Tablespace DDL

What are TEMPFILES?

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL>DROP TABLESPACE temp;

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1M;

Oracle9i Default Temporary Tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘/u02/oradata/TESTDB/temp2_01.dbf’ SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;



Drop Tempfile Command Method – (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with the above method, this should be performed during off hours with no users logged on performing work.

The first step is to obtain the name of the tempfile to drop. For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp01.dbf:

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;

SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’  SIZE 512m 

AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

select TABLESPACE_NAME, BYTES_USED/1024/1024 “USED_MB”, BYTES_FREE/1024/1024 “FREE_MB” from V$TEMP_SPACE_HEADER;

select tsh.TABLESPACE_NAME,file_name, BYTES_USED/1024/1024 “USED_MB”, BYTES_FREE/1024/1024 “FREE_MB”

from V$TEMP_SPACE_HEADER tsh,dba_temp_files dbf

where tsh.tablespace_name = dbf.tablespace_name;

To Check who is using temp space

select b.tablespace ,b.segfile# ,b.segblk#

,round(((b.blocks*p.value)/1024/1024),2) size_mb

,a.sid ,a.serial# ,a.username ,a.osuser ,a.program ,a.status

from v$session a ,v$sort_usage b ,v$process c ,v$parameter p

where p.name=’db_block_size’

and a.saddr = b.session_addr

and a.paddr=c.addr

order by  b.tablespace,b.segfile#,b.segblk#,b.blocks

IDEVELOPEMT TEMP TBLSPACE

ORAFAQ ON TEMP FILES

Advertisements

2 responses to this post.

  1. Posted by Anand Doraiswamy on March 24, 2011 at 5:28 am

    Hi,
    Its never a good idea to set TEMP tablesspaces to AUTOEXTEND. The TEMP tablespace will be out of your control if you do that. Its an Oracle best practice.

    Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: