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.
Received on Tue Mar 14 2000 - 15:49:05 CST
