Coalesce tablesapce
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Coalesce tablesapce

  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    45

    Coalesce tablesapce

    Hi,

    Is there any way to make multiple single free extent to a big extent. One of our tablespace has 162 free extents with different sizes (1MB, 5 MB etc..), totally it is around 1G with maximum extent 10 MB.

    Any one of the free extent is not being used, because all the segments belogs to that tablespace has 30+ MB as next extent.

    It was not done when I coalesced the tablespace, it seems all free extents are not near to each other.



    Thanks,

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    You can avoid this by always specifying extents of same size. Always specify pctincrease = 0 and initial = next. Anyways, To fix this, if you really want to, use export/import(I can think of only this now, maybe someone has any other idea). Like you said, Coaleace wont fix free extents scattered away from each other.

    Thanks.
    -- Dilip

  3. #3
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97

    Lightbulb

    Hi,

    Coalesce only union free segments, if possible, but dont free space.
    To defrag tablespace, use alter table MOVE or alter index REBUILD.
    If do not space in your tablespace, create new tablespace and move all objects.
    If do not space in disk, export, drop and import this solution.
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi Which version of oracle r u using, if its 8i then why dont u create a new local managed tablespace with uniform extents size and move all ur objects to that tablespaces as oracle will take care of
    extents management.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    If your ORACLE version is pre 8i then the process will be somewhat more complicated/ time consuming. I.e. You'll have to create new tablespaces and perform either of the following.

    Option a). Create new temporary table from old.
    CREATE TABLE temp AS SELECT * FROM emp;
    DROP TABLE emp;
    CREATE TABLE EMP AS SELECT * FROM EMP
    STORAGE(...);
    DROP TABLE TEMP
    Great GRANTS
    Recreate indexes

    etc, etc.

    A long process.

    Option b).

    Export Schema (Scott for example)
    Recreate Schema objects AND indexes with correct storage sizes. The time consuming process here is recreating the DDL of the objects should you not already have it.
    Import Schema back in

    Ta da...
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    Instead of moving all the objects to different tablespace, one temporary approach to solve this is run this sql script which will give the location of segments at the datafile level. Pick up the last object in this result and rebuild in the same tablespace, so that the space unused from the starting of datafile will be now reused. For permamnet solution transfer the data dictionary tablespace to an LMT.

    you should give tablespace name as input for this query.

    select file_id, block_id, blocks,
    owner||'.'||segment_name "Name"
    from sys.dba_extents
    where tablespace_name =
    UNION
    select file_id, block_id, blocks,
    'Free'
    from sys.dba_free_space
    where tablespace_name =
    order by 1,2,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