Home » RDBMS Server » Server Administration » Temporary Datafile Clean Up
Temporary Datafile Clean Up [message #277142] Mon, 29 October 2007 01:57 Go to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member
Hello,

Recently i witnessed a rather unknown behavior by TEMP tablesapce. I checked the temp datafiles and all were full (although not a problem as far as I know )and I was having disk shortage problem on the server.
So casually tried to resize one of the temp datafile ( TEMP data file was 110GB so thought of reducing it by 4-5 GB).
Not only that datafile got got reduced in size all the other temp data file got empty.

The environment is Oracle 10.2.0.1.0 and this is a datawarehouse so we have large TEMP tablespace.

What i would like to discuss is whether or not TEMP datafiles can be cleaned up?

thanks,
neel
Re: Temporary Datafile Clean Up [message #277160 is a reply to message #277142] Mon, 29 October 2007 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I checked the temp datafiles and all were full

This is the expected behaviour

Quote:

What i would like to discuss is whether or not TEMP datafiles can be cleaned up?

Restart the database.

But if you want to resize, the best way is to create a new temp tablespace, switch the user on it and drop the previous one.

Regards
Michel
Re: Temporary Datafile Clean Up [message #277219 is a reply to message #277142] Mon, 29 October 2007 04:46 Go to previous messageGo to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member

Thanks for the reply.

Also as i mentioned that while trying to resize one data file all the other data file (TEMP only) got empty too.

This was quite unusual for me so any ideas how this could have happened?

neel
Re: Temporary Datafile Clean Up [message #277224 is a reply to message #277219] Mon, 29 October 2007 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you know they are empty?
Most of tools (including Oracle ones) are not valid for temporary tablespaces. Don't trust what they say, empty or full the information they gave are meaningless.

Regards
Michel
Re: Temporary Datafile Clean Up [message #277234 is a reply to message #277142] Mon, 29 October 2007 05:17 Go to previous messageGo to next message
neelabh_SE
Messages: 20
Registered: October 2007
Junior Member
Well, I'm using Oracle Enterprise Manager.

Can you please tell me if there is any process to find out the same.
Is there any V$ or any tables like that for finding temp datafile.

thanks,
neel
Re: Temporary Datafile Clean Up [message #277251 is a reply to message #277234] Mon, 29 October 2007 05:47 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Full is the expected behaviour as soon as the database worked some time.

Database Concepts
Chapter 2 Data Blocks, Extents, and Segments
Section Overview of Segments
Subsection Introduction to Temporary Segments

Section Overview of Extents
Subsection When Extents Are Deallocated
Paragraph Extents in Temporary Segments

Chapter 3 Tablespaces, Datafiles, and Control Files
Section Overview of Tablespaces
Subsection Temporary Tablespaces for Sort Operations

Regards
Michel
Previous Topic: patch info
Next Topic: charset
Goto Forum:
  


Current Time: Sun Dec 04 08:45:01 CST 2016

Total time taken to generate the page: 0.14590 seconds