Re: Checking Size of the Stored procedures

markp7832_at_my-dejanews.com wrote:

> In article <7ech7b$bq9$1_at_miri.tele.dk>,
> "Søren Klintrup" <bigchief_at_aub.dk> wrote:
> <snip>
> > At the course i was told that 60 megabytes should be more than enough for
> > most systems ... and 300 megabytes for a developers system .... ok .. i
> > would have gone for 300-400 megs ... but 1.7 Gigabytes ??
> >
> > it seems that the stored procedures takes a lot of the space, but i'm not
> > sure ... that is what i want to check ...
> >
> > I just did a full export of the database and the stored procedures took over
> > 95% of the export time ..
> >
> >

<snip>

> There were already two replies, but I do not think either really answered
> your question. I believe that the package source is actually stored in
> sys.idl_ub1$ and idl_ub2$. The Oracle Designer product install actually
> instructs you to modify the storage for these two tables as part of the
> install, and that system has a lot of packaged code.

Correct. And Designer will install a little over 300 packages.

> Please do not make any
> changes, or attemp to re-org any sys owned tables except under the advise of
> Oracle support.

The standard increase of idl_ub1$ and idl_ub2$ is 50%. What the Designer installation
manual suggests, is to set the increase to 0%. What I always suggested (I used to work
for Oracle Case Support), was to set it to 0% for the duration of the installation, and
reset to 50% afterwards.
Reason for this is twofold: setting pct_increase to 0% will actually speed up the installation of Designer, but whenever (one of) those tables runs out of space, your db will come to an hold.

> May I suggest that you look at sys.dba_extents to see how much space is
> actually allocated to objects for tablespace system and rebuild your database
> allocating two to four times this amount for growth, Oracle auditing, etc....
> You should also verify that no user objects were allocated to this tablespace
> as they should be moved elsewhere, and no user other than sys uses system as
> their temporary tablespace.

Why that? If you created a temporary tablespace, make SYS and SYSTEM use that temporary tablespace.
Also, when designer (or another product) is installed, make sure the users have a default tablespace other than the system tablespace. I do this even for SYSTEM. Mind you, not SYS, SYSTEM only.
FYI, my SYSTEM tablespace now occupies 147MB on NT, with 2 versions of Designer installed,
and WebServer, and OEM.
--
Frank van Bortel

Technical Consultant Oracle
V&L Informatica BV

Work                                Home
Postbus 545                         Hunzestraat 4
7500 AM Enschede                    7555 WB Hengelo
(31)53.434.1500                     (31)74.242.5046