Re: Slow response from Data Dictionary?

Unfortunately

command
alter table table1 deallocate unused;  

frees up space only up to the High water mark, therefore it does not help much. For example, if you had 10000 rows inserted into table table1 and then deleted all of them your HWM is still at 10000 rows.

Only command that reclaims all space in a table is the dangerous command TRUNCATE, with no option to rollback.

As an aside, if you want to reclaim space upto HWM and keep 1M above it for expansion then use
alter table table1 deallocate unused keep 1M;

suresh.bhat_at_mitchell-energy.com

andreas.prusch_at_sintec.de wrote in article <6p42og$gn2$1_at_nnrp1.dejanews.com>...
> Dropping Objects is mainly a thing of freeing space and updating
> dependencies in the data dictionary. Because of the behaviour, that
> deleting rows not freeing space tables may have many extents but
> no rows. By the way, select count(*) then works very slow, too.
> You have to free the space of those tables manually with alter table
> deallocate ... (see reference).
> Analyzing dictionary objects don't work, because there are table
> locks on the tables the analyze command works on. In case of the
> dictionary tables, the tables will locked in which the analyze
> results should be written. That doesn't work.
> On the other hand dictionary performance is more important for
> overall database performance for internal use. See v$rowcache
> and v$librarycache for information. May be increase your shared
> pool size (30MB - 60 MB).
>
> Good luck
> Andreas Prusch
>
> In article <01bdb4e9$f963f860$4244ddcc_at_jmara.connects.com>,
> "John Mara" <john.mara_at_connects.com> wrote:
> > We are running a rather large development server (7000 tables over 20
> > schemas) and have some pretty slow response times when accessing the
DBA_
> > tables. This is somewhat understandable given the volume. Are there any
> > tuning options to speed things up? I have heard that it is not a good
idea
> > to ANALYZE the Data Dictionary tables, that they are self maintaining.
> >
> > We tried an export/import of the entire database to compress extents,
and
> > surprisingly found that our response times got worse? In particular,
> > dropping any objects that have storage allocated has become extremely
slow
> > (minutes for a table with no data???).
> >
> > Any suggestions would be appreciated. Thanks.
> >
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
Received on Wed Jul 22 1998 - 10:57:49 CDT