{"id":250,"date":"2012-05-25T12:18:58","date_gmt":"2012-05-25T12:18:58","guid":{"rendered":"https:\/\/adilfahim.com\/myblog\/?p=250"},"modified":"2012-05-25T12:18:58","modified_gmt":"2012-05-25T12:18:58","slug":"reduce-sga-size-in-oracle-10g","status":"publish","type":"post","link":"https:\/\/adilfahim.com\/myblog\/reduce-sga-size-in-oracle-10g\/","title":{"rendered":"Reduce SGA size in Oracle 10g"},"content":{"rendered":"

Due to some limitation we need to decrease the SGA<\/strong><\/em> size of our IDES system.-<\/p>\n

What is SGA ?<\/p>\n

– The shared pool
\n– The large pool
\n– The Java pool
\n– The buffer cache
\n– The Streams pool
\n– The Log Buffer<\/p>\n

All this memory areas together are the SGA.<\/p>\n

we check the parameters & found that our database is running via SPfile.<\/strong><\/p>\n

We follow the below steps to perform this action:<\/p>\n

1. login to sql \/ as sysdba<\/p>\n

SQL> show SGA – Check current SGA size.<\/p>\n

SQL> show parameter – check whether oracle is running via SPFILE or PFILE.<\/p>\n

SQL> create pfile from spfile; ( pfile is created in dbs or database folders)<\/p>\n

SQL>shutdown immediate<\/p>\n

while checking parameters we found that our SGA_MAX_SIZE = 1 GB<\/strong><\/em> in working pfile.(init<SID>.ora)<\/p>\n

we decrease the value 25% of current SGA_MAX_SIZE<\/strong> (512MB+256MB = 768 MB<\/strong>)<\/p>\n

we also decrease the shared_pool_size value<\/strong> & db buffer cache value<\/strong> to 25%<\/strong>.<\/p>\n

save the file.<\/p>\n

Now start Database with this newly PFILE.<\/p>\n

SQL>starup PFILE=”d:oracleSID102databaseinit<SID>.ora”<\/p>\n

if database is successfully started then convert it to SPFILE.<\/p>\n

SQL> create spfile from pfile;<\/p>\n

SQL> shutdown immediate<\/p>\n

SQL> startup<\/p>\n

Now check the value of changed SGA.<\/p>\n

SQL> show sga<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8],"tags":[156,166,225,226,227,254],"class_list":["post-250","post","type-post","status-publish","format-standard","hentry","category-sap-updates","tag-oracle","tag-pfile","tag-sga","tag-sga_max_size","tag-shared_pool_size","tag-spfile"],"_links":{"self":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts\/250","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/comments?post=250"}],"version-history":[{"count":0,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/posts\/250\/revisions"}],"wp:attachment":[{"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/media?parent=250"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/categories?post=250"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/adilfahim.com\/myblog\/wp-json\/wp\/v2\/tags?post=250"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}