Re: Should/Can I shrink dictionary table?
Date: Wed, 15 Oct 2008 14:29:03 +0000
I would recommend you don't touch it unless it is really impacting your performance, and view$ is confirmed to be the root cause.
Dictionary tables are usually well indexed and such query shouldn't be heavily impacted by fragemented tables.
Using tts to transfer all tablespace to a brandnew db could help, never did that in prod though(we had very fragemented fet/uet tables in very old db).
Sent via BlackBerry by AT&T
Date: Wed, 15 Oct 2008 07:52:46
To: <oracle-l_at_freelists.org>; <oracle-l-bounce_at_freelists.org> Subject: Should/Can I shrink dictionary table?
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.