2008 7search acetone adwords c3000 cleaning cost count database debug dedicated delete disposal ecommerce flashback golf google hosting keyword long managed miva mpio oil oracle polystyrene poplar portable price query record recovery recycler recycling searchfeed select server software solvent sps system table thinner timestamp togo waste water web WHIR yahoo

Maintaining oracle data, shrinking database segments

Oracle database is optimized for insert, update and select statements while lacking on delete front. It is not possible to get high performance on first three items while trying to optimize for delete as well. What complicates the situation is possible flashback and rollback segments and when you put it all together your data will end up very fragmented. There is no magic defragmenter for databases that will do the work for you automatically, however, you can do so in two easy steps:

• ALTER TABLE mytable ENABLE ROW MOVEMENT
• ALTER TABLE mytable SHRINK SPACE CASCADE

Let us briefly examine these two statements. The first statement will change table definition to notify Oracle that it is ok to move rows around. This is necessary step in order to defragment data, without this statement all you would accomplish is to truncate empty space at the end of the table.

Scroll down for additional info labeled Details on shrink space cascade and what goes on

More info: Details on shrink space cascade and what goes on

permanent link to article http://freecash.hogger.net/maintaining_oracle_data_shrinking_database_segments

When running cascade shrink, the row movement tracked is propagated to table related items. CASCADE is the key here as you can easily shrink all of these items:

• Table data
• Indexes
• Materialized views
• Logs (ie materialized view log)

Once the cascade shrink is executed, the changes are propagated to LOBs, subpartitions and partitions as well. When the statement completes, all table related data will be shrunk and possibly reorganized if you had any fantom deleted records