Re: Identifying deleted rows for a table

From: joel garry <>
Date: Tue, 19 Apr 2011 09:20:09 -0700 (PDT)
Message-ID: <>

On Apr 19, 2:59 am, Randolf Geist <> wrote:
> On Apr 18, 4:48 pm, dba cjb <>
> wrote:
> > Oracle enterprise windows 2003
> > I am interested in finding out how much of a table is made up of
> > deleted rows
> > SELECT blocks as BLOCKS_USED, empty_blocks
> > FROM dba_tables
> > WHERE table_name='TASK'
> > It shows me I have no empty blocks

Thanks to all for the various clarifications.

> Finally if you want to you could try the "Space/Segment Advisor"
> provided by Oracle, however I'm unsure about the license implications
> since it says that it will use AWR historic information for growth
> trend analysis, but the Segment Advisor itself is not mentioned in any
> license document:

The 10g license doc says you need the tuning pack to reorganize objects, the diagnostics pack to use the tuning pack, and if you click the setup link on the EM home page, then the management packs link, you will see check boxes that will take away any links or tabs you can't use. Doing this grays out the performance tab on the home page, so you can't get to any of the useful screens.

The 11g license doc is more clear about accessing the functionality "whether through Enterprise Manager Console, Desktop Widgets, commandline  APIs, or direct access to the underlying data":

For my largest tables which periodically show up and I then set up a shrink job, it appears to be wildly overoptimistic about how much space it will get, much like those who waste time rebuilding indices every weekend.

Looking at the dba_feature_usage_statistics is left as an exercise for the paranoid, or realists, as the case may be.


-- is bogus.
Received on Tue Apr 19 2011 - 11:20:09 CDT

Original text of this message