chk dba_free_space show enough freespace in INDEX ts but index pk complain cant exten
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: chk dba_free_space show enough freespace in INDEX ts but index pk complain cant exten

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456

    chk dba_free_space show enough freespace in INDEX ts but index pk complain cant exten

    hi guys,

    I got a funny situation here... I query my DBA_FREE_SPACE it shows enough freespace tablespace size of 600MB in my index table but when the user application try to perform some transaction it complain ORA-1654 and in the alert log it stated certain index pk cannot be extended....?? this is puzzling and i added another 300MB and the problem is solved??..wonder why?

    hv u guy encounter this prob? my platform is Solaris 2.6 with Oracle 8.1.7.4 ...

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you are using dictionary managed tablespaces, and haven't been careful to keep all extents of all segments exactly the same size in the TS's, then you might have fragmented free space.

    check the system view dba_free_space_coalesced to find out -- also start planning your switch to locally managed TS's with uniform extent sizes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi,

    if the tablespace is fragmented and you don't have
    enough contiguous free space to fit the extent size of the
    index, then you will get the error.

    you can try decreasing next extents of the index to smaller size.

    you can try coalescing the tablespace.
    (only if the fragmentation is of honeycomb type -
    that is the adjacent pieces free and large,
    but logically separated as different pieces,
    this will solve the problem).

  4. #4
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    hi slimdave,

    wondering if this mean i hv fragmented segment?

    this is my result:

    A)
    select tablespace_name,round(sum(total_bytes/1024/1024),1) MB from dba_free_space_coalesced group by tablespace_name;

    TABLESPACE_NAME MB
    ------------------------------ ----------
    PROD_INDX 1123.9


    B)
    SQL> select tablespace_name,round(sum(BYTES_COALESCED/1024/1024),1) MB from dba_free_space_coalesced group by tablespace_name;

    TABLESPACE_NAME MB
    ------------------------------ ----------
    PROD_INDX 1110.3

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