Re: TEMP Tablespace question

For sizing the temporary tablespace, you need to find out what caused the TEMP tablespace size / extent growth. Normally, it was because of SORT AREA SIZE and the poor SQL statement. Since only the SORT AREA SIZE in init<sid>.ora didn't big enough to keep all the sorting data, then it wrap the data to TEMPORARY TABLESPACE because disk sort. Unless it was because of create / rebuild indexes, otherwise, two reason can cause disk sort. First, large volumn data for sorting. Second, poor SQL statement.

So, check the SORT AREA SIZE, then check SQL statement. It's a very common problem of poor SQL statement for a new system.

Finally, after you drop and recreate TEMPORARY tablespace, you need to use ALTER USER to grant the temporary tablespace to all related users again, otherwise, you may get another error message.

<afilonov_at_pro-ns.net> wrote in message news:8archv$1u9$1_at_nnrp1.deja.com...> In article <8amc4f$bu5$1_at_nnrp1.deja.com>,
> lycovian_at_my-deja.com wrote:
>
> Just increase the size of TEMP tablespace. Temporary objects are
> created and deleted automatically and you have no control on them.
> The message is trying to tell you that there is no enough space for
> some operation (create index, group by, order by etc.).
>
>
> > Recently experienced an:
> > ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
> > I was going to run throught the usual of
> > 1) alter tablespace TEMP permanent
> > 2) create table x (c1 varchar2(1)) ... fails.
> > 3) alter tablespace TEMP temporary
> > to clean out the temp objects in the tablespace.
> > I was suprised to find that the TEMP tablespace was already permanent,
> > not temporary. Note: I inherited this database from our previous DBA
> > who recently left the company.
> > A select from dba_extents shows that several "temporary" objects are
> in
> > this tablespace all owned by SYSTEM with names like "3.123". Because
> of
> > this I can't change the tablespace to "temporary" because it has
> objects
> > allocated within it.
> > My question is this: How to do 1) get rid of the temporary objects, 2)
> > change the TEMP tablespace to temporary. Long term I would also like
> > raise the size of this tablespace, without adding an additional file.
> > Basically, after it is temporary, I think its size should be about 140
> > MB rather than the 80MB that it is currently sized to. I had thought
> > about droping the tablespace and recreating larger but I dont' know
> how
> > to tell all of the existing tablespaces to use the "new" TEMP
> tablespace
> > if I dropped and recreated it. Will they find the new one by name or
> > (more likely) some internal Oracle identifier that will not be the
> same
> > on the new tablespace. So, in short, I need to recreate my TEMP
> > tablespace, transition it to "temporary", make it bigger, and not
> break
> > all of the existing schemas and tables. I know I could do it by adding
> > an additional datafile to the TEMP tablespace but I would prefer to
> keep
> > it to a single file. Any ideas?
> > Mike Wilson
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Mar 18 2000 - 22:26:43 CST