Home » RDBMS Server » Server Administration » RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc.
RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146685] Sat, 12 November 2005 06:39 Go to next message
raquelframe
Messages: 7
Registered: November 2005
Location: Republic of Panama
Junior Member
I am installing Enterprize Manager. I need to alter shared_pool_size, session_cached_cursors and aq_tm__processes. In the past I altered the init.ora file but now in 10g it seems not to work. How to I alter those parameters.

Thanks

Raquel

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146688 is a reply to message #146685] Sat, 12 November 2005 06:54 Go to previous messageGo to next message
raquelframe
Messages: 7
Registered: November 2005
Location: Republic of Panama
Junior Member
I was able to alter shared_pool_size to 136314880 by loggin into the RDBMS as system sysdba with sql and entered the following command:

alter system set shared_pool_size = 136314880;

success

not able to set session_cached_cursors = 200
not able to set aq_tm_processes = 1

Where are these sets.

Thanks

Raquel

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146699 is a reply to message #146688] Sat, 12 November 2005 09:50 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Type sho parameter spfile, if the value is non null, then you are using a server parameter file (this is the default for 10g). In order to alter the parameters that you have listed you must set the scope to spfile then restart the database.

Alter system set session_cached_cursors = 200 scope=spfile;


HTH
Jim

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146700 is a reply to message #146685] Sat, 12 November 2005 12:00 Go to previous messageGo to next message
raquelframe
Messages: 7
Registered: November 2005
Location: Republic of Panama
Junior Member
ran Alter system set session_cached_cursors = 200 scope=spfile;
as system as sysdba
the set was successful but I keep getting a message from Enterprize Manager installation to alter session_cached_cursors = 200

Thanks

Raquel

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146701 is a reply to message #146685] Sat, 12 November 2005 12:06 Go to previous messageGo to next message
raquelframe
Messages: 7
Registered: November 2005
Location: Republic of Panama
Junior Member

in sql ran show parameter session_cached_cursors;
recieved 20
in sql ran alter session set session_cached_cursors=200;
as system@orcl as sysdba
in sql ran show parameter session_cached_cursors;
as system@orcl as sysdba
recieved 200

Raquel

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146804 is a reply to message #146701] Mon, 14 November 2005 00:23 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
After the command

Alter system set session_cached_cursors = 200 scope=spfile;

did you refreshed the database by restarting it?

Whenever the scope is spfile, you need to bounce the database in order to make that setting effective.

-Girish

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146941 is a reply to message #146685] Mon, 14 November 2005 16:58 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
If you are running with a spfile<sid>.ora rather than a pfile<sid>.ora you can set many parameters dynamiclly(while it is running) and let the database dynamiclly set itself by using SGA_TARGET and SGA_MAX_SIZE. When you set these your database adjusts itself and you no longer have to manually set DB_CACHE_SIZE, SHARED_POOl_SIZE, LARGE_POOL_SIZE and JAVA_POOL_SIZE. You still need to set log_buffer, keep, recycle, streams pool, and other block sizes. I set OPEN_CURSORS to 600.

You also must set SATISTICS_LEVEL to TYPICAL (or ALL?)

You can make a spfile by logging in as sys / as sysdba and issuing:
Create spfile from pfile = '$ORACLE_HOME/dbs/init<sid>.ora';

Then rename $ORACLE_HOME/dbs/init<sid>.ora to something like $ORACLE_HOME/dbs/old-init<sid>.ora so it will not be used at startup, then bounce the database.

Don't ever edit, view, cat, more, ... or otherwise touch the spfile!!!
You alter it via the alter database, alter system, and other indirect methods.

Report message to a moderator

Re: RDBMS 10g alter Shared_Pool_Size, Session_Cached_Cursors, etc. [message #146989 is a reply to message #146941] Tue, 15 November 2005 02:32 Go to previous message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hi
Just to clarify.
Quote:

Then rename $ORACLE_HOME/dbs/init<sid>.ora to something like $ORACLE_HOME/dbs/old-init<sid>.ora so it will not be used at startup

Not actually necessary as the DB will automatically look for an spfile to use first. If one is present (in the appropriate location) then it will not use a pfile (unless you have set that as a parameter in the startup command).

Jim

Report message to a moderator

Previous Topic: SQL behaving differently on different RAC Node
Next Topic: Partitionning strategy - Row movement b'cos of Update on key partition
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 13 17:33:35 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.