Re: Identifying deleted rows for a table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 19 Apr 2011 07:58:20 +0100
Message-ID: <SaednVWjh-DArzDQnZ2dnUVZ7vCdnZ2d_at_bt.com>

]"onedbguru" <onedbguru_at_yahoo.com> wrote in message
news:c1937467-db2e-4c98-86c4-243445807a85_at_y31g2000vbp.googlegroups.com...
]On Apr 18, 1:28 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
]wrote:
]>
]> It depends how much work you want Oracle to do, what sort of precision
you
]> want in your answer, and what assumptions you are prepared to make about
]> your data. For example, if you're prepared to assume that every row in a
]> table is about the same size then there's a query on my blog that could
be
]> helpful: http://jonathanlewis.wordpress.com/2011/04/05/rows-per-block/
]>
]> Slice and dice it various ways, but if you have an expected number of
rows
]> per block this gives you some idea of how many blocks are not filled.
]>
]
]You can spend a lot of time researching to find allocated but unused
]blocks or you can just look up ALTER {TABLE|INDEX} SHRINK and attempt
]to fix the problem ONLINE without interruption of day-to-day
]activities.

You could indeed attempt to fix the problem without knowing what the problem was and what the impact might be, but then you might end up as one of the unhappy individuals posting the question: "I did a shrink space on a table and its been running for the last 24 hours and has generated over 75 GB of redo; what should I do ?"

Joel Garry's comment about 'average row length' x number of rows might be a good enough first approximation for the OP to allow him to decide how much more time he's going to spend on understanding the problem. Your hypothesis of a batch job that deletes lots of old rows while inserting new ones might give him the idea that SHRINK is the worst way he could have to reclaim his space because in that scenario he might end up copying every row (one at a time - with index maintenance) from the "new" end of the table to the "old" end of the table because all the empty space is currently at the old end of the table.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Received on Tue Apr 19 2011 - 01:58:20 CDT