Re: TEMP Tablespace question

You won't break any links or usages of the TEMP tablespace by dropping and recreating the tablespace.

I'd recommend the following series of actions.

Shutdown and restart your database -- if possible in exclusive mode

Drop tablespace temp including contents;

create tablespace temp temporary datafile '<where the current file is + filename>' size 140m reuse;

shutdown and restart the database in normal mode

The names that you are seeing such as the example 3.123 are typically temporary index files created when you are making an index, or dropping a table and associated indices -- all created and controlled automatically by the system account.

Cheers,

Al

<lycovian_at_my-deja.com> wrote in message news:8amc4f$bu5$1_at_nnrp1.deja.com...
> 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 - 21:27:59 CST