That's why my temporary tablespace is always full....
Johanna Ivy, September 06, 2001 - 11:40 am UTC
Thanks for the explanation of how temporary tablespaces are managed. We just allocated another 1 Gig on our temporary tablespace, due to a few ORA-1652 errors. I noticed in my DBA studio view, that as soon as we ran the script that was using up the tablespace it took up 200 Mb of the new space, but never gave it back. I was beginning to wonder if the next time we ran it, we would run out of space again. Good to know it wasn't supposed to shrink.
kiro, April 24, 2002 - 10:26 am UTC
Tom,
You Said:
"You will never see an extent
freed in this tablespace until you shutdown (or alter the
tablespace in some way)".
Is it means that if I have in such tablespace 1G used space, new transaction that need 'temp' space will allocate new extend or will reuse existing?
Please explain.
Thanks
April 24, 2002 - 3:17 pm UTC
reuse existing. As I said:
"This is normal and is OK -- we
are not losing extents, we are just managing them internally -- in memory,
instead of in the data dictionary. "
alter tablespace TEMP temporary;
Mike C, August 11, 2002 - 12:12 am UTC
I have the similar situation. I have the tablepsace TEMP, in fact it was created as permanent tablespace. I want to convert it to the temporary tablespace:
alter tablespace temp temporary
/
I do not know if there are some of the side affects of converting , if there were some objects had been created in TEMP.
Thanks
August 11, 2002 - 9:52 am UTC
that is a sort of temporary tablespace.
Better to
drop tablespace temp
create TEMPORARY TABLESPACE TEMP tempfile .....
raises a q
Meyer Tollen, August 11, 2002 - 3:22 pm UTC
Was there ever a reason to use a non-temp temp instead of a real temp? (Maybe in 7.3.4 HPux?).
We have a production system (not ours) that vendor runs on 7.3 with a "permanent" temp...
Thanks.
August 11, 2002 - 7:51 pm UTC
whoops -- sorry, create temporary tablespace is "new" with 8.0 -- doesn't apply in this particular.
Reader
A reader, August 11, 2002 - 8:06 pm UTC
Tom,
What is the advantage of
"create temporary tablespace ..."
vs.
" create tablespace TEMP ... temporary ...."
besides the datafiles are not backed up.
Thanks
August 11, 2002 - 8:12 pm UTC
bingo.
also, on OS's that support it -- the tempfiles are created "sparse", meaning they create instantly and will actually allocate space only as needed (some view this as a negative benefit, depends on your perspective)
Unable to allocate extent size x on Temp Tablespace
SK, September 10, 2002 - 2:42 pm UTC
Hi Tom,
Is it possible to get an error like the above when temp tablespace is only 10% full (OEM View). I am facing this error in my large updates/deletes. Under what conditions this is possible?
Thanks
September 10, 2002 - 3:32 pm UTC
sure, depends on HOW the temp tablespace was created.
You should be using a true temporary tablespace "create temporary tablespace" with tempfiles, not datafiles, in a locally managed tablespace.
Follow up
SK, September 10, 2002 - 4:44 pm UTC
I am using Temp tablespace (raw) as I am running Oracle 9iR2 RAC. Will this happen here as well?
Thanks
September 10, 2002 - 8:44 pm UTC
will what happen here, there were many tiny discussions going on
In general I can say "yes" cause RAC won't fundementally change the way temp works really.
SMON process occupying 2.2 GB of TEMP
Sree, September 10, 2002 - 5:20 pm UTC
I was worried to see a SMON process occupying about 2.2 GB of TEMP space and not getting freedup. (Oracle 8.1.7.2.0)
select l.sid
,l.type "Tran Type"
,o.object_name "Object Name"
,decode(l.lmode,
0,'NONE',
1,'NULL',
2,'Row-SELECT (SS)',
3,'Row-X (SX)',
4,'SHARE',
5,'SELECT/Row-X (SSX)',
6,'EXCLUSIVE') "Mode"
,l.request "Request"
,s.type "Type"
from dba_objects o
,v$session s
,v$lock l
where l.id1=o.object_id and
s.sid=l.sid and
l.type != 'MR'
order by l.sid
SID Tran Type Object Name Mode Request Type
------------------------------------------------------------
8 TS I_OBJ# Row-X (SX) 0 BACKGROUND
SID 8 belongs to SMON process.
A query for segments in tablespace TEMP gives following:
Schema TableSpace Type Allocated(m) Extents
--------------------------------------------------------
SYS TEMP TEMPORARY 2283.125 562
Is this okay too?
Thanks in advance
September 10, 2002 - 8:51 pm UTC
Umm, how did you correlate the first query with the second? and what is the second query?
I mean, all I see is
a) smon is doing some cleanup on obj$
b) you have 2.2 gig allocated to temp
what do you see?
SMON process occupying 2.2 GB of TEMP
Sree, September 11, 2002 - 9:20 am UTC
Tom, I just wanted to show what is occupied in TEMP tablespace by second query. There was no correlation.
(select owner,tablespace_name,SEGMENT_TYPE,
BYTES/1024/1024,EXTENTS
from dba_segments
where tablespace_name='TEMP')
Actually a "select * from V$SORT_SEGMENT" proved that all the extents showing as used are in fact free and available for use.
Thank you very much for help.
September 11, 2002 - 10:09 am UTC
right - so SMON never was occupying 2.2 gig of temp ;)
how to see temp tablespace extent freed
tammy pickett, September 18, 2002 - 10:28 pm UTC
Tom: You wrote this: You will never see an extent freed in this tablespace until you shutdown (or alter the tablespace in some way).
I did see my temp tablespace is full and what command I need to use "alter tablespace temp..." to change the 75GB temp tablespace not full from dbartision view?? size: 76,000MB, used: 76,000MB
September 19, 2002 - 7:30 am UTC
but it is not full, it is just allocated. It is just waiting to be used.
query the v$sort_segment and v$sort_usage to see what is really up with that temp tablespace.
Difference between creating temporary, and specifying temporary keyword
A Reader, January 13, 2003 - 8:01 am UTC
Tom,
Can you explain please what the difference between:
CREATE TEMPORARY TABLESPACE ......
and
CREATE TABLESPACE ........ TEMPORARY
is ? Does it make any difference ?
Regards,
Paul
January 13, 2003 - 8:14 am UTC
yes, one uses tempfiles (CREATE TEMPORARY...) and one uses datafiles (create tablespace).
use create temporary tablespace -- tempfiles are never backed up, easily recreated, not logged -- they are special and treated differently than datafiles are.
dba_segments equivalent for temp tablespace ?
Sameer, January 13, 2003 - 5:07 pm UTC
If I create temporary tablespace temp, I cannot see segments allocated in it using select * from dba_segments where tablespace_name = 'TEMP' or segment_type = 'TEMPORARY'. Is there another view in the dictionary where temp segments are stored ?
Even segfile# from v$sort_usage doesn't match up with file_id from dba_data_files or dba_temp_files.
How do I know what temp segment is being used by any user process if I use create temporary tablespace type of ts ?
January 13, 2003 - 7:20 pm UTC
v$sort_usage and v$sort_segment are where they are stored.
the file id is off by DB_FILES -- so, if you
select segfile# - db_files
from v$sort_usage, (select value db_files from v$parameter where name = 'db_files' )
/
you'll get the file id you can use against dba_temp_files....
(oh, you'll be looking for "extents" -- not segments, one big old temp segment that the system manages and doles out extents of as needed.)
Who/what is consuming my temp tablespace
MSaeed, January 27, 2003 - 11:49 am UTC
Hi Tom:
Somehow our datafile belonging to a tablespace defined as temporary tablespace has grown to 10gb.
How can I find out what caused this and how will I be able to reclaim this space.
Would creating a pemanent t/s and assigning it to the users as their temporary tablespace help ?
Thanks a lot in advance for your time and help.
Moh'd
January 27, 2003 - 12:27 pm UTC
Somehow - well, you let it. You configured it to be permitted to grow that large and someone did something "big".
So, that is the how.
Now, temp stays allocated -- we just manage it and dole it out as needed. You can "resize" depending on how you created it.
Insufficient details to really tell you what to do however - how was it created, it is true tempfiles or datafiles, is it a DMT with thousands of extents to worry about or a LMT where this won't make a difference. And so on.
One way -- create a new one, alter users to this new one, drop old one.
V$sort_usage
kumar, April 03, 2003 - 3:10 am UTC
Tom,
When i query the v$sort_usage, i get the USER column showing the name of the user who is executing this query rather than the user who is actually using the temp space for running some other queries. Can you explain why this is happening ?
Thanks
April 03, 2003 - 7:54 am UTC
temporary tablespace
Nasir Sarwar, June 11, 2003 - 6:15 pm UTC
Excellent Tom.
I have some confusion about TYPE temporary tablespace in Oracle 8i and 9i.
1. If a user's TYPE temporary tablespace is changed to TYPE permanent tablespace, then what tablespace that user is using for sorting queries which cannot be carried out wholly in memory?
2. Now if a user's TYPE temporary tablespace is dropped then what tablespace user is using for sorting queries which cannot be carried out wholly in memory?
Can you please answer the above questions
June 12, 2003 - 8:31 am UTC
1) you can use a permanent or temporary tablespace for sort. we don't care. there are three types of tablespace you can use:
create temporary tablespace TEMP tempfile ....; <<== this is the one you should use
create tablespace temp datafile .... TEMPORARY; <<== this is 2cnd "best". it uses datafiles but disallows the creation of permanent objects in it, meaning we can manage the space much more efficiently
create tablespace temp datafile .... PERMANENT; <<=== this stinks, but works.
2) no where, you get an error.
sort segment
A reader, August 21, 2003 - 11:27 am UTC
Tom,
I have a basic doubt,
sort segment is created when instance is started and temporary segments are created when a sort operation are active. - right?
then when is sort segment being used?
sorry for my ignorance..
August 21, 2003 - 7:07 pm UTC
there is generally a single sort segment, in which there are many extents and when we sort, you get one of the extents for your user and when you are done you give it back.
you do not create new temp segments -- you use extents in a single temp segment.
Great, thanks for making me understand in few lines
A reader, August 22, 2003 - 12:46 am UTC
what is this temporary segments ?
Jagjeet Singh, December 26, 2003 - 6:20 am UTC
#### 1 ####
Select segment_name,segment_type,bytes/1024 from user_segments
SEGMENT_NAME SEGMENT_TYPE BYTES/1024
------------------------------ ------------------ ----------
JC TABLE 128
4.33 TEMPORARY 3072
4.1569 TEMPORARY 64
4.1601 TEMPORARY 64
SQL> set lines 72
SQL> Select * from user_tablespaces where tablespace_name = 'TEST';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT
------------------------------ ---------- -------------- -----------
MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS
----------- ----------- ------------ ---------- --------- ---------
LOGGING FOR EXTENT_MAN ALLOCATIO SEGMEN
--------- --- ---------- --------- ------
TEST 2048 65536
1 2147483645 65536 ONLINE PERMANENT
LOGGING NO LOCAL SYSTEM MANUAL
December 26, 2003 - 10:13 am UTC
i don't really know what or why I'm looking at here?
I can see there are some temp segments (you are using old fashioned dictionary managed tablespaces and permanent tablespaces for TEMP or you are runing some creates in other sessions and they are creating temporary segments that will eventually be converted.
I see a query getting info about the tablespace TEST (and no idea why I see that -- whats the relationship here....)
Re:
JS, December 29, 2003 - 3:02 am UTC
Sir,
Test is my default tablespace.
I am showing you that this is a LMT tablespace.
December 29, 2003 - 10:30 am UTC
so, that says nothing about the temporary segments.
benchmark between Type-2 and Type-3 tablespace
Sami, January 08, 2004 - 4:35 pm UTC
Dear Tom,
Thanks for all your kind support.
<AskTom>
1) you can use a permanent or temporary tablespace for sort. we don't care.
there are three types of tablespace you can use:
create temporary tablespace TEMP tempfile ....; <<== this is the one you should use
create tablespace temp datafile .... TEMPORARY; <<== this is 2cnd "best". it
uses datafiles but disallows the creation of permanent objects in it, meaning we
can manage the space much more efficiently
create tablespace temp datafile .... PERMANENT; <<=== this stinks, but works.
</AskTom>
Our DB has Type-3 TEMPORARY tablespace (using datafile).
We can not go for Type-1 because still we are in 8.1.7.3.
The best choice for us would be moving from Type-3 to Type-2.
Do you have any simple program to do the benchmark between Type-2 and Type-3? So that I can easily convince folks.
Thanks Tom,
Sami
January 08, 2004 - 8:20 pm UTC
you too can use type1
1* create temporary tablespace ttt tempfile '/tmp/ttt.dbf' size 5m
ops$tkyte@ORA817DEV> /
Tablespace created.
there is not much difference between 2 and 3 in terms of performance, 2 just doesn't let you actually create permanent objects in there (think of it as an edit).
HOWEVER, hey -- unless you've identified this as being "a problem", best to leave well enough alone sometimes. Especially in an unsupported database release. You don't really want to change a thing right now.
Memory Exhausted
A reader, February 05, 2004 - 10:21 am UTC
Hi, TOm,
We have a process(complicated involved) which is running fine before(after we tunned it, normally process only several hunds of records)), the process takes about 10 to 15 minutes to finish. But just started from yestoday, the temp spaces getting increased gradualy with the process going, and eventually, the process crashed(memory full, temp spaces growing to 10G and we stop it). We didn't change any part of the process(package and procedure). So, do you have any threads on your mind regarding what could be the reasons?
THanks a million
February 05, 2004 - 7:21 pm UTC
data could be radically different, causing different results.
could be a change in a query plan.
You would expect it to be "a query" so when you see temp growing, see what query it is running and take a look at that.
How
A reader, February 06, 2004 - 11:39 am UTC
Yes,
We identified it is a query, but it is in the form of internal language:
SELECT bolco#, file#.....
...
How we could interpret it to a meanful format?
THanks,
February 07, 2004 - 1:25 pm UTC
hows about you show the entire thing?
Enqueue- SS Exclusive
Alvin, March 01, 2004 - 4:05 am UTC
My users are complaining that their queries are super-slow. I've traced some queries and most of them have p1=1397948422... I have used the enqueue_decode function in your book and i got ' SS Exclusive ' locks.
As per oracle documentation SS means sort segment. Is oracle not re-releasing the temp segments after use ??
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 310 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 309 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #2: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
1 select p1,state, sum(seconds_in_wait) ,count(*), enqueue_decode(p1) enqueue_dec
2 from v$session_wait
3* group by p1,state
17:00:22 rtbe@RTBE.SUNFIRE01>
17:00:22 rtbe@RTBE.SUNFIRE01> /
P1 STATE SUM(SECONDS_IN_WAIT) COUNT(*) ENQUEUE_DEC
---------- ------------------- -------------------- ---------- ------------------------------
0 WAITING 20917 1 No lock
3 WAITING 0 1 Row-Exclusive
81 WAITING 0 1
300 WAITING 268 2
4288 WAITING 1 1
6000 WAITING 0 4
24076 WAITING 1 1
28130 WAITING 137 1
180000 WAITING 5156 1
675562835 WAITING 18900857 358 (D
1397948422 WAITING 439884 37 SS Exclusive
1413697536 WAITED SHORT TIME 0 1 TC
1413697536 WAITING 5777 2 TC
1650815232 WAITING 10230 2 be
4039132012 WAITED SHORT TIME 0 1
1 select sid, ROW_WAIT_OBJ# ,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
2* from v$session where status='ACTIVE'
17:06:07 rtbe@RTBE.SUNFIRE01>
17:06:07 rtbe@RTBE.SUNFIRE01> /
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
1 -1 0 0 0
2 -1 0 0 0
3 -1 0 0 0
4 -1 0 0 0
5 -1 0 0 0
6 -1 0 0 0
11 -1 0 0 0
16 -1 0 0 0
40 -1 0 0 0
73 -1 0 0 0
80 -1 0 0 0
91 -1 0 0 0
113 -1 0 0 0
129 -1 0 0 0
140 -1 0 0 0
173 -1 0 0 0
184 -1 0 0 0
192 -1 0 0 0
194 -1 0 0 0
208 -1 0 0 0
218 -1 0 0 0
221 -1 0 0 0
232 -1 0 0 0
243 -1 0 0 0
245 -1 0 0 0
248 -1 0 0 0
254 -1 0 0 0
259 -1 0 0 0
261 -1 0 0 0
262 -1 0 0 0
270 -1 0 0 0
274 -1 0 0 0
304 -1 0 0 0
305 -1 0 0 0
306 -1 0 0 0
308 -1 0 0 0
315 -1 0 0 0
322 -1 0 0 0
339 -1 0 0 0
340 -1 0 0 0
349 -1 0 0 0
356 -1 0 0 0
371 -1 0 0 0
376 -1 0 0 0
377 -1 0 0 0
399 -1 0 0 0
404 -1 0 0 0
408 -1 0 0 0
413 -1 0 0 0
414 -1 0 0 0
420 -1 0 0 0
51 rows selected.
March 01, 2004 - 8:05 am UTC
what version are you using and what type of tablespace is your temporary tablespace (is it a permanent in disguise as a temporary or a true temporary with locally managed extents - or what)
no, it is not due to "not releasing space" -- your users would get "unable to allocate" immediately if it were.
SS Exclusive...
Alvin, March 01, 2004 - 9:30 pm UTC
Our db is 8.1.7.0.0 running on a Solaris 5.8 OS. Our front end is developed in Java, i extracted the sql via TOAD and ran it on sqlplus. The results and waits are the same.
Our Temp tablespace is a DMT. I have just bounced the DB 5 days ago.
Inserts and Updates doesnt have problems some queries are not affected.
I was planning to post the statspack report here. Unfortunately my perfstat session suffers from the same wait event. I traced my perfstat session and the wait event is as follows.
Node name: sunfire01
Release: 5.8
Version: Generic_108528-27
Machine: sun4u
Instance name: rtbe
*** SESSION ID:(278.54208) 2004-03-02 02:23:57.819
WAIT #27: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
WAIT #27: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
WAIT #27: nam='enqueue' ela= 308 p1=1397948422 p2=3 p3=1
WAIT #27: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
WAIT #27: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
WAIT #27: nam='enqueue' ela= 307 p1=1397948422 p2=3 p3=1
I'm boggled. I don't know where to look anymore.
I do plan to
1. Create a LMT Temp tempfile
2. point the users to temp_lmt
3. Bounce the db
4. Drop the tablespace temp DMT
5. Remove the OS files of Temp DMT.
I will be looking at the patch set (ours is yet to be patch) and check whether its a bug. Our temp file has grown from 8gig -35 gig for the past week.
March 02, 2004 - 7:28 am UTC
your DMT -- is it temporary or permanent.
your DMT -- what is the extent size.
and what is your sort area_size/sort area retained set to....
what is the number of sorts and sorts to disk....
sounds like
your dmt has really small extents, you sort alot, you sort to disk alot and you are lining up doing heavy duty space allocation
all of which would be alleviated by
o upping sort area size/sort area retained if set small
o using a CREATE TEMPORARY tablespace with tempfiles not datafiles (makes it LMT)
o using a larger extent size in the temporary tablespaces (to avoid massive space mgmt requests)
Bdump trace logs.
Alvin, March 01, 2004 - 10:15 pm UTC
By the way, i did check the oracle logs found in the bdump directory. Nothing unusual there also.
I killed the sessions in wait.
Alvin, March 02, 2004 - 9:02 pm UTC
Your right... the extents are 64ks and the pctincrease was set 50% thats why it grew from 8g - 34g in 1 weeks time. Thanks a bunch !
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
------------------------------ --------------- --------------- --------------- --------------- -----
MIN_EXTLEN STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO PLU
--------------- --------- --------- --------- ---------- --------- ---
TEMP 65536 65536 1 0
65536 ONLINE TEMPORARY LOGGING DICTIONARY USER NO
TEMP_LMT 5242880 5242880 1 0
5242880 ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM NO
I've another question about nologging, standby databases and Temp LMT.
Last year (Sept 18) i accidentally issued a nologging CTAS but i did cancel it before it finished, this invalidated one tablespace (tblspace where i issued the CTAS to reside in ) in my standby db coz when i "opened" and tried to query any object from that table i get an error (Sorry i didnt cut and pasted the said error).
I thought that one the datafile of the said tablespace got corrupt but the test shows otherwise. Anyways i recreated my standby db around Oct.
NAME UNRECOVER
-------------------------------------------------- ---------
/export/spare/ora_dat/system01.dbf
/export/spare/ora_dat/tools01.dbf
/export/spare/ora_dat/rbs01.dbf
/export/spare/ora_dat/temp01.dbf
/export/spare/ora_dat/users01.dbf
/export/spare/ora_dat/indx01.dbf
/export/spare/ora_dat/drsys01.dbf
/export/spare/ora_dat/cdr_01.dbf 18-SEP-03
/export/spare/ora_dat/cdr_02.dbf 18-SEP-03
/export/spare/ora_dat/cdr_03.dbf 18-SEP-03
/export/spare/ora_dat/cdr_04.dbf 18-SEP-03
/export/spare/ora_dat/cdr_05.dbf 18-SEP-03
/export/spare/ora_dat/cdr_06.dbf
1. Are standby DB's that sensitive to nologging ?? even when the nologging activity didn't push thru/finished or was cancelled ??
2. Normally, When i create a tablespace i get an error in my standby DB's logs where it looks for the datafile of the new tablespace.
I would 'begin backup' the new tablespace and copy the datafile to the standby db's location and mount the standby db and everything would be fine.
But when i created a Temp LMT i didn't specify it to be nologging. Standby DB didn't ask for the datafile of the new tablespace (TEMP LMT).
I'm not that concerned this time because its just the temp tablespace that i added.
On to the question.
So far I am OK right ?? or do i need to copy the temp_lmt datafile to the standby db w/c to me makes no sense since no logs would be applied against it.
March 03, 2004 - 8:06 am UTC
1) standby's are sensitive to nologging yes since they are built based on it. (in 9i, you can force logging, so nologging is a big "no op")
2) temporary tablespaces are not logged and not "standby'ed"
You would just create a new temp upon opening the standby, they are not "standby'ed"
SS Exclusive no more ( but still monitoring )
Alvin, March 02, 2004 - 9:14 pm UTC
I haven't restarted my instance since i created the TEMP LMT yesterday. So my sort to disk might contain sorts from the old temp DMT as well. I just killed the sessions in wait and redirected all of the users temp tablespace to the LMT one.
As queried from V$sysstat my sort statistics are as follows.
STATISTIC# NAME CLASS VALUE
--------------- -------------------------------------------------- --------------- ---------------
188 sorts (memory) 64 20780617
189 sorts (disk) 64 123
190 sorts (rows) 64 243940718
3 rows selected.
Elapsed: 00:00:00.41
sql> show parameter sort
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
nls_sort string
sort_area_retained_size integer 2048000
sort_area_size integer 2048000
sort_multiblock_read_count integer 2
Do i have to...
1. Increase my
Sort_area_size to 10mb
Sort_area_retained_size =5 mb
sort_multiblock_read_count = 640 (8kb block size)
because my TEMP LMT's extent sizes are in 5 mb chunks ? Basically aligning everything to 5mb
2. Whats a good ratio for the sorting parameters with respect to extent size of uniform temp extents ?
March 03, 2004 - 8:57 am UTC
5m is overboard. 1 or 2meg (since you have 2m sort area size) would be more reasonable (or at least something divisible by 2)....
You want the lmt extents to fit nicely in there, 1m is good and positions you well for 9i with pga_aggregate_target
On OEM, it doesn't show the associated file to Temporary Tablespace
A reader, June 09, 2004 - 1:43 pm UTC
Hi Tom,
I created three databases using the following command:
CREATE DATABASE nmdb02
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
MAXDATAFILES 300
DATAFILE '/oracle/nmdb02/data/system/system01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
UNDO TABLESPACE undo_ts DATAFILE '/oracle/nmdb02/data/system/undo01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE 150M
DEFAULT TEMPORARY TABLESPACE temp_ts TEMPFILE '/oracle/nmdb02/data/system/temp.dbf' SIZE 100M
MAXLOGFILES 3
LOGFILE GROUP 1 ('/oracle/nmdb02/logs/redo_logs/online/copy1/redo01.log',
'/oracle/nmdb02/logs/redo_logs/online/copy2/redo01.log') SIZE 25M,
GROUP 2 ('/oracle/nmdb02/logs/redo_logs/online/copy1/redo02.log',
'/oracle/nmdb02/logs/redo_logs/online/copy2/redo02.log') SIZE 25M,
GROUP 3 ('/oracle/nmdb02/logs/redo_logs/online/copy1/redo03.log',
'/oracle/nmdb02/logs/redo_logs/online/copy2/redo03.log') SIZE 25M;
;
On two of the databases, when I click on “Storage/Tablespaces/Temp_ts” it doesn’t show me the filename! On one of them it shows me the correct filename ‘temp.dbf’ with the size of zero !
These databases have been used for more than 3 months. My questions are:
1- Why doesn’t it show the associated file ‘temp.dbf’ to Temp_ts tablespace?
2- Why does it show the size of zero for the other one? Is it because of big sort area size?
Oracle Version: 9.0.1
OS: Unix HP
Sort Area Size: 20 MB
Thanks for your help and wonderful web site!
- Arash
June 09, 2004 - 1:57 pm UTC
you'll have to work via support on that -- i use sqlplus myself ;)
A reader, June 15, 2004 - 2:22 pm UTC
Hi Tom,
I was reading another document onyour web site that you mentioned "temp files are in dba_temp_files ". Back to my previous question, I even used SQLPLUS "select tablespace_name,file_name from dba_temp_files;"
no rows selected
I couldn't get Oracle support yet! My question is, does it mean the database is working without TEMP tablesapce and just using "sort area" for sort?
Thanks,
Arash
June 16, 2004 - 11:08 am UTC
temp files are there but you could have temporary tablespaces that use datafiles (bad, but doable)
look in dba_users to see what tablespace users are assigned to.
look in dba_tablespaces to see what type of tablespace it is
A reader, June 16, 2004 - 12:09 pm UTC
Hi Tom,
I queried both dba_users and dba_tablespaces. 'TEMP_TS' tablespace has been assigned to all users and 'CONTENTS' for this tbs is 'TEMPORARY'
It is odd that doesn't show dbf file! Isn't it?
Thank you again
June 16, 2004 - 1:26 pm UTC
no, just means you restored and did not recreate the tempfiles and have been lucky so far that no one actually used temp (or they haven't reported the error to you)
A reader, January 18, 2005 - 7:14 am UTC
Temp space issue
atul, February 28, 2005 - 12:55 am UTC
Hi Tom,
I have a question and waiting for ur solution.
Our temp tablespace size is 5GB,Its locally managed tablspace,we r using 9i
One session connect to the database and checks for any x'tion is there in DB,if it found it will insert records ini a table.
if no x'tion it will wait,it will not disconnect.
What is happening,this session is eating up all the temp space,it is not releasing it back.
Mentioned operation is happening through Stored procedure.
and it has commit inside.
Could you tell me any option to release temp space.
Only option we are doing is killing the session,but i don't thing its a good option.
Please suggest
Thanks,
Atul
February 28, 2005 - 7:36 am UTC
"ur", "r" "x'tion" hmm. especially hmm on x'tion.
without an explicit small example, one cannot say. I cannot even imagine off of the top of my head why it would be using temp space.
Temp issue
atul, March 01, 2005 - 5:30 am UTC
Hi,
Session insert's data into one table ,commits and come out.
Then wait for some time,again do the same thing.
This happens through stored procedure.
One thing here,session never disconnects it remains there.
How should we release the temp space?
I can see values in v$sort_segment for free_extents and used_extents
Thanks,
Atul
March 01, 2005 - 8:30 am UTC
sorry, it does not work that way -- there is a missing piece to this puzzle if in fact that long running session is consuming temp space. (you have to do something that actually requires temp space)
would need a more detailed test case, like the process itself.
temporary tablespace
reader, April 08, 2005 - 7:55 am UTC
Tom,
We have around 3 GB size of temporary tablespace assigned to our application schema and also for users sys and system.
Our DBA told that the temporary tablespace has become full and hence the processing may slow down. When we pointed out that temporary tablespace showing "full" is normal and asked him to look into the v$sort_usage and v$sort_segment views he has come to us with the following reply.
=====================================================
"It is a well known fact and convention that, for a schema using more memory, requires a seperate temp segment to be allocated.
TEMPORARY segments are used in a number of operations within Oracle. These should always get cleaned up once an operation has completed but there are some obscure circumstances where this does not occur. To check what temporary segments exist log into Sqldba or Server Manager as a DBA user and:
SELECT * FROM dba_segments where segment_type='TEMPORARY';
This will show all TEMPORARY segments. It is perfectly normal to see these segments when sorts, index creation etc.. are in progress.
It takes a while for SMON to clean up TEMP segments. And if the sorting is big, it will cause performance bottlenecks.
======================================================
His point of contention now is that though there is free space in temporary tablespace, as sys and system users are also using this, a separate temporary tablespace needs to created and assigned to the application schema.
Is he right? Is there a real need to have two temporary tablespace (i.e) one for sys/system users and another one for application schema? Like to know your viewpoint and justification.
Regards
April 08, 2005 - 8:37 am UTC
that is a couple of years old and would have applied to permanent tablespaces used as temporary tablespaces.
it has not been universally true for a very long time.
Ask him if this is a true temporary tablespace (create temporary tablespace ...)
if so, he is "not correct"
Unless users are getting "unable to get you temp space, you lose" errors, there is no shortage of temporary tablespace in your system.
A primary reason for having more than one temporary tablespace would be to spread IO out over many devices. Now, as the SYS and SYSTEM users
a) should not be used by you really, short of starting and stopping the database
b) don't do really big sorts generally
it would not seem to make sense to have a special tablespace just for them.
temporary tablespace
reader, April 11, 2005 - 12:03 am UTC
Tom,
Yes..there is TEMPORARY tablespace of 3 GB.. and this is of LMT type.. with uniform extent size of 1 MB..
Is a separate temporary tablespace required for sys and system users in the above scenario? If really so, when and how this can be justified?
Our DBA says that having a single temporary tablespace for sys,system and the application schema may cause contention and SMON will take a lot of time to do clean up of temp segments..
In LMT, coalescing is not done by SMON, I think..If I'm right, what type of clean up SMON needs to do here?
Regards.
Pl clarify...
April 11, 2005 - 8:40 am UTC
SMON doesn't clean up temporary tablespaces. It does not work like that anymore. It worked like that years and years ago with permanent tablespaces used to hold temporary data.
There is no cleaning up by SMON
There is no coalescing
And sys and system should probably need pretty much 0 sort space as you aren't using them to do things and recursive sql run as sys should not be doing sorts that hit disk.
why it is cleaned in 9i
Pravesh Karthik from India, April 20, 2005 - 2:03 am UTC
Tom,
"There is no cleaning up by SMON"
Please let us know how the space is getting released after the sort in 9i
Thanks
Pravesh Karthik
April 20, 2005 - 6:52 am UTC
see the v$sort_usage and v$sort_segment views.
search for those terms on this site.
the space is managed by the server and given to sessions as needed. it is not returned to the dictionary only to be reallocated again and again and again.
A reader, May 11, 2005 - 8:37 pm UTC
If I have to use a temp table just to create, use it and drop it (same thing done once daily), can I use temporary tablespace?
May 12, 2005 - 7:28 am UTC
you would create a single global temporary table ONCE, never drop it, and it'll use temp.
temporary tablespace
deba, May 19, 2005 - 10:01 am UTC
Tom,
Is it possible to create temporary tablespace on diffrent block size ? Currently our block size is 8k . Now I saw a documents where it is written that temporary tablespace on 32k block size improves performance. So at this stage can we create a temporary tablespace on 32k ( where db_block_size is 8k ) using create temporary tablespace command ?
I think that creating tablespace with different block size using "create tablespace ... temporary " is possible . But if this is so, will we get any performance improvement using "create tablespace ... temporary" command ? our environment is data warehouse. If possible pls explain with small example ( since your examples are fantastic ).
Thanks
Deba
May 19, 2005 - 11:17 am UTC
hehehe
go ahead and try.
In order to create your temporary tablespace in a 32k blocksize -- you sort of need to, well, CREATE DATABASE with a 32k blocksize.
And you know what is *even funnier*. It is so "not relevant"
Let me guess, you read this:
....
This is very true. All temp segment access is sequential and 32k blocksizes greatly reduce logical I/O and disk sort times. I'm working an a benchmark right now that shows a one-third speed improvement of disk sorts in a 32k vs. an 8k blocksize.
..............
guess what -- IO to temp. Hmm, IO to temp is done
via direct IO, there is no such thing as LOGICAL IO to tempAnd in fact, the IO to temp is done with multiblock reads.
Ask yourself, say the IO size to temp is 128k, does it matter if:
a) we read 128k of data with 4, 32k blocks OR
b) we read 128k of data with 16, 8k blocks
for you see, we are going to read 128k of data - period. Not via the cache, no logical IO.
And the other part of that statement, the "I'm working on a benchmark" part -- strikes me as odd, since we normally benchmark to find out what the results would be, not the other way around.
So take that into consideration before you rebuild your entire database.
</code>
http://oraclesponge.blogspot.com/2005/05/sigh-more-on-myths.html <code>
has some material on this as well.
Measurement Tests
A reader, May 24, 2005 - 3:14 pm UTC
hello tom,
Do you have any measurement tests done between DMT temp and LMT temp using tempfiles? If yes, can you post the link. I did search unsuccessfully several times. My customer still uses LMT (SYSTEM,TEMP,RBS) but wants measurable tests to justify the change from DMT to LMT. In my previous shop it was LMT accross the board and I agree with your points, but can you give some proofs?
I did a trace on 10046, created global temp tables with large result sets and made SELECTs use temp space. The TKPROF of the results shows not much access to FET$ or UET$ as I expected. Am I missing something? The tests were based on our live data set which I cannot post here.
Thanks,
May 24, 2005 - 4:04 pm UTC
once the tablespace grows -- there won't be any difference. the extent allocations are done only once.
It is ease of use and the fact that in many databases today you only have LMT's.
But if they
a) like the dmt temp
b) are afraid of lmt temp
you can let them have this one.
Thanks.
A reader, May 24, 2005 - 4:13 pm UTC
Thanks for the quick reply!!!
I did post the same question again, as it did not show up after I refreshed the page. Please ignore the duplicate question.
estimate of Temp space
June, July 04, 2005 - 12:19 am UTC
Hi Tom,
I am wondering if there is any way to estimate the temp space for certain type of the work. For instance, to create unique index; sort data set, etc.
The reason I am asking this, is that I don't how much temp space it will consume when I tried to create unique index on 4 columns on table (compressed with size over 20GB); I have to terminate the process when I saw the process using more than 10GB temp space (via v$sort_usage). Now as workaround, I create index and add unique constraint enable novalidate. How will I know the size of temp space will fit the operation?
Thanks for any suggestion.
July 04, 2005 - 10:35 am UTC
how did creating the index manually change anything? I don't understand your "workaround"
as a worst case, the temp space needed for an index create could be as much as 2x the size of the resulting index.
but I don't see how your workaround works around anything?
follow-up on temp space estimation
J, July 04, 2005 - 11:56 am UTC
The original index on table is unique; After I moved data to new tables due to the fact that no new column could be added into compressed table, I have to re-create unique index. First time I did as usual, issued 'create unique index' statement, which I killed after 2 hours. Then I created index - it was quick, and added unique constraint - it was chewing up temp space - more than 10GB in an hour, I had to kill it again. So now I added unique sonstraint enable novalidate. Though enable validate won't lock table, I wonder how much temp space it will use. Ideally I still want to enable the unique index validate, and try to understand how to estimate temp space.
Thanks!
July 04, 2005 - 12:47 pm UTC
In oracle all indexes are unique -- if you don't say "unique", we just add in the rowid to the key and make that unique, else your key is the unique thing.
So, I'm still not understanding how your workaround worked around anything?
-----
Just had an idea pop into my head
what is your workarea policy set to? auto or manual?
the "workaround" could have been "you got luckier memory wise the 2nd time around and used less temp because of that"
A reader, August 31, 2005 - 3:53 pm UTC
Hi Tom,
We are using true temporary tablespace
( CREATE TEMPORARY tablespace with tempfiles not datafiles), but it
has grown to 25GB. What should we be looking at? How can we resolve
this. Please help.
Thanks.
August 31, 2005 - 4:08 pm UTC
what is the perceived problem with 25gig of temp?
far too few details...
A reader, August 31, 2005 - 4:20 pm UTC
Thanks for the immediate response. We think it is growing at a very high rate. Is it Ok to have 25GB temp?
Will there be any side effects if the temp tablespace keeps increasing like this.
September 01, 2005 - 1:28 am UTC
temp is used for sorting, hashing, global temporary tables and the like.
You need as much temp as you need. You should make sure your PGA memory settings (pga_aggregate_target OR *_area_size parameters) are set correctly for your system (to avoid using temp disk when possible).
25g is not unreasonable for a data warehouse/reporting system if you have the need to concurrently perform many large sort/hash operations.
given what I know about your system (little to nothing), that is about all I can say.
Look at how many concurrent users you have, what kind of sql they do, and whether you believe they actually need to be doing these big sort/hashes/temp tables.
A reader, August 31, 2005 - 4:30 pm UTC
I forgot to paste the output from v$sort_segment. Could you please comment on the results?
TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO
temp1 0 0 64 61 10370 663680 3190 204160 7180 459520 1112 232653 13900 139 23158 1482112 17861 1143104 17255 1104320 0
Thanks.
Reusing temp tablespace
Suvendu, September 12, 2005 - 4:13 am UTC
Hi Tom,
Here are few queries regarding TEMP tablespace issues:
We are running Oracle 9.2 on HP unix box. Our temp tablespace size is 10 GB. And when it is going to full, we are doing following process manually in cycle basis:
SQL> create temporary tablespace temp1 Â….;
SQL> alter user user1 temporary tablespace temp1;
SQL> drop tablespace temp;
We can not wait till oracle to release temp space on shutdown process. Is it the only option to reuse the temp space? Else would you advice any good option for it.
And we want to automate this process thru CRON, but once again itÂ’s not possible to achieve. We implementing the logic like, when the temp tablespace will fill upto 80% usage, we will do the above process, but if there any active transaction referring to same which will not allow to do.
Please, would you place your valuable comments on it; we would appreciate if you could demonstrate it with an example. :-)
Please, current me, if IÂ’m wrong.
Thanking you.
Regards,
Suvendu
September 12, 2005 - 7:32 am UTC
man oh man.
temp isn't "full" unless v$sort_usage says it is. We allocate temp space and manage it internally.
All you are doing is making us reallocate it all over again.
WHY are you doing this? This only accomplishes one thing:
o you run slower because we have to redo all of this work all over again
IF v$sort_usage says "all of the allocated temp space is in use", well then so be it - you are actually using it (and I don't see the point in dropping it again)
More confuse...
Suvendu, September 13, 2005 - 4:28 pm UTC
Hi Tom,
Thanks for your response and time.
As you told we can obtain the temp usage from v$sort_usage, on my test case I didnÂ’t get any result from it.
SQL> select a.name , b.value from v$statname a ,v$mystat b
2 where a.statistic#=b.statistic#
3 and a.name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 85
sorts (disk) 1
sorts (rows) 938230
SQL> select username, sqladdr ,tablespace, contents, segtype from v$sort_usage;
no rows selected
SQL> select count( distinct object_name), count( distinct owner) from my_all_objects;
COUNT(DISTINCTOBJECT_NAME) COUNT(DISTINCTOWNER)
-------------------------- --------------------
17452 24
SQL> select username, sqladdr ,tablespace, contents, segtype from v$sort_usage;
no rows selected
SQL> select a.name , b.value from v$statname a ,v$mystat b
2 where a.statistic#=b.statistic#
3 and a.name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 91
sorts (disk) 2
sorts (rows) 2812265
SQL> select username, sqladdr ,tablespace, contents, segtype from v$sort_usage;
no rows selected
SQL>
But, after reading a document from metalink, what was my understanding about tempspace “when it is going to full, … We can not wait till oracle to release temp space on shutdown process.” , it made me more closer.
I would appreciate; if you could elaborate same with some test case.
===============
Doc ID: Note:1039341.6
Subject: Temporary Segments Are Not Being De-Allocated After a Sort
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 01-DEC-1997
Last Revision Date: 22-APR-2004
Description
===========
When monitoring the size of the available free space in the TEMPORARY tablespace
it does not increased once sorts are finished even with no client connections
to the database. They may also be a large number of EXTENTS of type TEMPORARY
in the temporary tablespace.
Users may also experience ORA-1652 when running transactions. This error implies
that there is no free space left. SMON is not cleaning up after waiting for
hours and queries are failing.
Scope & Application
===================
If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released
until the instance is shutdown. If the TEMPORARY TABLESPACE is of type
PERMANENT, then cleanup is performed by SMON after completion of the process
using it.
Â…
Â…
For performance issues, tablespaces marked as temporary allocate extents
as needed, but the extents are not released or deallocated after the operation
is done. Instead, the extent is simply marked as FREE or available for the
next sort operation. This eliminates the need to continuously allocate and
deallocate extents after every sort operation, hence gaining performance
benefits.
Â…
Â…
===============
And this last parahraph is totally confusing to me. Please, would you explain same.
Please, currect me if IÂ’m wrong.
Thanking you.
Regards,
Suvendu
September 13, 2005 - 4:38 pm UTC
your temp is allocated, you are not using any of it -- it is completely and totally available for use.
It is like a rollback segment - they are "allocated", but they are not "full" unless they are full -- you query the dictionary and you see they take 500mb, but you don't know if they have 1 byte used or all bytes used (until you look elsewhere)
temp is just like that, it is allocated, it is not used however unless v$sort_usage says it is.
The problem with your example was the fact that the sort was done, over, finished by the time you queried v$sort_usage.
You have to open a query that does a sort, and keep it open to see the sort space used:
big_table@ORA10G> variable x refcursor
big_table@ORA10G> declare
2 l_rec big_table%rowtype;
3 begin
4 open :x for select * from big_table order by 1,2,3,4,5,6;
5 fetch :x into l_rec;
6 end;
7 /
PL/SQL procedure successfully completed.
big_table@ORA10G>
big_table@ORA10G> select * from v$sort_usage;
USERNAME USER SESSION_
------------------------------ ------------------------------ --------
SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE
----------- -------- ---------- ------------- -------------------------------
CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
--------- --------- ---------- ---------- ---------- ---------- ----------
BIG_TABLE BIG_TABLE 8F94E580
4933 8D4C7E4C 1029988163 9babjv8yq8ru3 TEMP2
TEMPORARY SORT 202 9 230 29440 1
BIG_TABLE BIG_TABLE 8F94E580
4933 8D4C7E4C 1029988163 9babjv8yq8ru3 TEMP2
TEMPORARY SORT 202 29449 230 29440 1
big_table@ORA10G> @connect /
big_table@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G> select * from v$sort_usage;
no rows selected
Not much more clear..
Suvendu, September 13, 2005 - 7:15 pm UTC
Hi Tom,
Thanks a lot for such a quick response. IÂ’m coming to know more on this discussion. And your help is amazing.
From beginning of this discussion, IÂ’m point and as metalink telling:
“If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released until the instance is shutdown.”
I could not able to more clear on your above answer regarding this. I would appreciate, if you could elaborate same more clearly.
Please, have a look into these statements:
SQL> create temporary tablespace temp3 tempfile 'd:\ORACLE\ORADATA\DBORC\TEMP03.dbf' size 200m;
Tablespace created.
SQL> @ c:\free.sql 1
%
% MaxPoss Max
Tablespace KBytes Used Free Used Largest Kbytes Used
---------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a INDX 25,600 15,744 9,856 61.5 9,152 33,554,416 .1
*a USERS 25,600 14,528 11,072 56.8 11,072 33,554,416 .1
*m SYSTEM 583,680 574,336 9,344 98.4 9,152 33,554,416 1.7
*m TEMP 62,464 62,464 0 100.0 0 33,554,416 .2
*m TEMP3 204,800 204,800 0 100.0 0 0 .0
*m UNDOTBS 204,800 92,032 112,768 44.9 111,552 33,554,416 .61
Where we are getting TEMP3 100% used and which is not like so.
As per my understanding (Sorry, not to provide any test case) :
Step 1: A sort operation taking place in disk and oracle allocating a sort segment.
Step 2: This segments space will remain marked as USED till shutting down process (as metalink telling).
Step 3: On repetitions of this process, gradually all space will mark as USED and finally we will get ORA-01652.
Could we have a query which will give the exact information what information we are getting from PERMANENT tablesapces usages.
I would appreciate, if you could share some time with an test case to confirm metalinks statements:
“TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released
until the instance is shutdown.”
Please, correct me if I'm wrong.
Thanking you.
Regards,
Suvendu
September 13, 2005 - 7:35 pm UTC
they were talking about very old fashioned "permanent tablespaces used as temporary space"
and that the space was not release was of little importance - it is back to the "it allocates space, it is there, but it is free to be used by temp"
they shouldn't appear *empty* that would be bad - it means we have to allocate it all over again.
usage
Alay, September 15, 2005 - 10:43 am UTC
Hi Tom,
Suppose in my database all users log out at night. One day i seen with help of OEM that after log out all the users the usage of temporary tablespace is 99%. How can i get it (usage which is 99%) down without shutting down and without deleting & then recreating tempoarary tablespace?
September 15, 2005 - 11:35 am UTC
why? You don't WANT to (i keep saying)
All that would do is make you REALLOCATE IT ALL OVER AGAIN tomorrow.
If v$sort_usage is empty, so is temp. We manage the space *internally*. Allocate once, use over and over and over...
proof from oracle documentation
Asim Naveed, September 15, 2005 - 10:56 am UTC
Hi Tom,
The following is a copy/paste from this discussion.
You said while discussing the TEMPORARY tablespaces
"You will never see an extent freed in this tablespace until you
shutdown (or alter the tablespace in some way). This is normal and is OK -- we
are not losing extents, we are just managing them internally -- in memory,
instead of in the data dictionary."
Ok, I have checked this and found your statement true.
But can I have a proof of this statement from
ORACLE'S DOCUMENTATION. I want to show that documentation
to my friend who doesnt believe on this statement.
Thanks
September 15, 2005 - 11:50 am UTC
ask them "why does it work OK if it is full" -- v$sort_usage is what counts here.
but the admin guide covers this:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm#9566 <code>
Temporary Tablespaces
To improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, create temporary tablespaces. A temporary tablespace can be shared by multiple users and can be assigned to users with the CREATE USER statement when you create users in the database.
Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment.
Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments.
That could be the final answer about temp space usage ...
Suvendu, September 15, 2005 - 1:41 pm UTC
Hi Tom,
Thanks a lot for this answer. And I'm sorry not see document from beginning.
I would appreciate, if you could comment on my understanding from the oracle document paragraph:
1. After startup process a single sort segment creates and all sort operation takes place within it.
2. During sort period, we can obtain the sort usage information from V$SORT_USAGE, V$SORT_SEGMENT
and V$TEMPSEG_USAGE. And this information removes from these views after referenced operation
finished.
3. Allocated extents in sort segment go to deallocate by internal mechanisms.
4. And, created that single sort segment goes to release (drop) at shutdown process.
5. Always a temporary tablespace associates with a SINGLE sort segment.
6. If, there is no row data from V$SORT_USAGE, then it says temporary tablespace is FREE
OR there is space in sort segment for use.
Thanking you.
Regards,
Suvendu
September 15, 2005 - 2:32 pm UTC
1) the sort segement is created implicitly by the first operation (statement) that needs it
2) correct
3) correct
4) correct
5) correct
6) means no space is used, anything allocated is free and more might be able to be allocated if the tablespace permits it.
is it deallocated?
reader, September 15, 2005 - 3:36 pm UTC
Re Q3 from above reader: My understanding is that the allocated extents are not deallocated but marked as "free" and they are there after instance shutdown and restart.
Following cut and paste is from database version 10.1.0.2
SQL> select TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS from v$sort_segment;
TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS
------------- ------------ ------------
17 0 17
SQL> select * from v$sort_usage;
no rows selected
SQL> connect / as sysdba;
Connected.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 778036 bytes
Variable Size 162537676 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> connect system/oracle;
Connected.
SQL> select TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS from v$sort_segment;
TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS
------------- ------------ ------------
17 0 17
Do deallocate and free mean the same? Thanks.
September 15, 2005 - 4:42 pm UTC
space is free to be used - space is free to be used.
v$sort_usage- undocumented view?
Sami, February 18, 2006 - 7:05 pm UTC
only for sorting?
A reader, March 02, 2006 - 1:37 pm UTC
What operations are performed by oracle on temporary
tablespace. Is sorting the only aim of temporary tablespaces?.
If my database is doing a lot lot lot of work but none
of them require any kind of sorting, the temporary
table will remain empty?
Thanks
March 02, 2006 - 2:22 pm UTC
sorting, hashing, temporary tables, anytime we would need to swap to disk, consider it "swap"
temporary tablespaces will appear "full" as in "allocated", you would use v$ views to see what is actually being used at any point in time.
Tom Fox, March 02, 2006 - 3:04 pm UTC
Can you tell the OEM programmers how to look at TEMP? We have 9i OEM (preparing to test 10gR2), and it pages constantly that TEMP is 407.29% full (or some other number > 100%). It's driving us all crazy. I hope 10gR2 fixes this.
Alert when temp tablespace is full
Anthony, March 03, 2006 - 2:20 am UTC
Where will I see if the temp tablespace is actually filled? Will it write a message to alert log? If not how can I know that users are getting errors because of temp tablespace filled due to some big sorts and temp tablespace filled.
Thanks
March 03, 2006 - 8:10 am UTC
you would get a message in the alert log if a session gets unable to allocate in temp
sorts (memory) and sorts (disk) statistics
A reader, August 22, 2006 - 8:27 am UTC
Hi Tom
I dont have right now an example, however in the past i have seen many times a nquery blows up a Temporayr Tablespace but sorts (disk) shows very small value or including cero.
I remmeber J. Lewis mentioned something about this behaviour but I just cant remember and I just cant find anything on metalink about this behaviour neither.
Do you know what can cause this?
Cheers
August 27, 2006 - 2:20 pm UTC
you only need 1 sort to disk to "blow out" your temporary tablespace.
it is called a "big sort".
vague references to "I heard from a friend who overheard a conversation about something...." - I cannot really respond with anything meaningful.
Temporary Tablespace Group
Stan, March 31, 2008 - 2:26 pm UTC
Hi Tom,
our current system which has evolved over the years has several temporary tablespaces defined. The purpose I'm told is to reduce contention. Now, in 10g I note there is a "Temporary Tablespace Group" ability. When I google this everything I find seems to explain how to do it - no when, why, or best practice.
Any thoughts on the multiple temporary tablespaces and what makes sense with grouping (or point me somewhere)?
Thanks in advance.
March 31, 2008 - 3:01 pm UTC
I doubt you need them to reduce contention today. It would be so rare - the temporary tablespace groups serve the same purpose - but in a better way. With your current set of temporary tablespaces - you have assigned individual users to their own temp - groups would spread the load out over many - regardless of userid.
And it is more than likely you don't need it, just a good striping of disks to spread the IO out.
Temporary Tablespace Group - Follow up
Stan, April 01, 2008 - 8:50 am UTC
OK, so my thoughts are:
1) Our temporary tablespaces are different sizes (originally designed based on expected loads), so I should just group them and assign everything to the group to simpilfy things.
2) For performance, if the disks are striped properly there really is no need for creating separate tablespaces in the first place? Is there any stat which would indicate a need for separation?
Thanks again.
April 01, 2008 - 9:39 am UTC
1) you likely won't need the SUM(existing temp), but some number smaller than that (since now they cannot be SHARED, and in the future they WILL BE shared - and it is unlikely they would all be fully utilized simultaneously
2) contention (IO related metrics) and space management waits (which should not be an issue once the tablespace is allocated)
drop tablespace temp ... take lot of time and never completes.....
Mehmood, April 02, 2008 - 4:03 pm UTC
Dear Tom
When ever we try to drop temporary tablespace having created the new temporary tablespace, it take lot of time and never get completed, and when we checked the locks, we found that the session is waiting for SMON, can you shed light on this.....
Thanks in advance....
Free TEMP Space
Ricardo PatrocÃnio, April 23, 2008 - 12:07 pm UTC
Hello Tom,
Some time in the past I had a few very heavy queries that made my TEMP tablespace grow to much, now I need the space in the hard drive.
How can I resize the TEMP datafiles to free some space in the hard drive?
Thank you,
R.P.
April 28, 2008 - 9:26 am UTC
a) create new temp of smaller size
b) alter database default temporary tablespace to that new one
c) then you can drop the old. (assuming no one is currently using it, you might have to wait till they are done)
ops$tkyte%ORA10GR2> @free 1
Wrote file /tmp/xtmpx.sql
old 30: order by &1
new 30: order by 1
%
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a BIG_TABLE 162,816 160,960 1,856 98.9 960 33,554,416 .5
*a SYSAUX 522,240 481,536 40,704 92.2 8,128 33,554,416 1.6
*a TEST 102,400 64 102,336 .1 102,336 33,554,416 .3
*a USERS 451,840 12,992 438,848 2.9 194,560 33,554,416 1.3
*m MSSM 8,192 64 8,128 .8 1,024 33,554,416 .0
*m SYSTEM 565,248 557,888 7,360 98.7 7,104 33,554,416 1.7
*m TEMP 128,000 128,000 0 100.0 0 33,554,416 .4
*m UNDOTBS1 283,648 283,584 64 100.0 64 33,554,416 .8
------------ ------------ ------------
sum 2,224,384 1,625,088 599,296
8 rows selected.
ops$tkyte%ORA10GR2> create temporary tablespace temp_new tempfile size 10m autoextend on next 10m;
Tablespace created.
ops$tkyte%ORA10GR2> alter database default temporary tablespace temp_new;
Database altered.
ops$tkyte%ORA10GR2> drop tablespace temp;
Tablespace dropped.
ops$tkyte%ORA10GR2> @free 1
Wrote file /tmp/xtmpx.sql
old 30: order by &1
new 30: order by 1
%
% MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a BIG_TABLE 162,816 160,960 1,856 98.9 960 33,554,416 .5
*a SYSAUX 522,240 481,536 40,704 92.2 8,128 33,554,416 1.6
*a TEST 102,400 64 102,336 .1 102,336 33,554,416 .3
*a USERS 451,840 12,992 438,848 2.9 194,560 33,554,416 1.3
*m MSSM 8,192 64 8,128 .8 1,024 33,554,416 .0
*m SYSTEM 565,248 557,888 7,360 98.7 7,104 33,554,416 1.7
*m TEMP_NEW 10,240 10,240 0 100.0 0 33,554,416 .0
*m UNDOTBS1 283,648 283,584 64 100.0 64 33,554,416 .8
------------ ------------ ------------
sum 2,106,624 1,507,328 599,296
8 rows selected.
Alexander, August 20, 2008 - 4:47 pm UTC
Tom,
If I have multiple temp tablespaces, will setting the default temp to a user use the others also if it needs it, or will it bomb after it exceeds the default?
Thanks.
August 21, 2008 - 8:32 am UTC
if you assign users to a specific temp tablespace, it'll use only that one for them, it'll not use the others.
you have temporary tablespace groups - they will round robin allocate.
you have what I think is easier/better - a single big temp tablespace - do not create more than one. It would be an extremely rare case that needed more than one.
Alexander, August 21, 2008 - 10:06 am UTC
I agree, I should have explained why I might want to do this.
We are building a new RAC environment for a critical system.
People want to be able to run reports, we have this product called hyperion that allows users to basically query the entire database at once.
The architect for the environment is afraid this will impact the OLTP system, so he want a separate reporting database that we'll have to refresh everyday.
So I was thinking if we could give hyp it's own temp it won't hog the OLTP just in case.
Maxing out 10-12gig of temp is the only problem I've seen from a database perspective that causes me any concern about having 1 database.
August 21, 2008 - 9:28 pm UTC
if your OLTP applications need temp, they are not OLTP - OLTP is characterized by small fast queries that return small sets of data. If they are spilling to disk, into temp, you have a problem.
I don't know why you are trying to solve a hypothetical problem that shouldn't exist....
If hyperion report users use a given account or set of accounts, those accounts can be assigned to a temporary tablespace of their own - and the OLTP users to their own.
But the OLTP users should likely never ever need it - if they do, you have a problem regardless of what else is going on in the database.
Alexander, August 22, 2008 - 12:36 pm UTC
That is my understanding also, the OLTP applications will not be using temp.
However, if the reporting users can submit massive queries that use temp, doesn't that imply they have consumed all available PGA, and that will impact everyone, right?
Would this be a case where I would want to investigate the use of resource manager?
August 26, 2008 - 7:32 pm UTC
... doesn't that imply they have consumed all available PGA ...
absolutely FALSE.
automatic pga memory management is sensitive to the number of concurrent users and the workload. It limits the amount of PGA a given session has - so that ALL SESSIONS HAVE PGA.
You can have a single session
Run a big query
That uses temp
and less than 5% of the available PGA memory would be used.....
You can have <many> sessions
Many of which run big queries
And some of them use temp
and there is still PGA memory waiting to be used.
That is what it does.
SMON doubt,
Aman...., September 13, 2008 - 11:09 pm UTC
Sir,
Few replies above you mentioned this,
SMON doesn't clean up temporary tablespaces. It does not work like that anymore. It worked like
that years and years ago with permanent tablespaces used to hold temporary data.
There is no cleaning up by SMON
There is no coalescing
And sys and system should probably need pretty much 0 sort space as you aren't using them to do
things and recursive sql run as sys should not be doing sorts that hit disk.
So does it mean that now SMON doesn't do any clearing of temporary tablespaces at startup? I see lots of times myself and from others too this issue that even afterthe startup and using a "true temporary " tablespace, space within is not reclaimed by Oracle.Temp tablespace keeps on having the same space which it had prior to shutdown when it is told that SMON would clear it in the next startup? What is the reason for this?
Regards
Aman....
September 16, 2008 - 9:46 pm UTC
SMON will free up orphaned temporary segments/extents. Eg: if you do a create index, you'll see the index is created in a temporary segment that is turned into a REAL segment at the end. If the create index fails (system is killed), upon restart SMON will free up the orphan temporary segments in permanent tablespaces - just like before.
Temp tablespaces in the current release - they just allocate - they do not release space back, it would be not efficient to do otherwise.
So, SMON will clean up failed "create" statements at startup - but only because those are the only temporary segments you'll see in permanent tablespaces these days.
Aman...., September 17, 2008 - 12:58 pm UTC
Thanks sir.
Can you please explain this line
but only because those are the only temporary segments you'll see in permanent tablespaces these days.
Regards
Aman....
September 17, 2008 - 1:18 pm UTC
in the year 2008, no one would have a 'temporary tablespace' created in a permanent tablespace.
They would be using TRUE temporary tablespaces - smon need do nothing with these.
Therefore, the ONLY temporary segments you'll see that SMON needs to worry about are those left over from failed create statements.
The ONLY temporary segments you'll see in a permanent tablespace will be those used by a create statement to hold the newly created data while it is being constructed.
Eg, to "create table t as select * from all_objects"
Oracle will
a) allocate a temporary segment in the users default tablespace.
b) run the query select * from all_objects and direct path load this temporary segment.
c) when the query is finished, we'll turn the temporary segment into a permanent one.
If the system fails during processing - when the database is restarted - SMON will see the temporary segment created in the permanent tablespace and clean it up.
Those are the only temporary segments you'll see that smon needs to deal with - because we do NOT create temporary tablespaces using permanent tablespaces anymore. We used to in days of old - way back in history - but not anymore.
Aman...., September 18, 2008 - 11:24 am UTC
Sir,
So temp segments which are assigned to SMON now are those wich are from failed statements. This gives arise to the question that why still it is mentioned that temp tablespacese are cleared by SMON process at next startup?Here temp tablespace I mean actual temp tablespace only.
And second part is that if SMON doesn't clear anything in the temp tablespace and they are supposed to be clearedwith the next startup, why so many times we see that the temp tablespace is not freed? It has the same space which it had before the shutdown.What's the reason for this?
Thanks and regards
Aman....
September 18, 2008 - 11:38 am UTC
it is mentioned that temporary segments left from failed processes are cleaned up by smon.
Today, in the year 2008, that is just going to be from failed create statements.
But that doesn't render the description invalid.
temp is NOT SUPPOSED TO BE FREED. All that would be accomplished by freeing up the temporary extents would be that WE WOULD HAVE TO ALLOCATE THEM ALL OVER AGAIN.
The only thing that can be in a temporary tablespace is temp. So, once we allocate and format the blocks in there - we don't let it go - we manage it internally.
use v$sort_usage to see what is used - it'll be "empty" when you start up (meaning regardless of how much of temp is allocated - no one is using it) and it'll have rows as people start to use it (and those rows will go away when they are done with it)
Temporary tablespaces
lalu, September 19, 2008 - 1:46 am UTC
HI Tom,
I have a 5TB DB with some index sizes >15GB.
The pga_aggregate_target is set to arround 10/11 GB.
There used to be data archival/purging on some tables and then index rebuilding on those tables.
My concern is:
less than 5% of the available PGA memory would be used..... for one session.
Then the index rebuild will use only 500MB of the PGA and rest will be used in the temp tablespace.
How can i speed up the index rebuild process?
Does setting sort_area_size(We are moving backword) at session level to something higher value(5/10/15G) will help?
Do we have any other method?
Thanks.
Lalu
September 19, 2008 - 7:59 am UTC
more memory does not mean faster in all cases.
and a rebuild of an index would not sort (unless the index was NOT maintained during the operation - eg: set to unusable and skipped). The rebuild would in fact read the existing index and just rewrite it - a rebuild of an index that was maintained does not have to sort, it reads the existing index.
so, are you even sure that it is using temp in that fashion (you will see TEMP EXTENTS/SEGMENTS created - but in the permanent tablespace, we rebuild into temporary extents and then convert them into permanent ones at the end - but this is not "sort temp" space)
and using different techniques (manual/auto memory management) is not a step "backwards", it is "a choice", "an option". You have to understand that automatic pga memory management is designed to "be fair for a group of users", it was not designed for "I'm the ONLY user"
Tablespaces
Parminder, May 21, 2009 - 8:04 am UTC
Hi Tom
I have only 1 thing to say
That you are grate
Thanks,
Parminder Singh
V$temp_space_header
Parminder, May 21, 2009 - 8:07 am UTC
I am using 8i
I check v$temp_space_header view..it shows 0 bytes free in temp1 tablespace.
Could you please let me know is it right view to see temp tablespace usage?
Thanks
Psingh
May 23, 2009 - 1:15 pm UTC
Dropping Unused Tempfiles
Matt, June 10, 2009 - 3:53 pm UTC
How can I look at a particular tempfile and tell if I can
drop it without causing an error to someone that's using it?
Under 10.2.0.2 I've increased the DB's default temporary
tablespace by many tempfiles to support some unusual sort
activity. Once that activity is done I'll want to reclaim
that space. I'm hoping to:
SQL> alter database tempfile n drop including datafiles;
So, I'd rather not drop and recreate the temp tablespace,
and I definitely cannot take the DB down for this. I just
want to drop tempfiles that aren't being used.
v$tempseg_usage doesn't seem to tell me whether a
particular tempfile has extents in use at any time.
June 10, 2009 - 4:41 pm UTC
it is going to be very hit and miss with
alter database tempfile 'x' resize N
first, we don't expose exactly what extents are actively being used (just the initial extents) and second - by the time you looked to figure out how big the file "could be", the answer will have changed.
I know you said "I don't want to" do something, but that will be the path of least resistance
a) create new
b) alter everyone to use it
c) watch v$tempseg_usage to see when old isn't used
d) drop it
else, you just have to guess how small each tempfile might be able to become and try to resize it to that size - completely hit and miss.
temp space
A reader, June 12, 2009 - 9:35 am UTC
Tom:
We ran into issue with TEMP space size at 6gig a few days ago:
1. What do you do to figure out what caused it? is there a way to verify it now or not.
2. You say oracle will manage it internally in memory then why it did happen? is it because autoextend is OFF - but still it should manage it. IF autoextend is ON and LMT is used would this ever happen.
3. What is the difference between using datafiles and tempfiles for temp space? would it make a difference in that ORA space error showing up.
4. what is temp space primarily used for (sorts and cursor cache). What is the primary SQL that causes the space issue.
thanks,
June 12, 2009 - 11:30 am UTC
1) hah, I don't even know what you mean by "an issue".
My car won't start.
2) manage WHAT?
3) you don't use datafiles for temp space (not for very very very many Oracle releases), you use tempfiles for temp space - therein lies the difference.
4) the primary sql would be sql that sorts/aggregates/hashes/bitmap merges lots of data.
space
A reader, June 12, 2009 - 12:08 pm UTC
Tom:
The issue is an error for running out of space in 5 gig TEMP tablespace.
Can this be blamed on some erroneous program or is it an oacle bug or is it a configuration issue.
Can you find out what caused that and permanent solution to eliminate it.
June 12, 2009 - 1:15 pm UTC
definitely not a bug.
You cannot even say anyone is to blame (except maybe the DBA - maybe they undersized the temporary tablespace)
In the year 2009, 5gb is tiny.
Queries sort/hash/do things. They try to do this in memory (your pga_aggregate_target controls how much memory they will use). If what they are doing is too large to fit into the available memory, they will swap it out to disk - write it to temp - and then read it back in later.
Now, if you have lots of users doing little sorts - that just exceed the sort/hash memory workareas, you'll easily get to 5gb of space used. If you have a few users running really large queries - same thing. Add to that the fact that global temporary tables use temporary tablespaces as well - it is easy to see how on an active system you would "run out".
You can monitor
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2164.htm#i1421319 v$tempseg_usage to see who is using how much of temp and for what they are using it.
temp
A reader, June 12, 2009 - 2:54 pm UTC
Tom:
that view has nothing so i guess i cant see what caused it.
this is low usage system.
Does not this mean it is a bug??
From: Oracle, Helen Schoone 15-Apr-03 20:21
Subject: Re : Temp Tablespace Management
Hi. There is a bug (bug 2193406) filed against 9.2, although it appears to effect 8i (bug 1003703) as well, which is an issue where you are unable to resize a temporary tablespace even though it is not currently being used. The bug is assigned to development. The workaround is to drop and re-add the tempfile(s).
2. I found this in V$SQL timed around when it froze.
can this be teh cause with those stats?
SELECT "vidseq","vidme","stage","contr","delivdate" FROM "production"
WHERE "delivdate">=:1-100 AND "delivdate"<=:2-70 AND ("stage"='ab' OR "stage"='bc' OR "stage"='er')
Disk_read = 208,651
bhuffer gets = 531,300
cpu time = 47,710,000
elapsed time = 210,358,211
3. is there a way to configure DB to prevent it parmanently
June 12, 2009 - 3:02 pm UTC
that bug has nothing to do with you - you ran out of temp space, you filled it up, you had more going on than you sized your temp space for, you had lots of queries going on or a few big ones.
you were not trying to resize anything right - so why would that bug even come up on the radar screen
(I think you are over analyzing this problem - it is really quite simple - you filled up temp)
what "froze", you got an error. nothing froze.
you probably found HUNDREDS of queries in v$sql - why pick on that one. That one that does have an order by, aggregrate, join - in short that one that would have no reason to use temp.
And take all of those numbers and divide by executions - you are seeing cumulative information.
as for number 3 - sure,
create temporary tablespace temp tempfile size 100tb;
100tb should do it. I mean, really - this is TEMP, you use TEMP to sort, you either
a) had a query that used more temp than you expected
b) had more users doing sorts at the same time than you expected
c) have undersized temp for your workload
Sam - this is not rocket science. This is not a bug. This is nothing especially "special".
Do you understand how programs use temp space? How things like virtual memory and swapping happen? How computers in short make it look like there is more 'memory' than exists by using disk to swap things in and out?
temp space
A reader, June 12, 2009 - 8:07 pm UTC
Tom:
Can you tell if this TEMP was configures: DMT or LMT, datafiles or tempfiles, PERMANENT or TEMPORARY
it seems LMT and temporary - not sure about datafiles.
TABLESPACE_NAME : TEMP
BLOCK_SIZE : 8192
INITIAL_EXTENT : 1048576
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS :
PCT_INCREASE : 0
MIN_EXTLEN : 1048576
STATUS : ONLINE
CONTENTS : TEMPORARY
LOGGING : NOLOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : UNIFORM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : MANUAL
A reader, December 07, 2009 - 3:42 pm UTC
Hello Tom;
I dont understand why temp is used inside SYSTEM tablespace rather than normal temp tablespace?
Here is the query which was captured by trigger that was causing temp error.
Can you please clear my doubts? (oracle 9i, dictionary managed tbs)
select distinct i.*
from PREM i, Summ p
WHERE i.Contract_Branch_Id = p.Pol_Branch_Id
AND i.Product_Code = p.Product_Code
AND i.Contract_Pol_No = p.Pol_No
AND p.Transaction_Type = 'V23'
AND p.Accounting_Prd IN
(TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE, -1),'YYYYMM')), TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMM')))
ORA_E_ALERT nzuw1p1 ORA-1652: unable to extend temp segment by 5394 in tablespace SYSTEM
{code}
Tablespace Name KBytes Used Free Used Largest
--------------------------- ------------ ------------ ------------ ------ ------------
SYSTEM 411,640 245,840 165,800 59.7 75,428
TEMP 6,156,264 4,188,184 1,968,080 68.0 10,240
{code}
December 10, 2009 - 7:27 am UTC
what is the temporary tablespace of the user running the query?
select username, temporary_tablespace from dba_users;
11g tempfile shrink
Sita, December 05, 2011 - 9:39 pm UTC
Hi Tom:
I tried to run the following alter command to freeup the particular tempfile of the temporary tablespace but apparently the entire tablespace freespace has been released.
I expected that Oracle will release freespace from the particular tempfile...
tempfile was allocated with 64GB.
SQL> alter tablespace TEMP shrink tempfile '/ora/prod/database/temp/TEMP_27.dbf' keep 2G ;
Tablespace altered.
December 06, 2011 - 11:17 am UTC
I don't know what you mean.
11g shrink tempfile
Sita, December 06, 2011 - 7:47 pm UTC
Ok, TEMP tablespace was created with 28 tempfiles.
I tried to shrink temp space from one tempfile as shown using the below command. But it released the tempspace from other tempfiles.
alter tablespace TEMP shrink tempfile '/ora/prod/database/temp/TEMP_27.dbf' keep 2G ;
Why it freed up the temp space from other tempfiles also ?
I thought the Oracle would shrink only the particular tempfile given.
December 07, 2011 - 1:10 pm UTC
prove it please, show your work like I show you my work.
sys%ORA11GR2> create temporary tablespace tt2
2 tempfile '/tmp/t1.dbf' size 5m,
3 '/tmp/t2.dbf' size 5m,
4 '/tmp/t3.dbf' size 5m;
Tablespace created.
sys%ORA11GR2>
sys%ORA11GR2>
sys%ORA11GR2> select name, bytes, create_bytes from v$tempfile where name like '/tmp/t_.dbf';
NAME BYTES CREATE_BYTES
------------------------------ ---------- ------------
/tmp/t1.dbf 5242880 5242880
/tmp/t2.dbf 5242880 5242880
/tmp/t3.dbf 5242880 5242880
sys%ORA11GR2> alter tablespace tt2 shrink tempfile '/tmp/t2.dbf' keep 2m;
Tablespace altered.
sys%ORA11GR2> select name, bytes, create_bytes from v$tempfile where name like '/tmp/t_.dbf';
NAME BYTES CREATE_BYTES
------------------------------ ---------- ------------
/tmp/t1.dbf 5242880 5242880
/tmp/t2.dbf 3137536 5242880
/tmp/t3.dbf 5242880 5242880
sys%ORA11GR2>
sys%ORA11GR2> drop tablespace tt2
2 including contents and datafiles;
Tablespace dropped.
Continuation of previous post
Sita, December 06, 2011 - 7:49 pm UTC
11g shrink tempfile
Oracle Version: 11.2.0.1