Re: Identifying deleted rows for a table

From: onedbguru <onedbguru_at_yahoo.com>
Date: Tue, 19 Apr 2011 16:25:09 -0700 (PDT)
Message-ID: <873dc26d-5257-4720-a603-f5d03cd1bb91_at_d27g2000vbz.googlegroups.com>



On Apr 19, 12:20 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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/optio...
>
> The 11g license doc is more clear about accessing the functionality
> "whether through Enterprise Manager Console, Desktop Widgets, command-
> line APIs, or direct access to the underlying data":http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/optio...
>
> 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-in...

<soapbox=on>
If anyone didn't negotiate these packages for free, they are in serious need of lessons in negotiating with Oracle. These "packages" are an integral part of the database engine and cannot be removed and you cannot not install them. And because they use my CPU and my Memory and my Storage for something that I cannot use is ludicrous. Later versions of Oracle use this whether or not you license it to keep itself intact. Try billing Oracle for the 1-3% CPU/memory and sometimes Gigs of storage that these "licensed options" take out of your system for which you get no real benefit. Never EVER take Oracle's first quote
</soapbox> Received on Tue Apr 19 2011 - 18:25:09 CDT

Original text of this message