Fragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Fragmentation

  1. #1
    Join Date
    Jul 2001
    Location
    Milano
    Posts
    22

    Unhappy Fragmentation

    Hi,
    i have a problem on fragmentation of tablespace. I want to know why Oracle don't use the subsequent (contiguos) free-space, but skip this space and use another space. For example: i submit this query:

    select
    2 'free space' owner /*"Proprietario" dello spazio libero*/
    3 ,' ' object /*Nome oggetto vuoto*/
    4 ,file_id /*ID file per l'intestazione di estensione*/
    5 ,block_id /*ID blocco per l'intestazione di blocco*/
    6 ,blocks /*lunghezza dell'estensione, in blocchi*/
    7 from DBA_FREE_SPACE
    8 where tablespace_name ='REPORTER'
    9 union
    10 select
    11 substr(owner,1,10) /*Nome proprietario*/
    12 ,substr(segment_name,1,14) /*Nome segmento*/
    13 ,file_id /*ID file per l'intestazione di estensione*/
    14 ,block_id /*ID blocco per l'intestazione di blocco*/
    15 ,blocks /*lunghezza dell'estensione, in blocchi*/
    16 from DBA_EXTENTS
    17 where tablespace_name ='REPORTER'
    18 and owner = 'OPENVIEW'
    19 order by 3,4
    20 /

    The output is:

    OWNER OBJECT Id BLOCK_ID BLOCKS

    OPENVIEW APPLICATION 7 5122 250
    free_space 7 5372 262
    OPENVIEW HP 7 5634 45

    Why the free-space from blockId 5372 to 5634 was unused for table HP?

    Thank's in advance,
    MaX

  2. #2
    Join Date
    Nov 2002
    Posts
    170
    My Immediate thought is:

    Your first datafile didn't have the next extent available for the table and grabbed the next extent from the second datafile which was added(starting at block id 5634). The no of blocks in the first datafile still remained unused. There could be other possible reasons too.

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