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.
No comments:
Post a Comment