Modify init.ora without db restart

[chikkodi]

Hi,

I want to test out some changes to the init.ora parameters. But I dont want to bring the database down as many developers are connected to it (its a dev database), Is it possible to do this ? Its alright if the changes are only temporary. I can add them to the init.ora at a more convenient time.

It will be very helpful if anyone can guide me how to do it, because everytime I want to change a parameter, I have to restart the database currently.

Thanks
Neelima

[Shashi]

hey...

u can change init.ora parameter without shuting down database.. u can use ALTER SYSTEM command..

E.G.
ALTER SYSTEM SET SQL_TRACE=TRUE;

but be careful..u can't change the DB_BLOCK_SIZE parameter..once the database created...

You can post me queries on my email...
shashi_lad2000@rediffmail.com

byeeeee

[marist89]

Some parameters can be changed while the database is up, some can not. For a list of the parameters that can be changed:
select name from v$parameter
where issys_modifiable = 'IMMEDIATE'

Otherwise, you will have to bounce your database for the parameter to become effective.

To change a value, use:
alter system set your_parameter_name = new_value;

[chikkodi]

Thank you very much. That works !!

[jmodic]

Originally posted by marist89
Some parameters can be changed while the database is up, some can not. For a list of the parameters that can be changed:
select name from v$parameter
where issys_modifiable = 'IMMEDIATE'

Otherwise, you will have to bounce your database for the parameter to become effective.

The above query should be changed to:

select name from v$parameter
where issys_modifiable in ('IMMEDIATE', 'DEFERRED')

Changes to parameters with value of 'IMMEDIATE' are visible immediately to all sessions. Changes to parameters with value of 'DEFERRED' are vissible to future sessions, meaning the currently opened sessions will not see the changes until they reconnect.

[kmesser]

There are numerous parameters which are modifiable on a session level without bouncing the database which are not modifiable at the system level without bouncing. To list them:

select * from v$parameter where isses_modifiable = 'TRUE' and issys_modifiable in ('FALSE', 'DEFERRED');

Can you set some of these values at the session level?