Re: Identifying deleted rows for a table
From: joel garry <joel-garry_at_home.com>
Date: Wed, 20 Apr 2011 08:53:46 -0700 (PDT)
Message-ID: <bd560c20-7322-4be6-ad79-2e868c6b17c0_at_17g2000prr.googlegroups.com>
On Apr 19, 4:25 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> 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>
Date: Wed, 20 Apr 2011 08:53:46 -0700 (PDT)
Message-ID: <bd560c20-7322-4be6-ad79-2e868c6b17c0_at_17g2000prr.googlegroups.com>
On Apr 19, 4:25 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> 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>
I guess you've never come into a place while the lawsuit dust is still settling.
jg
-- _at_home.com is bogus. http://www.nytimes.com/2009/04/20/us/20marijuana.htmlReceived on Wed Apr 20 2011 - 10:53:46 CDT