?
Solved

Temp tablespace full

Posted on 2011-09-30
10
Medium Priority
?
1,073 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 78

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 78

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
Industry Leaders: 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 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 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