Solved

Temp tablespace full

Posted on 2011-09-30
10
1,049 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
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 56 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 56 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 56 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
Independent Software Vendors: 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!

 
LVL 1

Assisted Solution

by:jsr_dba
jsr_dba earned 28 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 83 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 83 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 56 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 27 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 83 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

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

Suggested Solutions

Title # Comments Views Activity
Oracle encryption 12 59
capture vmstat info and insert it into an oracle table 31 57
update using pipeline function 3 32
minium over 4 numeric columns for each row in oracle 2 36
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

761 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