Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Checking Size of the Stored procedures

Re: Checking Size of the Stored procedures

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 06 Apr 1999 20:26:08 GMT
Message-ID: <370a5f61.2724564@news.siol.net>


On Tue, 6 Apr 1999 10:39:47 +0200, "Søren Klintrup" <bigchief_at_aub.dk> wrote:

>I'm a newly started DBA, i've just attended the oracle DBA course .. a Great
>course...
>
>The next monday i started to look at our database (wich was set up by the
>guy before me who knew nothing about oracle, as me :).
>And in all my horror i see that the System tablespace spans 1.7 Gigabyte!!!
>(yup that is 1700 Megabytes)..
>
>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 ..
>
>The database is not in production yet, so i can close it as much as i want
>.... but as far as i've been told theres still some data that has been
>transferred to the database ..
>
>How do i check the size of the Stored Procedures ??
>How do i check how which Stored Procuderes is installed ?
>How do i check the size of the tables in the system tablespace ?
>If none of this helps .. how do i check whats making the system tablespace
>so large ..

I realy doubt the stored procedures in your database are taking up even nearly anything around 1.7 GB of the system tablespace! You'd better check the default tablaspace for all of your database users. If any of your db schemas except of SYS (and maybe SYSTEM, although it is advisable that even user SYSTEM is assigned some other tablespace as default) has SYSTEM tablespace set as default, then it is time to grab your course materials (and other Oracle RDBMS related manuals) to find out how you'll best change your database layout (like exp + change users specifications + imp).

Another, even worse possibility is that your application schema owner is SYS. In that case you even can't export the aplication data and you'll have to implement some other, more complicated steps.

In any case, check which segments are taking the most of your SYSTEM tablespace. Connect as DBA user to SQL*PLUS, set PAUSE ON (as there is many segments constituting database dictionary, and you'll probably want to terminate the query output after a couple of pages listing the largest segments) and isue the following query:

SELECT owner, segment_name, segment_type, bytes FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY bytes DES;

This will list the largest tables, indexes and clusters, sorted by their size in the descending order.

>/Søren

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)



The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Apr 06 1999 - 15:26:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US