Re: Identifying deleted rows for a table

From: joel garry <joel-garry_at_home.com>
Date: Tue, 19 Apr 2011 09:20:09 -0700 (PDT)
Message-ID: <2d3432d0-edad-416e-a1b2-ea9444dc221c_at_d26g2000prn.googlegroups.com>

On Apr 19, 2:59 am, Randolf Geist <mah..._at_web.de> wrote:
> On Apr 18, 4:48 pm, 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
>
> > 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:http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/schema...

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. http://download.oracle.com/docs/cd/B19306_01/license.102/b14199/options.htm

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": http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/options.htm#CIHGFIAF

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.

jg

--
_at_home.com is bogus.
http://web.signonsandiego.com/news/2011/apr/17/balboa-park-expands-into-the-digital-age/

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