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 > > >>> [EMAIL PROTECTED] 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 > [EMAIL PROTECTED] Quad/Tech International, Sussex, > WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
