OraFAQ Forum: Server Administration » SGA Size modification

Home » RDBMS Server » Server Administration » SGA Size modification (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SGA Size modification [message #638752]

Fri, 19 June 2015 11:47

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?

Report message to a moderator

Re: SGA Size modification [message #638754 is a reply to message #638752]

Fri, 19 June 2015 11:54

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

Report message to a moderator

Re: SGA Size modification [message #638756 is a reply to message #638752]

Fri, 19 June 2015 12:10

John Watson
Messages: 8976
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.

Report message to a moderator

Previous Topic:	Unable to extend temp segment
Next Topic:	how to change character set in oracle 11g r2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 05:26:58 CDT 2025