Re: Convert TEMP tablespace from datafiles to tempfiles

Ron, you're confusing me.

> Perhaps the reason that I was confused is that I did not phrase the
> question correctly. If you create a tablespace to be used as the
> temporary tablespace for users and create it as type temporary (
> segments used by implicit sorts to handle order by clause) you can not
> use the LMT clauses.

That comes as a surprise, as I have several databases setup that way.

> If you create a temporary tablespace that is only
> in existence for the duration of the session you can use the LMT
> clauses. Does this mean that you have to recreate the temporary
> tablespace after a reboot?

What kind of tablespace are you referring to? I don't understand whay you mean by 'duration of the session' in context of a tablespace.

Jared

>
> Another point about LMT's. I like them and use them in the majority of
> my tablespaces. When you choose the uniform size it can be what ever you
> choose to work comfortably with the data size of the table. If I
> remember correctly( working through the gray hair again) a temporary
> extent should be equall to or a multiple of the sort area size so it
> does not create unnecessary thrashing when trying to fit a sort into the
> temp.
> Am I correct in what I remember?
> THanks.
> Ron
>
> >>> HANDM_at_polaroid.com 11/07/02 12:53PM >>>
>
> Jesse,
> I did the same thing last week on our sandbox system using the method
> others
> have prescribed. There is a note 140913.1 covering a LMT bug in
> 8.1.6.
> under OpenVMS. You might want to double check to make sure no
> equivalent
> problem exists on your platform.
>
> Ron,
> As Jared pointed out, it's the Temporary tablespace (not a tablespace
> with
> temporary contents) that permits local extent management. In 8.1.7 I'm
> sure
> you've already tried:
>
> create tablespace temp_contents
> datafile '/oracle/.../temp.data1' size 128M
> temporary
> extent management local uniform size 4M;
>
> or something similar and gotten a ORA-25144. Also be forwarned, 8.1.7
> will
> let you assign a Permanent LMT as temporary_tablespace for a user, but
> won't
> let you create temporary segments there.
>
> Mike
>
>
>
> -----Original Message-----
> Sent: Wednesday, November 06, 2002 10:56 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hey all,
>
> I've got some downtime coming up on an 8.1.6 DB on Solaris, and I'd
> like to
> take the opportunity to convert the datafiles of the TEMP tablespace
> to
> tempfiles. My reason for this change is primarily to get the TEMP
> tablespace LMT, but also to shrink our hotbacks w/o modifying the
> working
> script.
>
> I've been trying to reason out this task in my head, as I can't find
> much on
> MetaLink, and here's what I've got so far:
>
> 1) Shutdown DB.
> 2) Backup DB.
> 3) Startup restricted.
> 4) Offline tablespace TEMP.
> 5) Drop tablespace TEMP.
> 6) Create new temporary TEMP LMT.
> 7) Bounce instance.
>
> I don't yet have an arena to try this in. Will users whose assigned
> TEMPORARY TABLESPACE is TEMP need to be ALTERed? Anyone have any
> comments
> on the procedure?
>
> TIA!
>
> Rich
>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex,
> WI USA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).