TEMP SEGMENT FULL

HI ..i received 0ra 1652
saying that my temp tablespaces is full.

if i don't do anything (addding new datafile or resize)
,is oracle will recycle the temp segment
to do sorting , etc.

if i restart the database, is it going to
empty back the temp segment.

i am using Oracle 8i on solaris.

TQ
tsejahteraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bmoshierCommented:
Oracle will recycle temp segments once the transaction using the segments ends.

The space will be released to the system once the process that is using TEMP has either finished the SQL statement or disconnects. It can take a while for this to clear up, all depending on the activity on the database, the size of the temporary segment, and when the background process for cleaning up temp segments wakes up.

For example with 3300+ extents, you may want to:

(A) increase your SORT_AREA_SIZE to handle more of the processing in memory -and/or-
(B) increase the default INIT / NEXT storage sizes for your TEMP tablespace.

In either case, you may want to consider increasing the size of the TEMP tablespace.

-----

Contiguous free space is NOT automatically coalesced unless the default PCTINCREASE storage parameter for the temporary tablespace is non-0.  If you want to coalesce, issue:

ALTER TABLESPACE temp_tablespace_name COALESCE;

FYI:  This does not work when using LMT (Locally Managed TEMPORARY Tables) on Oracle 9i.

--

Restarting the database will end the transactions.  The system will free up the segments but it may not be immediate, but soon.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tsejahteraAuthor Commented:
when i try to resize , i received an erorr
saying that i cannot resize.
my temp now is 20GB size.
0
bmoshierCommented:
The following works for me (Windows XP Professional) while in the $ORACLE_BASE\oradata\$ORACLE_SID subdirectory, using an username with the DBA role.  The commands are:

1)  alter database tempfile 'c:\oracle\oradata\sneakers\temp.ora' drop;

2)  host erase temp.ora

3)  alter tablespace temp add tempfile 'c:\oracle\oradata\sneakers\temp.ora' size 10M;

The following shows a list of the commands and the results on my system.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram


10:51:58 By your command, human>host dir temp.ora
 Volume in drive C has no label.
 Volume Serial Number is 18A8-A11B

 Directory of C:\Oracle\oradata\sneakers

03/31/2003  10:51       110,108,672 TEMP.ORA
               1 File(s)     110,108,672 bytes
               0 Dir(s)  15,041,400,832 bytes free

10:50:44 By your command, human>alter database tempfile 'c:\oracle\oradata\sneakers\temp.ora' drop;

Database altered.

Elapsed: 00:00:00.07
10:51:21 By your command, human>host erase temp.ora

10:51:29 By your command, human>alter tablespace temp add tempfile 'c:\oracle\oradata\sneakers\temp.ora' size 10M;

Tablespace altered.

Elapsed: 00:00:00.06
10:51:58 By your command, human>host dir temp.ora
 Volume in drive C has no label.
 Volume Serial Number is 18A8-A11B

 Directory of C:\Oracle\oradata\sneakers

03/31/2003  10:51        10,493,952 TEMP.ORA
               1 File(s)     10,493,952 bytes
               0 Dir(s)  15,030,906,880 bytes free

10:52:06 By your command, human>
0
bmoshierCommented:
P.S.  Sorry I forgot to have the datafile TEMP.ORA auto extend.

alter database tempfile 'c:\oracle\oradata\sneakers\temp.ora' autoextend on next 10M;

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram
0
tsejahteraAuthor Commented:
tq, i  have the chance to restart the database and the temp
tablespace was zerorized.

tq
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.