Re-organise segment to eliminate chaining/migration

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

Sorry guys, I meant increase pctfree not pctincrease

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

you lock the table when you are moving it

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.

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....).

Cheers Folks

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

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.