KinsaKaUy? wrote:Ok, you have a database. You or someone you work with makes an informed decision that table_x doesn't need all 10 million rows, and so deletes 9 million of them. That is a conscious decision relating specifically to table_x. Then you or someone you work with determines that that table_x will never grow back to 10 million rows. That is another conscious decision relating specifically to table_x. And now you want oracle to tell you that table_x is a candidate for reorg? You already know it's a candidate. Your own analysis led to the decision to delete all those rows because they weren't needed. Your own analysis led to the decision that the table would never grow back to anything near its original size. So why wold you NOT already know that table_x is your candidate? The only reason you'd need an analysis from Oracle would be if these massive deletes were occurring in the natural course of business processes and you had no foreknowledge of which tables were involved.
Sorry Im not making myself clear. Maybe what I want is, I want to find the tables that has lots of rows deleted and I want to compress it back to a smaller size because I know it wont grow big again. For example a table that has 10million rows originally and 9million has beed deleted out of it. I want to find these tables that have this kind of characteristics. And maybe this is what is being mentioned above as "can be done" using a pl/sql program. :)