Re: Identifying deleted rows for a table
Date: Mon, 18 Apr 2011 17:50:19 -0700 (PDT)
Message-ID: <c1937467-db2e-4c98-86c4-243445807a85_at_y31g2000vbp.googlegroups.com>
On Apr 18, 1:28 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "dba cjb" <chris.br..._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 Lewishttp://jonathanlewis.wordpress.com
Based on the OP's question about doing an export/import sounds to me as if he thinks he has allocated/unused blocks taken up by a particular table and/or index most typically caused by large batch inserts followed by large batch of deletes. With LMT, once space is allocated to an OBJECT, it is never reclaimed for general use until you 1) drop the object, (export/import would take care of that and was the traditional way to reclaim space on a variety of databases and OS platforms) , 2) SHRINK the object, or 3) move the object to another tablespace.
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.
if you have 1000000 data blocks with only one 1K row, you might reduce the overall size of your RMAN backup as well as reduce your overall backup times. That being said, using disk backups for RMAN and using a minimum of 4 threads, I had (a few companies ago) a 2TB backed up in < 3hrs. 5 or more threads seem to push the I/O bandwidth to the point my < 3hr backup would take > 6. YMMV. Received on Mon Apr 18 2011 - 19:50:19 CDT