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
GNOVAKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.