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

>How do i check the size of the Stored Procedures ??

I'm not sure you can. At least, I don't know how to.

>How do i check how which Stored Procuderes is installed ?
select object_type, object_name from dba_objects where object_type in
('PACKAGE', 'PACKAGE BODY', 'PROCEDURE');

or

select object_type, object_name from dba_objects where object_type in
('PACKAGE', 'PACKAGE BODY', 'PROCEDURE')
and owner != 'SYS';

>How do i check the size of the tables in the system tablespace ?
select segment_name, sum(bytes) from dba_segments where segment_type='TABLE'
and tablespace_name='SYSTEM'
group by segment_name;

>If none of this helps .. how do i check whats making the system tablespace
so large ..
select segment_name, sum(bytes) segment_size
from dba_segments
where tablespace_name='SYSTEM'
group by segment_name
order by segment_size desc;

Take a look at the dba_object_size view.

select c.obj#, o.name, sum(c.bytes)
  from sys.code_pieces c, sys.obj$ o
  where c.obj# = o.obj#
  group by c.obj#, o.name

This will give you the size of all stored procedures and packages

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.