Re: Identifying deleted rows for a table
Date: Mon, 18 Apr 2011 08:48:16 -0700 (PDT)
Message-ID: <c13cec73-b573-4845-bbdc-0c1e6eab0b83_at_e21g2000vbz.googlegroups.com>
On Apr 18, 7:48 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> 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
I don't understand why knowing what rows were deleted affects whether or not you perform an export; possibly you could explain that to us. Oracle provides a least two tools you could use to identify which rows have been deleted: the DBMS_LOGMNR package or Change Data Capture. There is (to my knowledge) no way to scan each data block associated with a table to find the rows currentlty marked as deleted, and using either of the above methods of deleted row identification could still not show an accurate 'image' of the table data as slots vacated by deleted rows could be filled by newly inserted data. You could also query the table for rowids then pass those through the DBMS_ROWID package to extract the file, block and row address then visually scan that for 'holes' (a time-consuming task). Again I don't understand why this is necessary information in the decision process on whether or not to perform an export.
David Fitzjarrell Received on Mon Apr 18 2011 - 10:48:16 CDT