Oracle temp tablespace becomes too large-resize the temp tablespace

We mostly find ourselves stuck in the frustrating situation where our temporary tablespace becomes too large and hence database runs out of temporary tablespace or hard disk run out of space. This article provides some great tips that will help you reduce temporary tablespace.

Step 1 - Resizing Tempfile

Resize the tempfile to the reasonable size by running ALTER DATABASE TEMPFILE command

ALTER DATABASE TEMPFILE '/export/ home/ oracle/ oradata/ mydir/ temp01.dbf' RESIZE RESIZE 250M

Note: - Specify the temp file full path in above mentioned command.

Above command may results into ORA-03297 error. In such situation you should ensure that the TEMP datafile is autoextensible and maxbytes/maxblocks are not very low

Step 2 - Reducing Tempfile

Below approach will help you reduce tempfile even if your ALTER DATABASE TEMPFILE command results into ORA-03297 error. Mention the correct path in given commands.

1. Create temporary Tablespace: First of all create a temporary tablespace
   
   CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/export/ home/ oracle/ oradata/ mydir/ temp02_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;


2. Make it Default temporary Tablespace:Make it the default temporary tablespace of our database
   
   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;


3. Drop Old temporary Tablespace Datafiles: Drop old temporary tablespace datafiles by running below command.
   
   ALTER DATABASE TEMPFILE '/export / home/ oracle/ oradata/ mydir/temp01.dbf' DROP INCLUDING DATAFILES;


4. Add New Datafile: Add the new datafile to our old temporary tablespace.
   
   ALTER TABLESPACE TEMP ADD TEMPFILE '/export/ home/ oracle/ oradata/ mydir/ temp02.dbf ' SIZE 100M AUTOEXTEND ON NEXT 100M;


5. Redefine temporary Tablespace: Redefine temporary tablespace as the default temporary tablespace of our Oracle database
   
   ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;


6. Drop the New temporary Tablespace: Finally drop the new temporary tablespace created by us.
   
   DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

Add Comment

* Required information

Comment

1000

Upload

Drag & drop images (max 3)

Rating

Name

Email

Website

Town

Country

State

Question

Is it true or false that green is a number?

Answer

Captcha

Notify me of new comments via email

Remember my form inputs on this computer

I have read and understand the privacy policy

I have read and agree to the terms and conditions

Powered by Commentics

x

Insert Bullet List

Please enter at least one item.

Item:

Item:

Item:

Item:

Item:

x

Insert Numeric List

Please enter at least one item.

Item:

Item:

Item:

Item:

Item:

x

Insert Link

Please enter the link of the website

Optionally you can add display text

x

Insert Email

Please enter the email address

Optionally add any display text

x

Insert Image

Please enter the link of the image

x

Insert YouTube Video

Please enter the link of the video

x

Image Upload

x

Privacy Policy

This policy contains information about your privacy. By posting, you are declaring that you understand this policy:

• Your name, rating, website address, town, country, state and comment will be publicly displayed if entered.
• Aside from the data entered into these form fields, other stored data about your comment will include:
  • Your IP address (not displayed)
  • The time/date of your submission (displayed)
• Your email address will not be shared. It is collected for only two reasons:
  • Administrative purposes, should a need to contact you arise.
  • To inform you of new comments, should you subscribe to receive notifications.
• A cookie may be set on your computer. This is used to remember your inputs. It will expire by itself.

This policy is subject to change at any time and without notice.

x

Terms and Conditions

These terms and conditions contain rules about posting comments. By submitting a comment, you are declaring that you agree with these rules:

• Although the administrator will attempt to moderate comments, it is impossible for every comment to have been moderated at any given time.
• You acknowledge that all comments express the views and opinions of the original author and not those of the administrator.
• You agree not to post any material which is knowingly false, obscene, hateful, threatening, harassing or invasive of a person's privacy.
• The administrator has the right to edit, move or remove any comment for any reason and without notice.

Failure to comply with these rules may result in being banned from submitting further comments.

These terms and conditions are subject to change at any time and without notice.

Comments

No comments yet. Be the first!

x