?
Solved

TEMP SEGMENT FULL

Posted on 2003-03-27
5
Medium Priority
?
1,291 Views
Last Modified: 2013-12-11
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
0
Comment
Question by:tsejahtera
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
bmoshier earned 120 total points
ID: 8218604
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
 

Author Comment

by:tsejahtera
ID: 8235749
when i try to resize , i received an erorr
saying that i cannot resize.
my temp now is 20GB size.
0
 
LVL 4

Expert Comment

by:bmoshier
ID: 8239727
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
 
LVL 4

Expert Comment

by:bmoshier
ID: 8239838
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
 

Author Comment

by:tsejahtera
ID: 8280802
tq, i  have the chance to restart the database and the temp
tablespace was zerorized.

tq
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question