Alter system v Alter database – which to guess at
Posted by John Hallas on March 7, 2012
I have been doing a lot of interviewing recently and I have a set of questions which are quite generic but I like to use them to lead into areas where I can probe a bit deeper if the interviewee allows me to get that far by answering the question correctly in the first place.
It struck me the other day that almost everyone almost guesses as to whether a SQL command begins with alter system or alter database if they do not know the exact syntax. Now don’t get me wrong, I am not interested in asking OCP type questions and wanting the exact syntax of a command but I like the candidate to be able to show he might have used it in the past. A good example of this is when talking about DataGuard and starting recovery on the standby side. If I am getting a warm feeling that the person might have actually done this before, which does not always align with what the CV might suggest, then I might ask if they can remember the exact syntax to use. I would be looking for some of the keywords in ‘alter database recover managed standby database using current logfile disconnect’ – not something that trips of the tongue easily.
Another example is enabling flashback – is that an alter system or an alter database command. It struck me last night that there must be a way of being able to make an educated guess as to which it is.
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
DATABASE statement to modify, maintain, or recover an existing database.
The description for alter system is :- Use the
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.
If you get the answer right then thank Uncle John. If wrong then you only have yourself to blame.