DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Coalesce?

  1. #1
    Join Date
    Nov 2000
    Posts
    205
    Hi,

    I check out my tablespace usage in OEM and see that the datafiles are like 5M used and the datafile is like 2GB. I have resized many of the others similarly using the resize command. There are a few however that say that the amount in the resize command is much bigger? I refresh my display and see no such thing. Does this mean that objects are scattered in different blocks and I need to coalesce?

    Also if people are online etc and using, will coallesce cause any problems?

    Please help,
    thanks
    Nirasha

    Statement is Alter tablespace xxx coalesce, right?

  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    The error is due to fragmentation of free extents. Coalesce does not cause any problems. Syntax is correct.

  3. #3
    Join Date
    Nov 2000
    Posts
    205
    Thanks for the reply. Tried it, and coalesced, but still cannot perform it. Tried taking a look at the Tabelspace Map in OEM and saw 'selected' used and free (looks like a bad mess).. what does selected mean? and does this mean my only option is export/import.

    Thanks again,
    Nirasha

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    If your tablespace map is not evenly spread in OEM, thats fragmented.

    How about using reorg ? Else redefine storage clasuses of initial and next extents with initial and next extents as same and pctincrease 0 and add 2 oracle blocks for datafile as over head.

    Make sure your extent_size is multiple of multi_block_red_count.

    That should take care of fragmentation problem. See the SAFE tech. white paper link posted in somany threads from couple of weeks.

    Make sure to use comepress=n option while exporting.

    [Edited by sreddy on 01-30-2001 at 06:42 PM]

  5. #5
    Join Date
    Nov 2000
    Posts
    205
    Thanks, though not looking forward to a reorg without fancy tools available. What if the system tablespace has some fragmentation?

    Thanks,
    Nirasha

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Don't worry about the system tablespace. Oracle recommends not to touch it. And even if you want, you can't change the storage clause (other than resizing the tablespace/datafile) in simple way. You have to do it hard way by editing sql.bsq script. Oracle don not encourage dba's to change it without their suppot or whatever.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    For Nirasha...

    What are the obejcs you have in that tablesapce ?? If those are Indexes you can simply rebuild them with proper storage parameters. If those are tables you can do a export/import to reorganise the tablesapce.

    Hope it helps..

    Sanjay

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if they are tables and you are using 8i try

    alter table table_name move tablespace_name
    then resize the datafile
    and finally move the tables back with above command

  9. #9
    Join Date
    Nov 2000
    Posts
    205
    Thanks you guys for all that help. I just read recently about the feature of move tablespace, and I was curious whether there is anything I need to be aware of before doing this .. like people not touch the table etc???

    Thanks
    Nirasha
    PS Does anyone know if this email notification in this forum will ever be reactivated??

  10. #10
    Join Date
    Nov 2000
    Posts
    205
    Still wondering about whether I can move tablespace if anyone is working (selecting/updating it etc)?? Or is there some mode for it to be in???

    Nirasha

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