From: "TC" Newsgroups: comp.databases.oracle References: Subject: Re: Why my Temporary tablespace is always 99% full ? Lines: 31 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: NNTP-Posting-Host: 12.250.123.140 X-Complaints-To: abuse@attbi.com X-Trace: rwcrnsc54 1052147316 12.250.123.140 (Mon, 05 May 2003 15:08:36 GMT) NNTP-Posting-Date: Mon, 05 May 2003 15:08:36 GMT Organization: AT&T Broadband Date: Mon, 05 May 2003 15:08:36 GMT Are you sure that even after the database is restarted the TEMP tablespace remains 99% full ? Normally, in a temporary tablespace the allocated extents are not deallocated to save resource, instead these are reused. However, during a shutdown these do get deallocated. I presume you are doing a shutdown immediate or shutdown normal to take a cold backup and not a shutdown abort. A trick that works for me, since I cannot shutdown the databases at will is, changing pctincrease to 1 and then again changing it back to 0, which kind'a forces the SMON to wake up. If you do not have active sorts, very quickly changing the tablespace from type temporary to type permanent and changing it back helps too, the risk being that if an active sort is there, you will be stuck with a tablespace TEMP of type permanent. //tc "cpchan" wrote in message news:b95qdn$b8h5@imsp212.netvigator.com... > I don't understand why my Temporary tablespace is always 99% full even we > shut down the database for a UNIX tar backup on every 6:00 am. > Do I need to turn off the whole UNIX machine and then turn its power again > so that all the content of the Temporary tablespace will be cleared ? > If no, how can I clear all the content of a Temporary tablespace on an > Oracle 8i RDBMS ? > >