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 curre...
Thinking Through Technology.... To design any system put your Business Standard and Technology together keeping them rightly disjoin....