Re: How to get rid of temporary segments

On Wed, 06 Dec 2000 19:57:43 +0800, Connor McDonald <connor_mcdonald_at_yahoo.com> wrote:

>ashish25_at_my-deja.com wrote:
>>
>> Hi,
>> Here is how you can get rid of temporary sticking segments.
>> 1)alter your temporary tablespace to permanent.
>>
>> 2)Create a table in this tablespace
>> create table temp_tb(doodle NUMBER(10) tablespace tbs_name
>> storage(initial 1M,Next (big enough so it is not able to allocate)
>> minextents 2);
>> 3)The create table will fail and SMON will clear up your tablespace.
>> 4)Alter the tablespace to temporary.
>>
>> Thanks,
>> Ashish
>>
>> In article <amtq2t45qptulapi2r959ei9f8e9pjtncb_at_4ax.com>,
>> Yurasis Dragon <yurasis_spamNOSPAM_at_yahoo.com> wrote:
>> > Short of bouncing a database how do I get rid of
>> > temporary segments. I have one, owned by SYS,
>> > that is almost 2 gb in size and will cause problems
>> > if we have a big sort occuring anytime soon.
>> >
>> > Any ideas? Thanks in advance.
>> >
>> >
>>
>> Sent via Deja.com http://www.deja.com/
>> Before you buy.
>
>Even simpler is
>
>alter tablespace TEMP default storage ( pctincrease 0 );
>
>(even if was 0 already...SMON will still kick in)
>
>HTH
I had already tried the "pctincrease 0" change, hoping SMON would take over, it didn't happen.

I ended up having to bounce the database this morning.

I like the idea of Ashish, I'll try that next time.

Thanks everyone! Received on Wed Dec 06 2000 - 21:24:13 CST