Santosh Kangane

Well, as all other Oracle databases, we too facing classical problem of data fragmentation on OLTP system handling 2500 + Concurrent users. With daily data archival process for around 3.5K+ master rows and its 60 child tables in proportion of 2 - 7 times bigger. So much of concurrent operations and every day data deletion was causing data fragmentation and index badness; we have to do shrinking activity periodically. Then, I have started investigation as to how can we do this auto? Can oracle take care of data fragmentation by auto shrinking? Can’t DML operation de-fragment underling data?    I have ASM as underling data storage. After investigation and talking with Tom Keyt, I found that Keeping “Row movement Enable” is the solution that oracle has provided with 9i and above version. Enable Row movement is just a permission given to oracle to change the row address(ROWID) when Inert/Update DML operation executes. Oracle will check if it can move the current rows in upper bl