Re: Should/Can I shrink dictionary table?

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Wed, 15 Oct 2008 11:32:32 -0500
Message-ID: <48F61B20.6060902@gmail.com>


Mayen

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

There is an assumption here that as as view$ table size grows, performance will worsen. It is most probably an incorrect assumption. Performance worsens due to inefficient access path, not necessarily due to size of the table. Normal application processes and recursive SQL should not suffer from performance issues, as access to this table is through indices, not a full table scan.

As Stephen pointed out, only supported way is to create dummy database and use transportable tablespaces & exp/imp to move everything to new database. Unless, you have a specific performance issue, this is not exactly warranted.

Cheers
Riyaj
The Pythian Group www.pythian.com
http://orainternals.wordpress.com

Bort, Guillermo wrote:
>
> As far as I recall, Oracle does not support automatic segment space
> management for the SYSTEM tablespace, with that in mind, using alter
> table shrink space compact and shrink space cannot be done (as it
> requires row movement which in turn requires ASSM). Open an SR and
> request for support on optimizing this particular table, but I think
> it cannot be done. Do you create/drop views frequently?
>
>
>
> HTH
>
>
>
> *Guillermo Alan Bort*
>
> DBA / DBA Main Team
>
>
>
> *EDS*, an HP company
>
> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Jared Still
> *Sent:* Wednesday, October 15, 2008 1:00 PM
> *To:* Mayen.Shah_at_lazard.com
> *Cc:* oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
> *Subject:* Re: Should/Can I shrink dictionary table?
>
>
>
> On Wed, Oct 15, 2008 at 4:52 AM, <Mayen.Shah_at_lazard.com
> <mailto:Mayen.Shah_at_lazard.com>> wrote:
>
>
> 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.
>
>
> Create an SR and ask support if and how you can do it.
>
> That way you will have a supported solution.
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 15 2008 - 11:32:32 CDT

Original text of this message