Unable to extend tablespace.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Unable to extend tablespace.

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    57
    Folks,

    We have some shedulars to load data in to tables in daily basis . Some times we get below error though we have enough space in tablespace and this cause loader failure .

    ORA-1653: unable to extend table RBA.DT_TABLE by 12800 in tablespace RBA_DATA .

    In the above case we have 400 MB freespace in RBA_DATA
    tablespace , still we get the error .

    Is there any way to monitor or predict this one or two days in advance so that we can take care about the situation .

    thanks


  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I guess your tablespace is fragmented. It might have free space but can't find the extent size(chunk size of 12800) its trying to allocate. You have to reorg or add more space/datafiles.
    Reddy,Sam

  3. #3
    Join Date
    Dec 1999
    Location
    Alpharetta, GA, US
    Posts
    192
    make the table & tablespace pctincrease to 0.


    Good Luck.
    Chan
    OCP7.3/8.0/8i/9i
    Sun Certified Sys. Admin

  4. #4
    Join Date
    Feb 2001
    Posts
    389
    You may have free space but do u have contiguous space , that is contiguous space enough to hold next extent (12800 in your case).
    The space may be highly fragmented.
    Try tablespace coalescing.
    Find out the total number of extents with max free space size is > next extent , if it is less than 5 , need to work on tablespace.

    Take Care
    GP

  5. #5
    Join Date
    Feb 2001
    Posts
    15
    Hi,
    Use the following methods.

    1) ALTER TABLESPACE <name> COALESCE;

    2) ALTER DATABASE DATAFILE 'filename with path' AUTOEXTEND ON;


    Regards,
    Yajman

  6. #6
    Join Date
    Jun 2000
    Location
    san francisco,ca,usa
    Posts
    10
    run the following query :
    you will see the largest available extent in tablespace and no.of extents it is fragmented into:

    select tablespace_name,sum(bytes) "Free bytes", max(bytes) "Largest Extent",count(block_id) "# extents" from dba_free_space
    group by tablespace_name;

    Hope it helps you.

    -Syed

  7. #7
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Be carefull while coelsing as it makes the next extent as the size of the whole tablesize.
    Thanks
    Kishore Kumar

  8. #8
    Join Date
    Oct 2000
    Posts
    57


    Thanks to all

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