Re-organise segment to eliminate chaining/migration
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Re-organise segment to eliminate chaining/migration

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Friends..

    I have used OEM Tablespace Map and noticed that one of my segments has excessive chasining/migration. This can be resolved through OEM by using the re-organisation wizard.

    I am wondering if there is an equivalent way of eliminating this through sqlplus instead of using OEM. I have always thought that the way to do this through sqlplus would be to :

    create a copy of the table which has row migration Table A to Table B.
    copy migrated rows to new table from Table A to Table B
    delete migrated rows from real table Table A
    Increase pctincrease parameter Table A
    copy rows from Table B into Table A.

    Is there an easier way to do this ?

    Oracle 8.1.6
    SCO Unixware 7

    Thanks

    Suresh


  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Sorry guys, I meant increase pctfree not pctincrease

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    Am I answering my own question ?

    I have just found a command :

    To rebuild :
    alter table owner.table_name move;

    To rebuild with new storage parameters :
    alter table owner.table_name move storage (initial next etc.);

    Has anybody used these commands before and know of any implications.

    Thanks



  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you lock the table when you are moving it

  5. #5
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Another way is to move the table to a new tablespace with more accurate storage parameters.

    alter table XXXX move tablespace YYYY

    You need to rebuild the indexes because they become invalid.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Rebuilding or moving the table with modified storage parameters will only solve your problems if rows in the original table are *migrated*. If they are *chained* then the only cure is to recreate the entire database with larger block size. (Or to migrate to 9i where you can have different blocksizes in different tablespaces....).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    Thanks

    Cheers Folks

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi Jmodic

    In 9i you can have different block sizes for different tablespaces.wow does that mean that if i need to change the db_block_size i dont have to recreate my database like 8i.

    regards
    hrishy

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, that's what I have read. I haven't played with 9i yet, but as I understand it you can simply add another tablespace with different block size and move the table in there.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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