{"id":18054,"date":"2020-08-16T17:28:29","date_gmt":"2020-08-16T17:28:29","guid":{"rendered":"https:\/\/ittutorial.org\/?p=18054"},"modified":"2020-08-16T17:40:08","modified_gmt":"2020-08-16T17:40:08","slug":"change-memory_max_target-and-memory_target","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/change-memory_max_target-and-memory_target\/","title":{"rendered":"Change Memory_max_target and memory_target in Oracle"},"content":{"rendered":"<p id=\"tw-target-text\" class=\"tw-data-text tw-text-large XcVN5d tw-ta\" dir=\"ltr\" data-placeholder=\"\u00c7eviri\">MEMORY_TARGET is a database initialization parameter that can be used for automatic PGA and SGA memory sizing.<\/p>\n<p dir=\"ltr\" data-placeholder=\"\u00c7eviri\"><strong>MEMORY_TARGET<\/strong>\u00a0provides the following:<\/p>\n<p>A single parameter for total SGA and PGA sizes<br \/>\nAutomatically sizes SGA components and PGA<br \/>\nMemory is transferred to where most needed<br \/>\nUses workload information<br \/>\nUses internal advisory predictions<br \/>\nCan be enable by DBCA at the time of Database creation.<\/p>\n<p>By using one parameter we don\u2019t need to use all other SGA and PGA parameters like.<\/p>\n<p>DB_CACHE_SIZE<br \/>\nSHARED_POOL_SIZE<br \/>\nLARGE_POOL_SIZE<br \/>\nJAVA_POOL_SIZE<br \/>\nPGA_AGGREGATE_TARGET<\/p>\n<pre>10:37:48 SQL&gt; SHOW PARAMETER TARGET\r\n\r\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |VALUE\r\n\r\n------------------------------------|-----------|------------------------------\r\n\r\narchive_lag_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |0\r\n\r\ndb_big_table_cache_percent_target\u00a0\u00a0 |string\u00a0\u00a0\u00a0\u00a0 |0\r\n\r\ndb_flashback_retention_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |1440\r\n\r\nfast_start_io_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |0\r\n\r\nfast_start_mttr_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |0\r\n\r\nmemory_max_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|50G\r\n\r\nmemory_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|50G\r\n\r\nparallel_servers_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |256\r\n\r\npga_aggregate_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|0\r\n\r\nsga_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|0<\/pre>\n<p dir=\"ltr\" data-placeholder=\"\u00c7eviri\">Now we can change the value of parameter memory_target and memory_max_target<\/p>\n<pre>10:37:41 SQL&gt; ALTER SYSTEM SET MEMORY_MAX_TARGET = 80G SCOPE = SPFILE;\r\n\r\n10:39:57 SQL&gt;\u00a0 alter system set memory_target=80G SCOPE = SPFILE;\u00a0\u00a0\r\n\r\n<span lang=\"en\">and we close the database<\/span><\/pre>\n<pre>$ sqlplus \/ as sysdba\r\n\r\nSQL&gt; shutdown immediate<\/pre>\n<p><span lang=\"en\">and we open the database<\/span><\/p>\n<pre>10:41:03 SQL&gt; startup;\r\n\r\nORACLE instance started.\r\n\r\nTotal System Global Area|5.1540E+10|bytes\r\n\r\nFixed Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 5870816|bytes\r\n\r\nVariable Size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |5909777184|bytes\r\n\r\nDatabase Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |4.5617E+10|bytes\r\n\r\nRedo Buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0 6709248|bytes\r\n\r\nDatabase mounted.\r\n\r\nDatabase opened.<\/pre>\n<pre>10:41:49 SQL&gt; SHOW PARAMETER TARGET\r\n\r\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |VALUE\r\n\r\n------------------------------------|-----------|------------------------------\r\n\r\narchive_lag_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |0\r\n\r\ndb_big_table_cache_percent_target\u00a0\u00a0 |string\u00a0\u00a0\u00a0\u00a0 |0\r\n\r\ndb_flashback_retention_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |1440\r\n\r\nfast_start_io_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |0\r\n\r\nfast_start_mttr_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |0\r\n\r\nmemory_max_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|80G\r\n\r\nmemory_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|80G\r\n\r\nparallel_servers_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |integer\u00a0\u00a0\u00a0 |256\r\n\r\npga_aggregate_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|0\r\n\r\nsga_target\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |big integer|0\r\n\r\n10:41:57 SQL&gt;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>MEMORY_TARGET is a database initialization parameter that can be used for automatic PGA and SGA memory sizing. MEMORY_TARGET\u00a0provides the following: A single parameter for total SGA and PGA sizes Automatically sizes SGA components and PGA Memory is transferred to where most needed Uses workload information Uses internal advisory predictions Can be enable by DBCA at &hellip;<\/p>\n","protected":false},"author":61,"featured_media":18055,"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,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5047,1994,5045],"tags":[7671,12404],"class_list":["post-18054","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-architecture","category-oracle","category-upgrade","tag-memory","tag-memory-target"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/08\/download.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/18054","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/61"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=18054"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/18054\/revisions"}],"predecessor-version":[{"id":18059,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/18054\/revisions\/18059"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/18055"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=18054"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=18054"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=18054"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}