Re: TEMP Tablespace question

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 Thu Mar 16 2000 - 13:27:02 CST