What does this mean in Tablespace?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: What does this mean in Tablespace?

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    hi,

    I am trying to create an index on a table with about 40Mil records. I am creating it in a seperate tablespace called
    partition_idx .

    After nearly 2 hours, I get this error

    ERROR at line 2:
    ORA-01652: unable to extend temp segment by 3200 in tablespace PARTITION_IDX

    As per the oracle document, I have to add another datafile.

    1) Does adding data file will suffice.
    2) What does 'Temp segment' in the erro mean.
    3) Can I have a datafile with AUTOEXTEND ON, SO I NEVER HAVE THIS PROBLEM


    Please advice
    Badrinath

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    1. you don't (necessarily) have to add another datafile. If the OS does not have any filesize limitatiions, you can simply resize it to a larger size.
    2. index creation requires sorting and therefore a Temp ts is used for the sorting.
    3. Sure, like I said, make sure there are not filesize limitations at the OS level.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    badrinathn

    look your previous thread about this, your question has been fully answered

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    Check what is the temporary tablespace of ur user , it should be seperate from index/data tablespace.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    this has nothing to do with temp ts, somehow oracle still creates temp segments in the tablespace where you store data, in this case PARTITION_IDX

    This has happened to me several times

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Pandu/Halo/GPS

    Here my confusion is about '....segment by 3200'. In my case the next is 25M. In that case the next extent of 25M should be allocated. If it fails , should n't it give me
    unable to extend by 25M

    Clarify

    Badrinath

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    because you dont have 25MB contiguos freespace

    you only have 26211200 bytes free

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by badrinathn
    Pandu/Halo/GPS

    Here my confusion is about '....segment by 3200'. In my case the next is 25M. In that case the next extent of 25M should be allocated. If it fails , should n't it give me
    unable to extend by 25M

    Clarify

    Badrinath
    ORA message is talking about DB blocks, not KB or MB. Your db_block_size is obviously 8K, multiply this by 3200 blocks and you'll get exactly 25MB.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width