DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: difference between alter system and alter database

  1. #1
    Join Date
    Oct 2002
    Posts
    284

    difference between alter system and alter database

    Hello guys..

    I am always confused on when to use "ALTER SYSTEM" and when to use "ALTER DATABASE" commands..

    How do i know which one to use ?...any tips ?

    thanks
    ron

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    If you don't know the difference, better do not use them!!!!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    Have you read the syntax definition for each?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    learn the difference an instance and a database - you will be half way there then

  5. #5
    Join Date
    Mar 2006
    Posts
    1

    difference between alter system and alter database

    hi,

    Use the ALTER SYSTEM statement to dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted.

    Use the ALTER DATABASE statement to modify, maintain, or recover an existing database.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    "The settings stay in effect as long as the database is mounted."
    That one is cool :-) Have you heard about SPFILE?

  7. #7
    Join Date
    Jun 2006
    Location
    Wales, UK
    Posts
    62
    Have you heard about SPFILE?
    What dbaforlife said is correct - the changes will stay in effect only as long as the database is mounted. HOWEVER, if you wish to make the changes permanent then the SPFile must be amended.

  8. #8
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    What dbaforlife said is correct - the changes will stay in effect only as long as the database is mounted.
    Wanna have a bet on that one?

    HOWEVER, if you wish to make the changes permanent then the SPFile must be amended.
    How would you amend SPFile without using Alter system?
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  9. #9
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    1) The changest will stay in effect as long as the instance is UP, IF you use scpoe=memory when changing the parameter. The default is BOTH what means it changes the SPFILE as well. I can hardly imagine that you can avoid using SPFILE if you are not doing that by purpose. The default database even creates SPFILE
    2) Mounting the database have nothing to deal with the alter system command and the parameter changing. The changes are in effect until you do not restart the instance if they are done ONLY in memory. If I do
    startup nomount
    alter system.....scope=memory
    the changes will be in effect although the database IS NOT MOUNTED
    3) There is nothing wrong to say "I am wrong, sorry, I just retyped the documentation but looked at an old one"
    :-)

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