Manage the temporary tablespace in oracle

Manage the temporary table space in Oracle

Temporary tablespace is used in Oracle by sql query or long jobs, like sorting operation which is not fit in the physical memory of the machine. Following steps let us help to configure the temp tablespace as default, how to create the temporary tablespace, how to add datafile in temporary tablespace and check the space in temporary tablespace.

Check the temporary files and tablespace

Select * from dba_temp_files;

Create new Temporary tablespace in database

Create temporary tablespace TEMP2 tempfile '/u02/oradata/temp_01.dbf'  size 500m;

Drop the temp tablespace including physical files:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Add temp files to TEMP Tablespace

ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/temp_03.dbf' SIZE 100M;

Check the usage of Temporary tablespace

select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
from v$session    a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;

Check and Set the Default Temporary Tablespace

SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

 
Modify the temp tablespace as default

alter database default temporary tablespace temp;

Checked Free space in Temporary tablespace

Select tablespace_name , Bytes_used, Bytes_free from v$TEMP_SPACE_HEADER;

Note:
1. One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database.
2 . If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty.
 

Advertisements

One thought on “Manage the temporary tablespace in oracle

  1. Pingback: Move or Rename An Oracle Datafile | Smart way of Technology

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.