Change Memory_max_target and memory_target in Oracle

MEMORY_TARGET is a database initialization parameter that can be used for automatic PGA and SGA memory sizing.

MEMORY_TARGET provides 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 the time of Database creation.

By using one parameter we don’t need to use all other SGA and PGA parameters like.

DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
PGA_AGGREGATE_TARGET

10:37:48 SQL> SHOW PARAMETER TARGET

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

archive_lag_target                  |integer    |0

db_big_table_cache_percent_target   |string     |0

db_flashback_retention_target       |integer    |1440

fast_start_io_target                |integer    |0

fast_start_mttr_target              |integer    |0

memory_max_target                   |big integer|50G

memory_target                       |big integer|50G

parallel_servers_target             |integer    |256

pga_aggregate_target                |big integer|0

sga_target                          |big integer|0

Now we can change the value of parameter memory_target and memory_max_target

10:37:41 SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET = 80G SCOPE = SPFILE;

10:39:57 SQL>  alter system set memory_target=80G SCOPE = SPFILE;  

and we close the database
$ sqlplus / as sysdba

SQL> shutdown immediate

and we open the database

10:41:03 SQL> startup;

ORACLE instance started.

Total System Global Area|5.1540E+10|bytes

Fixed Size              |   5870816|bytes

Variable Size           |5909777184|bytes

Database Buffers        |4.5617E+10|bytes

Redo Buffers            |   6709248|bytes

Database mounted.

Database opened.
10:41:49 SQL> SHOW PARAMETER TARGET

NAME                                |TYPE       |VALUE

------------------------------------|-----------|------------------------------

archive_lag_target                  |integer    |0

db_big_table_cache_percent_target   |string     |0

db_flashback_retention_target       |integer    |1440

fast_start_io_target                |integer    |0

fast_start_mttr_target              |integer    |0

memory_max_target                   |big integer|80G

memory_target                       |big integer|80G

parallel_servers_target             |integer    |256

pga_aggregate_target                |big integer|0

sga_target                          |big integer|0

10:41:57 SQL>

 1,071 views last month,  3 views today

About baranyildiz

I am Computer Engineer. Oracle & SQL Server Database,Goldengate,Guardium,Tableau , Exadata Machine administrator 6 years.I took part in many different projects in energy sector and customer information systems. I worked on sql server, oracle consolidation,migration, upgrade projectsMail : [email protected]

Leave a Reply

Do NOT follow this link or you will be banned from the site!