If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Oracle > ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jan 2012
Posts: 2
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

how can i fix this? would increasing the maxsize work?

alter database TEMPFILE 'E:\QuantumDB\Database\CCTL\TEMP01_01.DBF' autoextend on maxsize 10000M

I'm using Oracle 11g
Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,616
what is largest OS datafile for your version of Windows?

what is OS name, version & Edition?
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Jan 2012
Posts: 2
its on Windows Server 2008 64 bit. not sure i understand the first quesion.
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Mar 2010
Location: Vienna, Austria
Posts: 149

before discussing the maximum datafile size, you should look at the query, that needed so much temp space.

Sometimes it's reasonable to expand, but in my experience 98% of these queries are sort operations in sort/merge joins doing cartesian products, because someone forgot a join predicate in a large query ...

If it's not a rogue query, here is the answer to your question:

the maximum size of a datafile is determined by 3 factors:

1.) maximum file size of Operating System (if its's NTFS on your 64bit Windows box it's 16TB - 64KB)

2.) whether your TEMP - tablespace is a BIGFILE or SMALLFILE Tablespace (if it's a standart implementation it's a SMALLFILE TS)

3.) the block - size of the tablespace (2K - 32K, default is 8K but 16K is quite common)

The maximum size of a SMALLFILE datafile:

2^22 * <blocksize_in_bytes>

which is 32 G for 8K blocksize and 64G for 16K blocksize

if it's a BIGFILE tablespace:

2^32 * <blocksize_in_bytes>

which translates to 32TB for 8K tablespaces and 64TB for 16K blockzize datafiles (but remember the NTFS limit of 16TB, and be aware of the limitation to 1 BIGFILE datafile per tablespace).

You may want to read up on BIGFILE tablespaces, and Oracle Database Limits
"There is always an easy solution to every problem - neat, plausible, and wrong."
-- H.L. Mencken
Reply With Quote

oracle 11g

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On