Solved

Oracle 11g: how to fix Temp File space exceeded

Posted on 2014-09-23
17
791 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
[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
  • 7
  • 6
  • 4
17 Comments
 
LVL 74

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 77

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 74

Expert Comment

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

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 74

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
 
LVL 74

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 77

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 77

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 77

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 77

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

623 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