Reduce SGA size in Oracle 10g


Due to some limitation we need to decrease the SGA size of our IDES system.-

What is SGA ?

– The shared pool
– The large pool
– The Java pool
– The buffer cache
– The Streams pool
– The Log Buffer

All this memory areas together are the SGA.

we check the parameters & found that our database is running via SPfile.

We follow the below steps to perform this action:

1. login to sql / as sysdba

SQL> show SGA – Check current SGA size.

SQL> show parameter – check whether oracle is running via SPFILE or PFILE.

SQL> create pfile from spfile; ( pfile is created in dbs or database folders)

SQL>shutdown immediate

while checking parameters we found that our SGA_MAX_SIZE = 1 GB in working pfile.(init<SID>.ora)

we decrease the value 25% of current SGA_MAX_SIZE (512MB+256MB = 768 MB)

we also decrease the shared_pool_size value & db buffer cache value to 25%.

save the file.

Now start Database with this newly PFILE.

SQL>starup PFILE=”d:oracleSID102databaseinit<SID>.ora”

if database is successfully started then convert it to SPFILE.

SQL> create spfile from pfile;

SQL> shutdown immediate

SQL> startup

Now check the value of changed SGA.

SQL> show sga

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.