space management problem
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: space management problem

  1. #1
    Join Date
    Apr 2001
    Posts
    103

    Question

    Hello all,
    I have a tablespace for index with 25G.
    This tablespace has got four files. That is G:\indx\indx1.dbf, indx2.dbf, etc ...
    I ran a query to see the free space i have around 2G of free space dispersed in three different files (indx1.dbf 731Mb, indx2.dbf 780mb, indx3.dbf 711mb).
    But this table space is fragmented like anything.
    So i did a coalesce to defragment it.
    When i run again the query to see the fragmented tablespaces i always see my index tablespace.
    I tried to create an index in this tablespace it says it dosent have much place to extend.
    Can some one explain me how oracle manages the tablespace.
    Thanks
    Naeem
    Naeem

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    coalesce will not completely defragment, it will join adjacent chunks of free space.

    just because you have 2GB of free space, doesn't mean it is in one big piece. You need to find out how big your pieces are. If a segment needs to exetend by say 10Mb and a 10mb piece of freespace cannot be found then you will get error.

    To overcome, defragment via export/import.
    change tablespace to LMT.

    This script will help you find out how much freespace, how many pieces and biggest/smallest chunk.

    prompt Listing Available Free Space In Tablespaces/Fragmentation
    prompt ---------------------------------------------------------
    pause



    select substr(f.tablespace_name,1,40) "Tablespace Name",
    to_char(d.bytes/1024/1024,'9999.99')||' MB' Total,
    to_char(sum(f.bytes/1024/1024),'9999.99')||' MB' Free,
    to_char(round(max(f.bytes/1024/1024),2),'9999.99')||' MB' Biggest_Chunk,
    to_char(round(min(f.bytes/1024/1024),2),'9999.99')||' MB' Smallest_Chunk,
    count(f.bytes/1024/1024) Pieces
    from dba_free_space f,
    dba_data_files d
    where f.file_id = d.file_id
    group by f.tablespace_name,
    to_char(d.bytes/1024/1024,'9999.99');
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Apr 2001
    Posts
    103
    Thanks Suresh,
    I have Biggest pices is 703.50MB and my smallest on is 10.88MB.
    and i have 6 pieces.
    I am creating an index with an initial extend of 100MB.
    Since it has 703.50MB piece availabe, i don't understand why it is giving me an error.
    When I do export do i have to say compress=y.
    does this makes any difference?
    naeem
    Naeem

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    don't think you need to defragment with those stats.

    Check other index sizes on that table to determine size of index that will be created.

    what is your next extent size ?

    are you sure you are specifying correct tablespace to store index when creating ?
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Apr 2001
    Posts
    103
    My next extent size is 100MB
    I am sure that i am specifying correct tablespace to store index when creating, because i am getting the folowing error

    ORA-01652: unable to extend temp segment by 16 in tablespace INDX"

    I am checking the size of the other indexes.
    Thanks for your help.
    Naeem

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    To avoid fragmentation I recomand you to use localy managed tablespace with an unique extent size (CREATE TABLESPACE ...EXTENT MANAGEMENT LOCAL UNIFORM SIZE ???K).
    In addition to that you could create a TBS for small indexes with an exstent size of 512k and one for large indexes with an extent size of 20M (this is only an example).

    Instead of an export you could also rebuild the index to defragment it(ALTER INDEX idx_name REBUILD TABLESPACE tbs_name STORAGE(INITIAL 10M NEXT 10M)

    Could please give the exacte error number and text you get?

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    To me it seems that your index becomes very large.

    How many rows has your table?
    How many columns are you using for the index?
    What is the average length of these columns?
    Where do you specify the next extent size?
    Could you give the statment you are executing?

  8. #8
    Join Date
    Apr 2001
    Posts
    103
    Mike,
    here are the answers for your questions.

    How many rows has your table?
    90 565 373
    This table is partioned by range.

    How many columns are you using for the index?
    One column

    What is the average length of these columns?
    10b
    Where do you specify the next extent size?
    i don't understant your question

    Could you give the statment you are executing?
    ALTER INDEX inhis_hicod_hitxt REBUILD
    COMPUTE STATISTICS
    Naeem

  9. #9
    Join Date
    Mar 2002
    Posts
    534
    sorry i didn't see that you want to rebuild your index, I was thinking you want to create it.
    Could you give the number of blocks used by this index.
    You need to execute an
    ANALYZE INDEX inhis_hicod_hitxt VALIDATE STRUCTURE
    and then have a look at the INDEX_STATS view and get the number of leaf blocks in the B-Tree (LF_BLKS) and the number of branch blocks (BR_BLKS).


    Which is the default storage for this tablespace (view DBA_TABLESPACES: INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE) ?


    And one more question: in your first message you say that you have a tablespace with 25GB based on four files. Is 25GB correct isn't it 2.5GB?


    >>Where do you specify the next extent size?
    >i don't understant your question
    Your error message says that it can not allocate 16 blocks which means from my point of view that you are using extents of 16*block_size which is equal to 128k (if you are using 8k db blocks). With the statement below you would specifiy the extent size:
    ALTER INDEX idx_pk_dept
    REBUILD
    TABLESPACE idx
    STORAGE(INITIAL 100M NEXT 100M);

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