Tablespace coalesce no effact
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Tablespace coalesce no effact

  1. #1
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    Our tablespace has lots of delete and insert and updates,
    When I am trying tablespace coalesce it does not create any space in datafiles, it shows tablespace altered but there is no effact on space.
    I deleted some users, many tables still data files shows full.
    removeing object from tablespace does not create any space.
    my disk is full please help



  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    The reason you were not gaining disk space is your tablespace (datafiles) has more bubble and honeycomb.
    Do not create objects with different initial and next sizes in one tablespace.

    Use "ALTER TABLE tablename MOVE TABLESPACE tablespacename STORAGE ..." to gain spaces within the tablespace.
    Also use "ALTER INDEX indexname REBUILD" when there is low level database activity.

  4. #4
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    Let me be more specific. My tablespace is full.
    I deleted many users schema which contains lots of data.
    now since I deleted many objects from tablespaces it should free some space in tablespace. How can I create free space ?
    where that space has gone ? (of deleted objects)

    I am just confused becuase by coalesce it does not create free space.

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Coalesce tablespace can't create no free 1 bytes in tablespaces.
    It only union neast free extents into 1 lager extent, and that's it.
    U should check dba_free_extents : does ur tablespace has fee space or not?
    and only then, if u have free extents, u can coalesce this tablespace.

  6. #6
    Join Date
    Feb 2001
    Posts
    20

    Thumbs up

    I think u need to resize your datafile. Here is the syntax

    ALTER DATABASE
    DATAFILE <'datafile'>
    RESIZE

    There are some method we can find out the highest water mark in the datafile but here u can use guess and trial method by taking a small size and increasing and little bit if it fails.

    Best of luck

  7. #7
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by Shestakov
    Coalesce tablespace can't create no free 1 bytes in tablespaces.
    It only union neast free extents into 1 lager extent, and that's it.
    U should check dba_free_extents : does ur tablespace has fee space or not?
    and only then, if u have free extents, u can coalesce this tablespace.
    I think the view you point to is DBA_FREE_SPACE

    Cheers

    Angel

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