alter database vs alter system

The obvious idea is that alter database might apply to a whole database and alter system might be applicable to only one instance.

The description for alter database is :- Use the ALTER DATABASE statement to modify, maintain, or recover an existing database.

The description for alter system is :- Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance.
The settings stay in effect as long as the database is mounted.

The key difference seems to be the use of the word dynamically for alter system. 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 (albeit it might affect performance in the short-term).

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 my theory is the alter database parallel command.


So to summarise, if asked and you do not know the answer then figure out if it affects every user and session on the database and 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.