OraFAQ Forum: Server Administration » Temporary Datafile Clean Up

Home » RDBMS Server » Server Administration » Temporary Datafile Clean Up

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Temporary Datafile Clean Up [message #277142]

Mon, 29 October 2007 01:57

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

Report message to a moderator

Re: Temporary Datafile Clean Up [message #277160 is a reply to message #277142]

Mon, 29 October 2007 02:19

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Temporary Datafile Clean Up [message #277219 is a reply to message #277142]

Mon, 29 October 2007 04:46

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

Report message to a moderator

Re: Temporary Datafile Clean Up [message #277224 is a reply to message #277219]

Mon, 29 October 2007 04:55

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Temporary Datafile Clean Up [message #277234 is a reply to message #277142]

Mon, 29 October 2007 05:17

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

Report message to a moderator

Re: Temporary Datafile Clean Up [message #277251 is a reply to message #277234]

Mon, 29 October 2007 05:47

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	patch info
Next Topic:	charset

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 27 20:23:44 CST 2025