Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Dublin, Ireland
    Posts
    13

    Unanswered: alter tablespace [tablespacename] coalesce

    Hi,

    I'm using Oracle 8i and I have a tablespace with over fifteen free sapce segments in it.

    I tried running alter tablespace [tablespacename] coalesce in SQL*Plus where [tablespacename] is the name of the fragmented tablespace.

    SQL*Plus returned immediately with Tablespace altered but it wasn't.

    Does anyone know what I'm doing wrong here?

    Is this possible in 8i? Shoulod I have started the database in a restricted mode? Do I need to take that tablespace offline first?

    What if I make SQL*Plus a cup of tea, do you think it would work for me then?

    Any and all help gratefully received.

    Cheers,

    Kev

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155

    Re: alter tablespace [tablespacename] coalesce

    I found that Oracle prefers CocaCola better than tea...

    alter tablespace [tablespace_name] coalesce;

    This will compress the free extents that are on adjoining data blocks.

    If you have a free extent on block 1 and a table segment on block 2 and
    a free extent on block 3... Coalesce will do NOTHING to this ...

    You need to rebuild the objects that are inbetween your free space
    by either rebuilding ( alter index ... rebuild ... ) ... (Create table ... as select ... anothertablespacename) ...
    OR
    export information from the tablespace in question, drop objects,
    coalesce tablespace ... import objects ...

  3. #3
    Join Date
    Aug 2002
    Location
    Dublin, Ireland
    Posts
    13
    Thanks gbrabham.

    That's really cleared things up for me. Why couldn't Oracle have put it that simply?

    I've begun the torturous process of exporting, dropping, coalescing, creating and importing.

    If ever there was a need for a DBA tool, then this is it. Any Oracle developers out there - get to work on this and make yourselves some money.

    Oh, you're all rolling in it already. Never mind, then...

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I may be wrong here, but can't you do an alter table move tablespace - to the same tablespace which effects an coalesce of the table? Similar to performing an index rebuild?

    I haven't tried it, just seem to remember it from somewhere.

    ALTER TABLE <tablename> MOVE TABLESPACE <tsname>

    If, not moving out of and back into your tablespace would achieve it.

    HTH
    Bill

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One thing to mention is when you do a table move remember to rebuild the indexes as they become invalid. Apart from that the alter table move command is very useful indeed and also quite quick.

    Here's a script I use to rebuild a set of partition tablespaces with new storage options. It works by moving all the tables and indexes to the user tablespace (so make sure its big enough or replace the USERS tablespace in the script), then coalesing the tablespaces and then moving the tables and indexes back and analysing them.

    Replace PDATA_2001% with the tablespace name(s) which hold the tables you need to rebuild. NOTE the script assumes the index tablespaces are called PINDX so alter the replace statement to put in the name of the tablespaces which hold the associated indexes. Either remove the storage options or replace them with a new set of options.
    It works by moving the tablespace

    select '/* ' || partition_name ||'.1'||table_owner ||'.'||table_name ||' 1 */ ALTER TABLE '||
    table_owner || '.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE USERS PCTUSED 70 PCTFREE 27 STORAGE(INITIAL 5M NEXT 5M) NOLOGGING ;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    union
    select distinct '/* ' || partition_name ||'.2'||' 2 */ ALTER TABLESPACE '||tablespace_name|| ' coalesce;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    union
    select '/* ' || partition_name ||'.3'||table_owner ||'.'||table_name ||' 3 */ ALTER TABLE '||
    table_owner || '.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE '||tablespace_name ||' PCTUSED 70 PCTFREE 27 STORAGE(INITIAL 5M NEXT 5M) NOLOGGING ;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    union
    select '/* ' || partition_name ||'.4'||table_owner ||'.'||table_name ||' 4 */ ALTER INDEX '||
    table_owner || '.'||table_name||'_PK REBUILD PARTITION '||partition_name||' TABLESPACE USERS PCTFREE 10 STORAGE(INITIAL 5M NEXT 5M) NOLOGGING ;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    union
    select distinct '/* ' || partition_name ||'.5'||' 5 */ ALTER TABLESPACE '||replace(tablespace_name,'PDATA','PINDX')|| ' coalesce;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    union
    select '/* ' || partition_name ||'.6'||table_owner ||'.'||table_name ||' 6 */ ALTER INDEX '||
    table_owner || '.'||table_name||'_PK REBUILD PARTITION '||partition_name||' TABLESPACE '|| replace(tablespace_name,'PDATA','PINDX')||' PCTFREE 10 STORAGE(INITIAL 5M NEXT 5M) NOLOGGING ;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    union
    select '/* ' || partition_name ||'.7'||table_owner ||'.'||table_name ||' 7 */ Analyze table '||table_owner||'.'||table_name||' partition('||partition_name||') estimate statistics sample 5 percent;'
    from dba_tab_partitions
    where upper(tablespace_name) like 'PDATA_2001%'
    order by 1


    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •