Re: Should/Can I shrink dictionary table?

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Wed, 15 Oct 2008 17:07:53 +0200
Message-ID: <486b2b610810150807j2632fdf0l2645824c49a87346@mail.gmail.com>


The short answer is you can't, and as Zhuchao already suggested, I wouldnt worry about it unless you identify it to be a performance problem in your environment. As there's lots of systems out there with 10's of 1000's of views, I doubt that this is the case though.

The long answer is you could probably do it by using sql*plus copy command to copy the rows to a dummy table, truncate view$ and copy them back. As view$ isn't protected by bootstrap, this is possible. But DO NOT do anything like this without contacting Oracle Support first. In general, you will need to be very convincing that this is the only way for you to fix a critical issue, which apparently here isn't the case, so I doubt you'd get their blessing. The only "supported" way out of this would be to re-create the database, and use exp(dp)/imp(dp) to move the data over.

Cheers

Stefan

On Wed, Oct 15, 2008 at 1:52 PM, <Mayen.Shah_at_lazard.com> wrote:

>
> Hi Gurus,
>
> Implemented new packaged application on 10.2.0.3 EE on Solaris9
>
> Application server need to be recycle every week. Each time app server is
> recycled; it created many views and supposed to drop at time of shutting
> down app. Creating new views part worked fine but dropping part did not
> work. So ended up with 35829 views in one schema and sys.view$ grew to be
> 600M. I caught this and after confirming with vendor, dropped all views.
>
> I am little concerned of oversized sys.view$ table and potential
> performance impact, since application relies heavily on views and sys.view$
> may be accessed heavily by recursive sql. How can I reclaim (shrink) space
> from sys.view$? sys.view$ does have long column.
>
> Thank you for your comments.
>
> Regards
> Mayen

-- 
=========================

Stefan P Knecht
Senior Consultant
Systems Engineering

OPITZ CONSULTING Schweiz GmbH
Seestrasse 97
CH-8800 Thalwil

Mobile +41-79-571 36 27
stefan.knecht_at_opitz-consulting.ch
http://www.opitz-consulting.ch

OCP 9i/10g SCSA SCNA
=========================

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 15 2008 - 10:07:53 CDT

Original text of this message