Solved

Oracle 11g: how to fix Temp File space exceeded

Posted on 2014-09-23
17
642 Views
Last Modified: 2014-09-23
I'm not a DBA, I only play one occassionally.  I know when to stop.  We're using TOAD 12.
This is one of those situations.
We're running Oracle 11 on Windows Server 2008 R2.
We have a tablespace called Warehouse1that has about 39G Used, 24G free initial extent = 64, nextExt = 0, Min Extents = 1and max is a very large number (2B).
It has a WTEMPDF Tempfile that has 64G Total.
When I run my awesome query, it fills up the 64G and then produces a ORA-1652 error. It sounds like its maxing the Temp space and not expanding it.
If my thinking is correct, I need to somehow expand my tempfile or create another one.

Any help would be appreciated.


I dont know if any of this is
0
Comment
Question by:GNOVAK
  • 7
  • 6
  • 4
17 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 200 total points
ID: 40339508
you can add another temp file

I'm assuming Warehouse1 is your temp tablespace that is being filled

ALTER TABLESPACE Warehouse1
  ADD TEMPFILE '/your/path/goes/here/your_temp_file_name.dbf'
  SIZE 16G  
  AUTOEXTEND ON
  NEXT 128M
  MAXSIZE 64G;

Change path sizes as needed

If you are using Oracle Managed Files, then you can (and should) leave the path and name out of the command.


ALTER TABLESPACE Warehouse1
  ADD TEMPFILE
  SIZE 16G  
  AUTOEXTEND ON
  NEXT 128M
  MAXSIZE 64G;
0
 

Author Comment

by:GNOVAK
ID: 40339513
Is there any caveats with changing the MAXSIZE ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40339514
I would probably rezie the temp datafile over adding a new one:
ALTER DATABASE TEMPFILE '/your/path/goes/here/your_temp_file_name.dbf' RESIZE 128G;

Allowing a temp datafile to resize automatically can be dangerous.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40339517
alternately - check the query.  Does it really need all of that temp?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40339525
>>Is there any caveats with changing the MAXSIZE ?

You can do that.  An automatic file extend is expensive.  Letting TEMP grow unchecked can fill up a disk quick.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40339527
Using multiple tempfiles instead of a single big one can help parallel queries.  Especially if you're on rac, in which case you might want to look at using a tablespace group
0
 

Author Comment

by:GNOVAK
ID: 40339535
When I tried to add a filespace using:
ALTER TABLESPACE Warehouse1
   ADD TEMPFILE 'F:/ORADATA/WAREHOUSE/DATAFILE/WTEMPDF2.dbf'
   SIZE 16G  
   AUTOEXTEND ON
   NEXT 128M
   MAXSIZE 64G;

I received an ORA-01268: invalid TEMPFILE clause for alter of permanent TABLESPACE.
any idea on what I did wrong?
0
 

Author Comment

by:GNOVAK
ID: 40339543
Not on a rac
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.

 
LVL 73

Expert Comment

by:sdstuber
ID: 40339545
Warehouse1 isn't your temp tablespace.


change Warehouse1 to whatever tablespace you are using.  Most systems simply call it "TEMP"

Not on rac - ok, multiple files are still helpful for parallel processing  - or even just having multiple sessions doing unrelated work.  Less contention on a single resource.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 40339547
You need to find the temporary tablespace name and add the datafile to that.

select tablespace_name from dba_temp_files;
0
 

Author Comment

by:GNOVAK
ID: 40339572
The main tablespace is WAREHOUSE1 with a file name of WAREHOUSE1.
The Tempfile is Tablespace WTEMP, Filename is WTEMPDF.
Am I adding the Tablespace to the Tempfile Tablespace or am I "adding" a tempfile to WAREHOUSE1?


ALTER TABLESPACE Warehouse1 .....
or
ALTER TABLESPACE WTEMP .....
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 40339578
You add temp datafiles to temporary tablespaces.
0
 

Author Comment

by:GNOVAK
ID: 40339602
Sorry for my lack of complete understanding....
When I run it with
ALTER TABLESPACE WTEMP
   ADD TEMPFILE 'F:/ORADATA/WAREHOUSE/DATAFILE/WTEMPDF2.dbf'
   SIZE 16G  
   AUTOEXTEND ON
   NEXT 128M
   MAXSIZE 64G;

I get ORA-03206: maximum file size of (4194304) blocks in AUTOEXEND clause is out of range
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 40339620
Looks like 64Gig is too large for your system and configuration.  Make is smaller.  Maybe 32Gig.

also, 128Meg for the next allocation is a little small.  Remember, autoextending is expensive and these operations should be minimized.

I would look at 1 Gig per allocation.
0
 

Author Comment

by:GNOVAK
ID: 40339665
ok - that worked.
How would I have a Temp file of 64G (actually listed as 67,108,832KB) and not be able to add one?
If I change the statement to
ALTER TABLESPACE WTEMP
   ADD TEMPFILE 'F:/ORADATA/WAREHOUSE/DATAFILE/WTEMPDF2'
   SIZE 67108832K
   AUTOEXTEND ON
   NEXT 100M
   MAXSIZE 67108832K;

It works... Any idea on why?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 300 total points
ID: 40339684
Likely some funny math based on the blocksize of the database.

The error explanation can be found here:
http://ora-03206.ora-code.com/

The max filesize for Oracle is based on the block size of the database.  The docs have all this somewhere.  If you are 1 byte off, it complains.


You still have a small NEXT size.
0
 

Author Closing Comment

by:GNOVAK
ID: 40339747
Thanks Everyone!
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…

743 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

16 Experts available now in Live!

Get 1:1 Help Now