Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1293
  • Last Modified:

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
0
tsejahtera
Asked:
tsejahtera
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now