?
Solved

Temp tablespace full

Posted on 2011-09-30
10
Medium Priority
?
1,062 Views
Last Modified: 2012-05-12
I have already three files in Temp Tablespace and all are set to autoextend, but I am getting error
Temp Tbalespace is full. Please help me guys.
0
Comment
Question by:nirvairghuman
[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
  • 2
  • +3
10 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 224 total points
ID: 36891789
You need to track down what queries are causing all the sorting.

If they are legitimate, then you just need to add more space.

There are a lot of queries out there to track down what is using temp space.

I'm on mobile right now and cannot provide a link right now.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 224 total points
ID: 36891791
You need to track down what queries are causing all the sorting.

If they are legitimate, then you just need to add more space.

There are a lot of queries out there to track down what is using temp space.

I'm on mobile right now and cannot provide a link.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 224 total points
ID: 36892210
You can generate AWR reports (if you have license) and see which query took more resources.
If you know which job failed or query, then provide us table size details and query along with Temp tablespace definition.

Right now we can only suggest to increase temp tablespace size.

It happened sometime that more than  one job (need more memory) run simultaniously and caused this issue. Therefore you need to find if there is increase in data or removal of some temp file happened etc.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Assisted Solution

by:jsr_dba
jsr_dba earned 112 total points
ID: 36892511
1)  You can  use the scripts  below.  Put then in one file and run it.  this will show you the user and the query consuming the temp tablespace.

2)  If  no jobs failed and if  all 3  temp files are not used  then don't worry about the message. That is not a problem.  

3)  Make sure if your filesystem has enough space to autoextend.

=====================  script ================

ttitle left '   '
set linesize 132
set pagesize 60
select    
 CURRENT_USERS    ,      
 TOTAL_EXTENTS   ,      
 TOTAL_BLOCKS   ,        
 USED_EXTENTS  ,        
 USED_BLOCKS   ,        
 FREE_EXTENTS  ,        
 FREE_BLOCKS    
from v$sort_Segment
/      
column tablespace format a15
column osuser format a10 truncate
column username format a12
TTITLE LEFT 'SORT USAGE:'
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
/
select sql_text
from v$sql
where  hash_value in (  SELECT   sql_hash_value
                   FROM     v$session a,v$sort_usage b
                   WHERE    a.saddr = b.session_addr
                )
and sql_text  not like '%hash_value in%'
/
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 332 total points
ID: 36895969
The duty of SMON is to free unused blocks in the temp and other tablespaces. For some reason SMON do not start periodically and do not frees the unused blocks in the temporary tablespace.

There are some workarounds.

Initially I have redirected the temporary file activity to newly created tablespaces:

CREATE
    TEMPORARY TABLESPACE "TEMP1" TEMPFILE
    'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP02.DBF' SIZE 200M
    REUSE AUTOEXTEND
    ON NEXT  640K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Nowadays I prefer to activate SMON and it cleans the temporary tablespaces. There is a special "trace events" that orders SMON to clean particular tablespace.

0
 

Author Comment

by:nirvairghuman
ID: 36903154
Hi schwertner, can You  please give more details how to activate SMON to cleans Temp Tablespace.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 332 total points
ID: 36905157
I have the procedure to do this in my office (tomorrow will be there!).

I the net I find the explanation which possibly will work:

Find out the tablespace number (ts#) which contains temporary segments:
SQL> select ts# from sys.ts$ where name = 'tablespace name';

Suppose it comes out to be 10, use the following command to cleanup temporary segments:

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 11';

level is ts#+1 i.e 10+1=11 in this case.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 224 total points
ID: 36905408
You can use the following to find create an alert message system that whenever there temp table is almost 90% fill, DBA can take appropriate action.

SELECT s.inst_id, S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
and t.INST_ID = s.inst_id
and s.INST_ID = p.inst_id
AND T.tablespace = TBS.tablespace_name
GROUP BY s.inst_id, S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 108 total points
ID: 36905942
Often a full temp tablespace does *NOT* mean you should make the temp tablespace larger!  Often this means a poorly-written query is running.  You may need to identify and fix (if possible) the poorly-written query.  This could be a "where" clause join condition that is missing in a multi-table query, or it could be an "order by" or "group by" clause on a non-indexed column.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 332 total points
ID: 36911911
Oracle has big troubles with the temporary tablespaces, because it is shared resource.
In order to command better the temps a good practice is to create for every application on the instance different temporary tablespace.
About the full temp. tablespaces: it is fact that they rarely are cleaned automatically. there are many schools (and rumors) about this. Some of the experts say that this is normal and oracle internally uses the nonused blocks. The main problem is that it is hard task to distinguish between the two main events:
1. Event #1: Temp. is full, but Oracle doesn't complain. possibly it either uses nonused blocks or doesn't work with the temp. tbs.

2. Event #2; the tracking system (OVO, legato,...) founds in alert.log the mesage " Temporary segment TEMP can not be increased with 1024 bytes". This really means that the temp. is narrow, but ... the statement has failed.  Bad thing... especially on Production.

There is a special situation in which you have to sit on the station and to measure the load of the temp. This is when you do IMPORT. When it comes to index creation Oracle begins to use intensivelly the temp. And if the temp gets full often some of the indexes are not created. So if you do not command the size and the occupation of the temp you will be forced to recreate the failed indexes in the import. this happens very often when you have customers that regularly do refreshes of production to the test.

So you have to be prepared to command the temp in very flexible maner ...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

770 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