Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 2 May 2012

Alter system v Alter database

ALTER SYSTEM is an Instance Level commands generally it applies for running processes, parameters etc where as ALTER DATABASE is a Database Level commands generally it applies to the physical structure of the database. Consider the RAC environment most of our ALTER SYSTEM command local to the instance (ALTER SYSTEM DUMP is an exceptional) and ALTER DATABASE command for the whole database.
Mostly we can use ALTER SYSTEM command when the database status is OPEN while Alter database we can use in MOUNT state.
In the sense of Auditing ALTER DATABSE command cannot be audited where as ALTER SYSTEM can.

In the struggling period of my career I always confused with these two related command. Even today it is difficult to remember related every command at the moment. The obvious ideas is that ALTER SYSTEM allows things to happen to the database whilst it is in use – flush shared pool, set a init.ora parameter,  switch archive log, kill session. They are all either non-database wide or non-intrusive database wide. By that I mean that killing a session is specific to that session and flushing shared pool does not harm everyone connected Let’s look at alter database and see if I can find any anomalies to this theory. The various clauses of startup, recovery, datafile, logfile, controlfile, standby database all fall in line. The only one that sits uncomfortably with this theory is the alter database parallel command. So in short if the situation you do not remember exact syntax then think if it affects every user and session on the database go for ALTER DATABASE, if it looks like it might be specific to a session or non-intrusive across all users then go for ALTER SYSTEM.

Finally Use the ALTER DATABASE statement to modify, maintain, or recover an existing and Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted.

Check my earlier post “Alter system in Oracle” or Alter database options in oracle 9i/10g” for the related command of these two in oracle:


Post a Comment