difference between alter system and alter database

[vr76413]

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

[LKBrwn_DBA]

If you don't know the difference, better do not use them!!!!

[stecal]

Have you read the syntax definition for each?

[davey23uk]

learn the difference an instance and a database - you will be half way there then

[dbaforlife]

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.

[Bore]

"The settings stay in effect as long as the database is mounted."
That one is cool :-) Have you heard about SPFILE?

[cis_groupie]

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.

[simply_dba]

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?

[Bore]

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"
:-)