Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

Oracle 11g: how to fix Temp File space exceeded

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
GNOVAK
Asked:
GNOVAK
  • 7
  • 6
  • 4
5 Solutions
 
sdstuberCommented:
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
 
GNOVAKAuthor Commented:
Is there any caveats with changing the MAXSIZE ?
0
 
slightwv (䄆 Netminder) Commented:
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.

 
sdstuberCommented:
alternately - check the query.  Does it really need all of that temp?
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
sdstuberCommented:
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
 
GNOVAKAuthor Commented:
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
 
GNOVAKAuthor Commented:
Not on a rac
0
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
You need to find the temporary tablespace name and add the datafile to that.

select tablespace_name from dba_temp_files;
0
 
GNOVAKAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
You add temp datafiles to temporary tablespaces.
0
 
GNOVAKAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
GNOVAKAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
GNOVAKAuthor Commented:
Thanks Everyone!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now