| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Convert TEMP tablespace from datafiles to tempfiles
Mike,
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. 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?
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:
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
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).
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).
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). Received on Thu Nov 07 2002 - 13:44:02 CST
 |
 |