Results 1 to 4 of 4
  1. #1
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,629
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    its on Windows Server 2008 64 bit. not sure i understand the first quesion.

  4. #4
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    eltorr0,

    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •