| Changing SGA_MAX_SIZE [message #197558] |
Wed, 11 October 2006 15:15  |
itzac
Messages: 4
Registered: October 2006
|
Junior Member |
|
|
I'm using 9i and I'm having a heck of a time finding out how to change the value of sga_max_size. I've tried:
alter system set sga_max_size = 100M scope = SPFILE
It of course won't run if the database is up, but it also doesn't work after a shutdown command. Could someone please spell it out for me from sqlplus login on a running instance as a starting point? Or point me to the pertinent Oracle doc, even. I'd be your best friend forever.
It's very frustrating to know exactly what you want to do and not be able to figure out how to do it.
[Updated on: Wed, 11 October 2006 16:17] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #197778 is a reply to message #197592] |
Thu, 12 October 2006 12:10  |
itzac
Messages: 4
Registered: October 2006
|
Junior Member |
|
|
C:\>sqlplus /nolog
SQL> connect sys/* as sysdba
Connected.
SQL> alter system set sga_max_size = 700MB scope=SPFILE;
alter system set sga_max_size = 700MB scope=SPFILE
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 11 10:44:11 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/* as sysdba
Connected to an idle instance.
SQL> alter system set sga_max_size = 700MB scope=SPFILE;
alter system set sga_max_size = 700MB scope=SPFILE
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> help startup
SP2-0171: HELP not accessible.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 839983304 bytes
Fixed Size 454856 bytes
Variable Size 310378496 bytes
Database Buffers 528482304 bytes
Redo Buffers 667648 bytes
SQL> alter system set sga_max_size = 700MB scope=SPFILE;
alter system set sga_max_size = 700MB scope=SPFILE
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
|
|
|
|
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #324638 is a reply to message #197783] |
Tue, 03 June 2008 07:48 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Hello,
I changed the sga_max_size. But i am not able to see when i run the query select value from v$parameter where name='sga_max_size';
Here is the screenshot..
C:\>set oracle_sid=oraprod
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Jun 3 08:26:25 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect sys/sys as sysdba
Connected.
SQL> select value from v$parameter where name='sga_max_size';
VALUE
--------------------------------------------------------------------------------
135338868
SQL> alter system set sga_max_size=100M scope=SPFILE
2 ;
System altered.
SQL> select value from v$parameter where name='sga_max_size';
VALUE
--------------------------------------------------------------------------------
135338868
SQL> startup force
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name='sga_max_size';
VALUE
--------------------------------------------------------------------------------
135338868
SQL>
Is there any other init paramter i should change for this??
|
|
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #324693 is a reply to message #324643] |
Tue, 03 June 2008 13:04 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Hello ebrian, I agree with you. Thanks again.
Michel, Based on the reply, here is my understanding. We need to restart the instance for seeing the changed value in data dictionary. But once we altered the values by using the alter system command, it will be affective immediately. Please correct me if i miss anything...
Another question.
SQL> select value from v$parameter
2 where name='log_buffer';
VALUE
--------------------------------------------------------------------------------
524288
My understanding is, the above query should match the below Redo Buffers value. But it is not matching... Am i missing anything here... Please clarify me.
SQL> show sga
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL>
I would appreciate if you could answer.
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #324708 is a reply to message #324693] |
Tue, 03 June 2008 13:58 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | But once we altered the values by using the alter system command, it will be affective immediately.
|
Wrong, a static parameter sees its change only on the next instance life.
And this is the case for ANY parameter if you only record the change in spfile as you did.
Log buffer is flanked by guard pages that you can see in show sga.
Regards
Michel
[Updated on: Tue, 03 June 2008 14:05] Report message to a moderator |
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #324732 is a reply to message #197558] |
Tue, 03 June 2008 16:27 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Hello,
One more question. sorry to ask you too many question.
Here is my database version.
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
I changed the shared_pool_size dynamically as below.
SQL>
SQL> alter system set shared_pool_size=30m scope=both;
System altered.
The below query should match with 30M. But why it is not matching? Am i missing anything here..
SQL> select
2 sum(bytes)/1024/1024
3 from
4 v$sgastat
5 where
6 pool='shared pool';
SUM(BYTES)/1024/1024
--------------------
48
SQL>
|
|
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #324951 is a reply to message #324754] |
Wed, 04 June 2008 10:42 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
Anacedent, Yes. it is tiny database. It is my test database which is created in my home computer.
Michel,
SQL> alter system set shared_pool_size=30m scope=both;
System altered.
SQL>
SQL> select
2 sum(bytes)/1024/1024
3 from
4 v$sgastat
5 where
6 pool='shared pool';
SUM(BYTES)/1024/1024
--------------------
48
SQL> select min_size/1024/1024 from v$sga_dynamic_components where
2 component='shared pool';
MIN_SIZE/1024/1024
------------------
32
SQL>
SQL> select max_size/1024/1024 from v$sga_dynamic_components where
2 component='shared pool';
MAX_SIZE/1024/1024
------------------
32
SQL>
The above query is showing 2 MB more, because of the granule size..
Now my question here is, i am not sure why v$sgastat and v$sga_dynamic_components are showing different figure.
In the previous thread, you said, we need to wait for some time to shrink.. but i waited for couple of days. But no difference.
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #324969 is a reply to message #324952] |
Wed, 04 June 2008 11:55 |
shrinika
Messages: 306
Registered: April 2008
|
Senior Member |
|
|
anacedent, This is oracle9i. I think, v$sgainfo is available in oracle10g
Again i checked v$parameter. It shows same as v$sga_dynamic_components. Only v$sgastat is not matching. May be it is bug in oracle9i.
SQL> ;
1* select value/1024/1024 from v$parameter where name ='shared_pool_size'
SQL> /
VALUE/1024/1024
---------------
32
SQL>
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #633477 is a reply to message #633475] |
Thu, 19 February 2015 07:59 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's not like google treats them as though they're archaeological artefacts.
Whenever I enter something oracle related into google it almost always displays some really old forum posts on the first page.
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #633478 is a reply to message #633477] |
Thu, 19 February 2015 08:15 |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
|
Senior Member |
|
|
|
I must admit to having been guilty of failing to notice the date of a post occasionally. Like you say, when they bubble to the top of a Google search, I assume (wrongly) that they're more current than they actually are.
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #633483 is a reply to message #633478] |
Thu, 19 February 2015 08:57 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Except anything specifically related to the version, I wouldn't mind to answer a newbie's question if it exactly related to the original question.
I would of course, expect it to be a new question if the post is related to a different version compared to the original question. The basic problem while a newbie posts a new question on a zombie thread is, there is no version specified.
In this thread, the original question was posted almost 7 years ago. The latest Oracle version then was obviously not what it is now. Therefore, it creates a big confusion. Google obviously doesn't take care of all this and might just show a zombie thread on top. It is only upto us to decide whether it should be a new question or not.
|
|
|
|
|
|
|
|
|
|
| Re: Changing SGA_MAX_SIZE [message #633540 is a reply to message #633477] |
Fri, 20 February 2015 16:43 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
cookiemonster wrote on Thu, 19 February 2015 07:59It's not like google treats them as though they're archaeological artefacts.
Whenever I enter something oracle related into google it almost always displays some really old forum posts on the first page.
Ah. I didn't consider the google factor.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
