Home » RDBMS Server » Server Administration » SGA Size modification (Oracle 11g)
SGA Size modification [message #638752] Fri, 19 June 2015 11:47 Go to next message
rajendra.prasad
Messages: 1
Registered: June 2015
Location: India
Junior Member

Need to modify SGA lets say xxx to yyy.

How ever found the below steps

##############################################################

SQL> alter system set memory_target=6G scope=spfile
2 /

System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2058981376 bytes
Fixed Size 1300968 bytes
Variable Size 536872472 bytes
Database Buffers 1509949440 bytes
Redo Buffers 10858496 bytes
Database mounted.
Database opened.

SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 6G
memory_target big integer 6G
shared_memory_address integer 0

SQL>

##############################################################

My question is when we are using a dynamic parameter, is it necessary that we need to restart the Database?

Re: SGA Size modification [message #638754 is a reply to message #638752] Fri, 19 June 2015 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rajendra.prasad wrote on Fri, 19 June 2015 09:47


My question is when we are using a dynamic parameter, is it necessary that we need to restart the Database?



answer depends upon the value of any of the *MODIFIABLE* columns below
SQL> desc v$parameter
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 ISBASIC                                            VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER


Re: SGA Size modification [message #638756 is a reply to message #638752] Fri, 19 June 2015 12:10 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Need to modify SGA lets say xxx to yyy.

How ever found the below steps

##############################################################

SQL> alter system set memory_target=6G scope=spfile
2 /
This is not modifying te SGA. It is modifying the memory target. You need to look at your sga_max_size and sga_target paraneters.
Previous Topic: Unable to extend temp segment
Next Topic: how to change character set in oracle 11g r2
Goto Forum:
  


Current Time: Thu Mar 28 07:47:38 CDT 2024