Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    11

    Unanswered: Vendor Message:ORA-01652: unable to extend temp segment

    Hi,

    I am getting the error "Vendor Message:ORA-01652: unable to extend temp segment DATAFILE" when I am trying to load the table. DATAFILE is not a temporary table space. I have about 15 GB of total freespace in this. When I am running the load I tried to monitor the table space usage. Neither the actual table space or the temporary tablespace were filled. Any inputs will be helpful

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Singapore
    Posts
    11

    Smile Re: Vendor Message:ORA-01652: unable to extend temp segment

    Oracle automatically releases temporary segment space. If ur temp tablespace show it's full then other option woulb be to bounce the oracle server. This will free up ur temp tablespace.

    Regards,

    Azhar.

    Originally posted by seb
    Hi,

    I am getting the error "Vendor Message:ORA-01652: unable to extend temp segment DATAFILE" when I am trying to load the table. DATAFILE is not a temporary table space. I have about 15 GB of total freespace in this. When I am running the load I tried to monitor the table space usage. Neither the actual table space or the temporary tablespace were filled. Any inputs will be helpful

    Thanks

  3. #3
    Join Date
    Sep 2003
    Posts
    11

    Re: Vendor Message:ORA-01652: unable to extend temp segment

    Originally posted by aekjobs
    Oracle automatically releases temporary segment space. If ur temp tablespace show it's full then other option woulb be to bounce the oracle server. This will free up ur temp tablespace.

    Regards,

    Azhar.
    Hi,

    Thank you for your response. In my case the temporary segment wasn't showing that it is full. The datafile also had lot of space while it is running. Is there anything else I should look into ?

    Thanks.

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: Vendor Message:ORA-01652: unable to extend temp segment

    Originally posted by seb
    Hi,

    I am getting the error "Vendor Message:ORA-01652: unable to extend temp segment DATAFILE" when I am trying to load the table. DATAFILE is not a temporary table space. I have about 15 GB of total freespace in this. When I am running the load I tried to monitor the table space usage. Neither the actual table space or the temporary tablespace were filled. Any inputs will be helpful

    Thanks
    Look in your alert log. There should be an error message of the form ORA-01652 ... It will contain a value that is the number of blocks that it want's to use and the name of the tablespace. The number in the error message is the number of contiguous blocks that Oracle needs to create the temp segment. Mulitply this number by db_block_size of the database to get the size of contiguous disk space that Oracle needs.

    Then, check dba_free_space for the tablespace listed in the error message and see if there is a chunk of free space for the temp segment to use. Even though there might be a lot of free space in the tablespace, it could be an enourmous number of small chunks of space. You must have a single chunk of space at least as big as Oracle is requesting for the temp segment to get created.

    HTH,
    Patrick

  5. #5
    Join Date
    Sep 2003
    Location
    Delhi
    Posts
    15

    Re: Vendor Message:ORA-01652: unable to extend temp segment

    Originally posted by seb
    Hi,

    I am getting the error "Vendor Message:ORA-01652: unable to extend temp segment DATAFILE" when I am trying to load the table. DATAFILE is not a temporary table space. I have about 15 GB of total freespace in this. When I am running the load I tried to monitor the table space usage. Neither the actual table space or the temporary tablespace were filled. Any inputs will be helpful

    Thanks
    Hi,
    The problem exists with your temporary tablespace.Oracle needs temporary tablespace for sorting of data...and the amount of tablespace you have defined is not enough for for the sorting operation to take place...You need to add a datafile to that particular tablespace to get over with the problem...

    ALTER TABLESPACE tbs_03
    ADD DATAFILE 'tbs_f04.dbf'
    SIZE 50K
    AUTOEXTEND ON
    NEXT 10K
    MAXSIZE 100K;

  6. #6
    Join Date
    Sep 2003
    Posts
    11

    reply

    Hi,

    Thank you for all of your responses. In my case looks like the situation is little more complicated. I have in my actual table space almost 15GB of free space which is 100 % of used space. In my index table space also same case. Temporary table space also has 10 GB of free space. I was watching while running the load neither of these table space was getting filled even 50 %. But failing with unable to extent error. Actually we have an application running on this database and if I try to recycle the application seems to improve the situation. The issue started only two days back before that we did not have this problem. I think even a simple querry is making the situation worst. In any case the %used is not more than 50% but is failing with unable to extend table space error. When I recylced the application I was able to run the load successfully once. But for the second time still the same behaviour. Any inputs is appreciated. I haven't tried recycling oracle since it is shared by other applications.

    Thanks.

Posting Permissions

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