Oracle DBA – A lifelong learning experience

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 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.

If you get the answer right then thank Uncle John. If wrong then you only have yourself to blame.

6 Responses to “Alter system v Alter database – which to guess at”

  1. joel garry said

    I’ll think of you the next time I use alter system suspend.

    • John Hallas said

      There is always something to prove me wrong Joel
      Not sure that I would know when I would ever use that command or the quiece comand either.


      • joel garry said

        So from this I am to conclude you’ve never done split mirror backups or reinstalled a repository with emca?

        I don’t mean to pick on you, I just have a general problem with all the “interview question” books and blogs, mostly because I’ve been interviewed by some real doofuses at times, and I’m not convinced the interview process as done by most places is anywhere close to reasonable for our type of work. I’ve been on both sides of the table, seen some people waaaaay over estimate their own capability of evaluating others. I spent a lot of time as an independent being thrust into random environments with no interview at all, and that has worked best for me. The best jobs have been people dragging me in under the pretext of showing me some cool new tech. I could never remember standby syntax, I always have the attitude, if it isn’t in a script, it hasn’t been reliably retested.

        The most common error I’ve seen is people evaluating interviewees with the expectation that they will be the person who has left. The most common problem is people not fitting in, which brings up the concept of all the pseudo-science HR personality “predictors” like handwriting analysis and fake stressor interviews.

        On an unrelated note – how do you get the icon pictures to work? It doesn’t seem to use my gravitar, which works elsewhere. (I know I could login to the other things like wordpress, I’ve seen gravitar not work in some places so I’m curious where the fail is, especially if mine.)

      • John Hallas said

        Yes I have used BCV’s etc, although quite a few years ago now. We are just getting a lot of new HP 3Par technology which allows database to be snapshotted to disk and I assume that I will be using the commands again.

        I think I said in either my post yesterday or today that I see interviewing being about getting a feel for a person and seeing where he will fit in. This is especially true for permanent staff rather than contractors who are often there to do a piece of work (although invariably stay much longer if they are good). What I hate is the written test type interview and often as a contractor I refused to attend one of them, taking the view that my experience and OCP status should negate the need for a written test. However I will agree that having an OCP does not actually mean you know that much and I do think that some people get others to attend the exam for them as I see no other way that they managed a pass.

        I also made the point that I rarely ask about syntax and I only do so when I feel the person will know the answer anyway.

        I originally uploaded my image to WordPress but then I found and that seems to link in automatically to WordPress so I have had no problems.

        Feel free to continue reading my entries and commenting as you feel fit



  2. joel garry said

    per gravitar, I noticed a developer faq answer there about “In php, try md5(strtolower($email)); (adapt for other languages)” because they use all lowercase email. I’m also posting this while logged in to gravitar – clicking on the gravitar icon next to the email line of the reply, it seems to know I’m logged in, but still doesn’t display here.

    • John Hallas said

      Whatever you are doing Joel doesn’t seem to be working for you as the gravatar on here is still not showing anything.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: