OraFAQ Forum: Server Administration » alter parameter dynamically

Home » RDBMS Server » Server Administration » alter parameter dynamically

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

alter parameter dynamically [message #59709]

Thu, 18 December 2003 00:09

Sunny
Messages: 25
Registered: November 2001

Junior Member

Hi

How would I alter the the parameters dynamically..I cannot bring down the database..

sunny

Report message to a moderator

Re: alter parameter dynamically [message #59710 is a reply to message #59709]

Thu, 18 December 2003 01:34

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Hi,

Try this syntax: "ALTER SYSTEM SET parameter=value;". Look at this example:

SQL> show parameter open_cursors
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     255

SQL> alter system set open_cursors = 300;

System altered.

SQL> show parameter open_cursors
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

Please note that some parameters cannot be altered on-line, and that you may have to shut down your database.

Best regards.

Frank

Report message to a moderator

Re: alter parameter dynamically [message #59713 is a reply to message #59710]

Thu, 18 December 2003 02:46

Sunny
Messages: 25
Registered: November 2001

Junior Member

hi

Thanks frank...

how could i know which all parameter are dinamically changable and which are not.Is there any sql statement which will give me the information

Report message to a moderator

Re: alter parameter dynamically [message #59717 is a reply to message #59713]

Thu, 18 December 2003 03:29

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Hi,

Try the following queries:

tti 'Parameters that can be modified on-line:'

SELECT name, issys_modifiable
FROM sys.v_$parameter
WHERE issys_modifiable != 'FALSE';

tti 'Parameters that cannot be modified on-line:'

SELECT name, issys_modifiable
FROM sys.v_$parameter
WHERE issys_modifiable = 'FALSE';

Best regards.

Frank

Report message to a moderator

Re: alter parameter dynamically [message #59718 is a reply to message #59717]

Thu, 18 December 2003 03:54

Sunny
Messages: 25
Registered: November 2001

Junior Member

thnks frnak

when i executed(1st) statment what u said..it gave me an ouput in which the status is showing FALSE and for some it is showing deferedand some it is showing as immediate..

could u please elaborate on these...

regards
sujith

Report message to a moderator

Re: alter parameter dynamically [message #59719 is a reply to message #59718]

Thu, 18 December 2003 04:01

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Hi,

IMMEDIATE = change takes effect immediately
DEFERRED = change takes effect for subsequent sessions
FALSE = Cannot be altered on-line

For more info, see the "Oracle9i Database Reference Guide".

Best regards.

Frank

Report message to a moderator

Re: alter parameter dynamically [message #59722 is a reply to message #59719]

Thu, 18 December 2003 04:43

Sunny
Messages: 25
Registered: November 2001

Junior Member

thanks frank ..thanks a lot

Report message to a moderator

Previous Topic:	Oracle lisence transfer
Next Topic:	problem with UNION ALL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 06 18:17:39 CST 2025