Solved

Temp tablespace full

Posted on 2011-09-30
10
1,023 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 76

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 76

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
 
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 47

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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 34

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now