Modify init.ora without db restart
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Modify init.ora without db restart

  1. #1
    Join Date
    Jan 2001
    Posts
    216
    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

  2. #2
    Join Date
    Oct 2000
    Location
    US
    Posts
    7
    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

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Jan 2001
    Posts
    216
    Thank you very much. That works !!

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    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?
    Oracle DBA and Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width