RE: Convert TEMP tablespace from datafiles to tempfiles

Yeah, from the List, this method seems to be preferred. My only problem is in step 7 -- change "a" to "several". This could require more testing, however. I imagine I'll be trying to ATLER TABLESPAVE TMEP afterwards...

Thx all! :)

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Mercadante, Thomas F [mailto:NDATFM_at_labor.state.ny.us]
> Sent: Wednesday, November 06, 2002 11:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Convert TEMP tablespace from datafiles to tempfiles
>
>
> Rich,
>
> I have a better set of steps for you:
>
> 1). Create a new tablespace (TEMPLMT) and make it a LMT.
> 2). One by one, issue ALTER USER {username} TEMPORARY
> TABLESPACE TEMPLMT;
> 3). Bounce the database
> 4). DROP TABLESPACE TEMP INCLUDING CONTENTS;
> 5). Backup the database
> 6). Let users at it.
> 7). Go have a beer.
>
> The only "negative" here is that your TEMP tablespace is
> called TEMPLMT, but
> it just doesn't matter. If you want, you can do it again, this time
> creating the tablespace as TEMP if it makes you feel better.
>
> Your method would work just fine. Not sure if the users
> would lose the
> designation of their TEMPORARY TABLESPACE, but if they do,
> you can easily
> create a script to set their temp tablespace to TEMP again.
>
> Hope this helps.
>
> Tom Mercadante
> Oracle Certified Professional

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).