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...
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