Re: Identifying deleted rows for a table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 18 Apr 2011 18:28:59 +0100
Message-ID: <66idnYPaj_gt6THQnZ2dnUVZ8lidnZ2d_at_bt.com>



"dba cjb" <chris.brown_at_providentinsurance.co.uk> wrote in message news:56bbc456-e724-472c-b9f2-eecbd0b62f0a_at_e8g2000vbz.googlegroups.com...
> Oracle 10.2.0.4 enterprise windows 2003
>
> I am interested in finding out how much of a table is made up of
> deleted rows
>
> / just to see if there is a potential benefit for backups /
> operations
>
> I have run following query
>
>
> SELECT blocks as BLOCKS_USED, empty_blocks
> FROM dba_tables
> WHERE table_name='TASK'
>
> It shows me I have no empty blocks
>
> Is there a query that shows me deleted rows / non-deleted rows
> within the used blocks for this table
>
> My aim is to assess wether a table export / import would be beneficial
> for
>
> 1) rman backups
> 2) database operations
>
>
> regards
> Chris B
>

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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Received on Mon Apr 18 2011 - 12:28:59 CDT

Original text of this message