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