Re: Database directory Size and Memory Usage

Hi Jason

In terms of physical size of the database you will need to first of all perform an export (do not compress extents), so that you have a logical backup of the database as is.

The next step is to recreate the database and tablespaces with manually assigned sizes. Do NOT select autoextend for the datafiles if you wish to retain manual control over space allocation. Especially do not assign autoextend to the temp tablespace's datafiles for your users, since this can grow and grow. Doing this will control the space allocated to the database. It will of course mean you have to monitor usage etc.

Next you will need to import the export you created earlier. You may find it appropriate to have recreated the schema objects first with sensible storage clauses for your data and run the import with ignore = n.

As far as memory allocation is concerned the amount of memory in use by the database server is controlled by several database initialisation parameters , the most important of which are probably db_block_buffers, db_block_size (which together control the size of the buffer cache), & shared_pool_size. In addition each process which connects will get allocated an area of memory for sorting the size of which is determined by sort_area_size - so if this is large and you have lots of sessions connecting this can eat memory too.

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK


"Jason Rigsbee" <jrigsbee_at_rigsbee.net> wrote in message
news:#GpTir29$GA.369_at_cpmsnbbsa07...


> I have a database that has grown toooooo big for what it is and what it

> does.  There is not much to this database (not many tables, views,

 triggers,


> data, etc.) and it is used only for development purposes.  It has grown to

> over 8GB in size.  How can I shrink (compress) a database and then limit

 the


> amount of disk space it can gobble up?

>

> Finally I also need to know how I can limit each database to a selected

> static memory space but preferably a dynamic range of memory-lets say

> between 0 MB and 150 MB of physical RAM when running?

>

> Thank you.

>

> jlr

>

>

>