OraFAQ Forum: Server Administration » temporary tablespace size

Home » RDBMS Server » Server Administration » temporary tablespace size

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

temporary tablespace size [message #52632]

Thu, 01 August 2002 22:42

Saravana Kumar
Messages: 7
Registered: May 2002

Junior Member

size of my temporary tablespace has suddenly shot upto 4 GB. we have very less space in Server now. How can I reduce the datafile size of temporary tablespace?

Oracle Enterprise manager gives some option to directly change the size. but the database is online, and i dont want to do R&D on that.

I am using Oracle 9i Enterprise server with NT Service Pack 6.

Please help.

Report message to a moderator

Re: temporary tablespace size [message #52634 is a reply to message #52632]

Fri, 02 August 2002 05:17

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

apart from setting an optimum pctincrease ( 0 or nonzero) you can try to force SMON to do some clean up.
1. login to sqlplus as internal /sys
2. alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

3. To find the tablespace number run the following query:

select name, ts# from ts$;

4. This will return the tablespace number which you must pass in the query.
5. The DROP_SEGMENTS event will not work if the segments are locked. Typically you will see TS locks in v$lock on the associated tablespace if this event is not working.

This routine does what SMON does in the background, i.e. drops temporary segments. It is provided as a manual intervention tool which the user may invoke if SMON misses the post and does not get to clean the temp segments for hours.

Report message to a moderator

Re: temporary tablespace size [message #52667 is a reply to message #52632]

Mon, 05 August 2002 13:47

Silpa
Messages: 23
Registered: July 2002

Junior Member

I had the same problem before.

I restarted the database and every thing was clean.

Did immediate shutdown and restarted it.

If you can afford to bring down you database then you can try this.

Report message to a moderator

Previous Topic:	Sun OS paging and SGA
Next Topic:	Windows XP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Oct 31 08:05:50 CDT 2025