Home » RDBMS Server » Server Administration » Temporary Tablespace Increasing
Temporary Tablespace Increasing [message #59299] Mon, 10 November 2003 22:06 Go to next message
Amrit
Messages: 10
Registered: December 2002
Junior Member
Hi,
When we run report on Developer 2000 temporary table space keeps on increasing. Because of this the HD space is increasing exponentially and effecting the performance of Oracle server. The temp table space does not get flushed even after restarting Oracle.

How do I rectify this problem? How can I flush the database?

Thanks

Report message to a moderator

Re: Temporary Tablespace Increasing [message #59302 is a reply to message #59299] Tue, 11 November 2003 01:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
first make sure the temp tablespace is really of type temp and not a permanent one.

Report message to a moderator

Re1: Temporary Tablespace Increasing [message #59303 is a reply to message #59302] Tue, 11 November 2003 01:56 Go to previous messageGo to next message
Amrit
Messages: 10
Registered: December 2002
Junior Member
Dear Bro,
Of course the tablespace is temporary and not permanent, I want to flush the size of temporary tablespace which is increasing only, no decreasing and much headache of space problem..Plz, give me some solution...

Thanks

Mahesh

Report message to a moderator

Re: Temporary Tablespace Increasing [message #59304 is a reply to message #59302] Tue, 11 November 2003 01:58 Go to previous messageGo to next message
_simma_dba
Messages: 34
Registered: November 2003
Member
Try to increase SORT_AREA_SIZE in your init.ora. Most of your sorting request are done on disk not in memory. You have plenty of tips for this problem on internet so keep searching.

Report message to a moderator

Re: Re1: Temporary Tablespace Increasing [message #59306 is a reply to message #59303] Tue, 11 November 2003 02:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
 
i beleive, this is becuase SMON is not doin the job properly.
Increasing the SORT_AREA_SIZE considerably and making the SORT_AREA_RETAINED_SIZE equal to SORT_AREA_SIZE will help.
make sure u have initial=next and pctincrease 0.
do this when there is no sorting taking place in database.

ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0);

Report message to a moderator

Re: Re1: Temporary Tablespace Increasing [message #59312 is a reply to message #59303] Tue, 11 November 2003 08:48 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Is this a true temporary tablespace that uses tempfiles instead of datafiles ? If so, the space should have been released after the bounce.

Are these Locally managed tablespaces with UNIFORM extent allocation ? Otherwise you 'could' have free space fragmentation (ie space not being effectively used by sessions causing the file to grow)..
Also is Autoextend turned ON ? Try turning it off and see if your sessions fail in need of more sort_Segments.

You could create a new temporary tablespace with tempfiles ( locally managed uniform extent allocation with good sized extents(multiples of sort_area_size))
, reassign the users to this one and drop the old one if you cannot 'fix' it.

-Thiru

Report message to a moderator

Re: Re1: Temporary Tablespace Increasing [message #59345 is a reply to message #59306] Wed, 12 November 2003 22:04 Go to previous message
Amrit
Messages: 10
Registered: December 2002
Junior Member
We are using Oracle 9i, in which, temporary tablespace is created locally.

As earlier mentioned, I also tried by increasing SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE making 2MB on both. We have 1GB physical memory and about 80% of memory is allocated for Oracle SGA and Others.

Comparative to earliers, now, the performance of SQL and developer forms is well increased. But in report, it is still very slow and temporary tablespace is increasing.

I tried to flush the temporary tablespace using
ALTER TABLESPACE TEMP DEFAULT STORAGE (PCTINCREASE 0);

In the response, an error is prompted
___________________________________________________
ORA-03217 Invalid option for alter of temporary space
__________________________________________________

So, I am still in headache for truncating tablespace. Please help me....

Thanks

Mahesh

Report message to a moderator

Previous Topic: Enterprise Manager Configuration Assistant - No Listener!
Next Topic: ARCH files extension
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 11:29:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.