|
-
how to clean up TEMP tablespace ?
Hello all
Oracle9.2 on sun 5.8
My TEMP tbs is 98% FULL. This tbs is LOCALLY managed. How do we celan it up without bouncing the instance ?...
Any help is appreciated.
thanks
Ron
-
you can't clean up the TEMP tablespace in local extent in 9i. It shows 98% full b/c Oracle preallocated the tablespaces. you should not worry about it unless you want to drop the temp and recreate the new one.
-
____________________________________________________________
It shows 98% full b/c Oracle preallocated the tablespaces.
___________________________________________________
Thanks Mike2000...
I ddn't get it when u said "preallocated ". Do u mean that Oracle 9i has preallocated 'x' bytes of space for this TBS ?...
reason is , my other 9i DB TEMP tbs is only 17% Full..
any help is appreciated...
thanks
ron
-
What you need to do is read the Oracle documentation on how it uses temporary tablespaces -- it changed in 8i.
Search the forum for previous questions on temp tablespaces being full, 'cos this gets asked about once a month.
-
Temporary segments cleared when Database is shutdown.
A temporary segment created in temp tablespace will not be deallocated at the end of the sort. The extents are MARKED as free, are not placed back on the freelist, and are retained for reuse. An element called the Sort Extent Pool (SEP) is created in the SGA to describe them.
Subsequent sort operations will then be allocated extents from this pool via a memory lookup.
Dilip 
Dilip Patel
OCP 8i
Catch me online at Yahoo: ddpatel256
-
Do you get an error trying to allocate big sort segment?
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
Hi...
I got the same problem, I can release some space in TEMP after running this query :
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks
Then kill the session which is holding the segments, if you have TOP Session utility from OEM it will be easier...
If you restart the DB will solve the problem...
TIA
Jeram
Oracle DBA
-
If you restart the DB will solve the problem...
Which problem is that?
-
Originally posted by vr76413
____________________________________________________________
It shows 98% full b/c Oracle preallocated the tablespaces.
___________________________________________________
Thanks Mike2000...
I ddn't get it when u said "preallocated ". Do u mean that Oracle 9i has preallocated 'x' bytes of space for this TBS ?...
reason is , my other 9i DB TEMP tbs is only 17% Full..
any help is appreciated...
thanks
ron
Are you sure that in your other DB the TEMP tablespace is a temporary tablespace and not permanent tablespace.
Do a select tablespace_name,file_name from dba_temp_files to find out if its actually a temporary tablespace.
Because if its a LMT Temporary tablespace then the extents are
pre-allocated.
Code:
sys@ACME.WORLD> create temporary tablespace TEMP_TBS tempfile 'e:/temp_tbs01.dbf' size 50M;
Tablespace created.
sys@ACME.WORLD> select tablespace_name,file_id,block_id,blocks,bytes/1024/1024 from v$temp_extent_map
2 where tablespace_name = 'TEMP_TBS';
TABLESPACE_NAME FILE_ID BLOCK_ID BLOCKS BYTES/1024/1024
------------------------------ ---------- ---------- ---------- ---------------
TEMP_TBS 2 9 128 1
TEMP_TBS 2 137 128 1
TEMP_TBS 2 265 128 1
TEMP_TBS 2 393 128 1
TEMP_TBS 2 521 128 1
TEMP_TBS 2 649 128 1
TEMP_TBS 2 777 128 1
TEMP_TBS 2 905 128 1
TEMP_TBS 2 1033 128 1
TEMP_TBS 2 1161 128 1
TEMP_TBS 2 1289 128 1
TEMP_TBS 2 1417 128 1
TEMP_TBS 2 1545 128 1
TEMP_TBS 2 1673 128 1
TEMP_TBS 2 1801 128 1
TEMP_TBS 2 1929 128 1
TEMP_TBS 2 2057 128 1
TEMP_TBS 2 2185 128 1
TEMP_TBS 2 2313 128 1
TEMP_TBS 2 2441 128 1
TEMP_TBS 2 2569 128 1
TEMP_TBS 2 2697 128 1
TEMP_TBS 2 2825 128 1
TEMP_TBS 2 2953 128 1
TEMP_TBS 2 3081 128 1
TEMP_TBS 2 3209 128 1
TEMP_TBS 2 3337 128 1
TEMP_TBS 2 3465 128 1
TEMP_TBS 2 3593 128 1
TEMP_TBS 2 3721 128 1
TEMP_TBS 2 3849 128 1
TEMP_TBS 2 3977 128 1
TEMP_TBS 2 4105 128 1
TEMP_TBS 2 4233 128 1
TEMP_TBS 2 4361 128 1
TEMP_TBS 2 4489 128 1
TEMP_TBS 2 4617 128 1
TEMP_TBS 2 4745 128 1
TEMP_TBS 2 4873 128 1
TEMP_TBS 2 5001 128 1
TEMP_TBS 2 5129 128 1
TEMP_TBS 2 5257 128 1
TEMP_TBS 2 5385 128 1
TEMP_TBS 2 5513 128 1
TEMP_TBS 2 5641 128 1
TEMP_TBS 2 5769 128 1
TEMP_TBS 2 5897 128 1
TEMP_TBS 2 6025 128 1
TEMP_TBS 2 6153 128 1
49 rows selected.
sys@ACME.WORLD>
See above, here i just created a temporary tablespace of size 50M and all extents were preallocated.
HTH
Amar
"There is a difference between knowing the path and walking the path."
 
-
I'll give you guys a little tip also regarding Oracle 9i's TEMP tablespace DATATFILES that caught me a while ago.
Just say you have a 10 GB filesystem on UNIX, and we've decide to place a say... 2 4GB datafiles an a 2 GB Temp datafile under it.
If you check the size of the files system... you'll see that there is still 2GB left on the filesystem (even though you've allocated the full 10GB). This is because even though you specified the TEMP TABLESPACE 2GB it's not utilised on unix until the TEMP tablespace is written to. Therefore, a while down the track, somebody comes along and notices 1.5 GB of free filesystem space under the file system and creates another 1.5GB datafiles... guess what, when you're TEMP tablespace starts being used again... it runs out of space.
Just thought I'd mention it...
Bye...
OCP 8i, 9i DBA
Brisbane Australia
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
