Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

First, I browsed through SO for matching questions and didn't find one, but please point me in the right direction if this exact question has already been asked.

I work with and around programmers of various skill levels on various platforms. I would consider my skills to be strong in terms of relational database design, query development, and basic performance tuning and administration. I'm mid-level when it comes to database theory.

My team is looking to me to ensure that we have the best talent on staff, in this case, an engineer experienced in Oracle administration. To me, a well-rounded database administrator, regardless of platform, should also be competent in developing against the database so that is also a requirement.

However my database skills are centralized around SQL Server 200x with experience in a few other products like SAP MaxDB, Access, and FoxPro.

How can I thoroughly assess the skills of an Oracle engineer? I can ask high-level database theory questions and talk about routine tasks that are common across platforms, but I want to dig deep enough that I can be confident in the people I hire. Normally, I would alternate very specific questions that have a right/wrong answer with architectural questions that might have several valid answers.

Does anyone have an interview template, specific questions, or any other knowledge that they can share? Even knowing the meaningful Oracle-related certifications would be a help. Thank you.

EDIT: All the answers have been very helpful so far and I have given upvotes to everyone.

I'm surprised that there are already 3 close votes on this question as "off topic". To be clear, I am specifically asking how a MS SQL Server engineer (like myself) can effectively interview a person with different but symbiotic skills.

The question has already received specific, technical answers which have improved my own database design and programming skills.

If this is more appropriate as a community wiki, please convert it.

share|improve this question
1  
Asking some questions from material in the concepts guide might be useful. All good DBAs will know this stuff. See: download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm –  a'r Feb 18 '11 at 8:54
    
Not sure on good questions, but if you are looking for Oracle Administration the top skill you want restoring. And to be able to restore, one must be able to backup ahead. Everything else is a distant second. –  Shannon Severance Feb 18 '11 at 19:16
add comment

migrated from stackoverflow.com Feb 18 '11 at 22:37

This question came from our site for professional and enthusiast programmers.

7 Answers

up vote 5 down vote accepted

I recently had an Oracle interview that asked to explain %ROWTYPE and %TYPE. IMO, good indication of PLSQL coding experience, besides knowledge.

  • They also asked me to explain
  • I would provide tables & ask for a statement that could work using DISTINCT, GROUP BY, or IN/EXISTS. Someone using EXISTS would rank higher to me, while someone who used DISTINCT in the same statement as a GROUP BY would be a red flag.
  • Ask them how DISTINCT and GROUP BY are different -- because the explain plan is identical, IME
  • Do they know what data pump is? And Flashback?
  • Implicit vs Explicit cursors?
  • JOINs - ANSI-89 vs ANSI-92 syntax, and what the (+) indicates in Oracle
  • PIVOT/UNPIVOT (supported 11g+)
  • COALESCE vs NVL/NVL2, and can NVL be cascaded?
share|improve this answer
    
+1 - I like the autonomous transactions; that's something I can map back to my knowledge of SQL Server's transaction isolation levels. –  Tim Medora Feb 18 '11 at 6:39
    
I'm curious how autonomous transactions maps to SQL Server's transaction isolation levels? Seem like very different concepts to me. –  Shannon Severance Feb 18 '11 at 19:18
    
@Shannon Severance - I guess that was a nebulous comment on my part. The description of autonomous transactions reminds me of a combination of using isolation levels and TransactionScopes in SQL Server/.Net to accomplish reads/writes that are independent/less-dependent on the current transaction being executed. So although not the same thing, @OMG Ponies answer helped me draw an immediate analogy. –  Tim Medora Feb 18 '11 at 20:26
    
What does "gives priority to SELECT statements" mean in this context? I can't think of any way that Oracle prioritizes a SELECT statement. And with multi-version read consistency, there wouldn't appear to be any benefit to doing so since readers don't block writers and writers don't block readers. –  Justin Cave Feb 18 '11 at 22:45
1  
@OMG Ponies - I believe you're describing multi-version read consistency. Readers don't block writers, they select the data as of the SCN the query started. Writers don't block readers because, by definition, they're updating a later version of the data than readers are trying to read. As opposed to other databases where the lock a writer acquires would block a query from accessing the data until the writer was done or a read lock would prevent a writer from updating the row until the query is done. –  Justin Cave Feb 18 '11 at 23:37
show 1 more comment

Asking a candidate to explain multi-version read consistency would be a great place to start. For developers, understanding this is key to understanding how locking happens and why readers don't block writers and writers don't block readers. For administrators, you can dig in a bit further and have them explain how UNDO is used to support multi-version read consistency, how that impacts the sizing of the UNDO tablespace, and the performance implications of queries that require lots of relatively "old" blocks.

Talking about performance tuning in Oracle would also tend to be rather enlightening. Asking a candidate to walk through an AWR (or statspack) report, for example, would generally be rather enlightening both of their level of Oracle knowledge and their general problem-solving skills. Jonathan Lewis's 12-part series on analyzing statspack is a great place to go to get an idea about how a candidate would ideally go about analyzing such a report. It's also a good test of their ability to communicate technical information about Oracle to people that don't necessarily have the same level of Oracle background.

share|improve this answer
add comment

How about asking them to bring examples of their work? What projects have they been involved with? How about asking them to come up with a db design for a simple project (either using UML or showing DDL). SQL tuning is important (give them a poorly written query against a small set of tables, give them the table definitions and some info about the data (num rows, etc), ask them to rewrite). PL/SQL should be strong, have them write a procedure that does some basic tasks (opens a cursor, updates tables committing every x rows, etc). Oracle concepts should be strong (they should know basic internals of the Oracle engine, see concepts guide if you need to, although this can get fairly deep in the weeds).

Basically, what will you be asking this person to do? Give them a simplified version of these scenarios and see what they produce.

Also, IDEs like Toad are very commonly used, so I wouldn't necessarily deny them use of such a tool (against a test db of course). If you expect them to know the exact syntax for all statements, then don't give them access to Toad, but know that most developers are not hand-writing scripts in Textpad ;)

share|improve this answer
1  
"committing every x rows, etc). Oracle concepts should be strong" ?? –  Rob van Wijk Feb 18 '11 at 13:44
    
whats your question Rob? –  tbone Feb 18 '11 at 13:50
1  
That would be incredibly problematic for most applicants as their work would be the owned property of their previous (or current) employer and disclosing it would likely represent an actionable breach. –  Michael Broughton Feb 18 '11 at 14:49
    
App developers show examples all the time. Asking for a few examples (not a full dump of all code of course) of procedures or similar shouldn't be an issue, and will reveal much to an experienced developer. But I'm not lawyer, so DYODD etc. as always. –  tbone Feb 18 '11 at 15:11
    
@Michael Broughton - I'm on the fence with this one...some of my best work can't be shown, but most developers (app or database) have SOME side project that they are particularly proud of or some non-proprietary piece of a project that they can show. Granted, it's probably harder to genericize schemas, queries, and reports than an application. –  Tim Medora Feb 18 '11 at 16:05
add comment

I often find that people that can answer a lot of technical questions are quite useless when it comes to the day to day tasks. First of all a good Oracle Engineer needs the right attitude.

  • know where to get your info
  • good commincation skills
  • team player
  • and more soft skills

I alway prefer to just chat about databases.

  • What are you most proud of?
  • What do you miss in Oracle?
  • What's you favorate tool or technique
  • The future of Cloud computing

You'll get a better impression then ask the some technical questions.

share|improve this answer
add comment

Check out the PL/SQL Challenge . You can get a bunch of random questions at different levels and see how your candidates do against the population of people who took the quiz.

Also ask how they keep their knowledge up to date. See if they answer questions on stackoverflow or the OTN forums, maintain a blog, are a member of a usergroup. And, of course, Google them.

share|improve this answer
add comment

I can throw a couple of Oracle specific questions your way:

  • what is the DUAL table and what is it used for?
  • what is the TNSNAMES.ORA file?
  • how do you export and then reimport an Oracle database?
  • what are Oracle forms?
  • what technology is the Oracle installer built using? (anyone who has installed more than a couple of Oracle instances should know this one)
  • what is the relevance of a schema in Oracle?
  • what is TOAD?
share|improve this answer
add comment

One of my college instructors said that when being interviewed for a teaching position, the interviewer asked her "What is the most difficult thing to teach your students?". She replied with a difficult Law concept (it was for a Law teaching position). Then the interviewer, who mentioned his wife was a lawyer (so he would have some idea what she was talking about), asked her to teach the concept to him. She got the job, but she says he was the best interviewer she'd ever come across.

So what about something like that? Ask them what the most difficult part of database design is or what the most difficult system to design for is? Then tell them to go design and/or implement it. :)

PS: she obviously got the job, since she's my instructor. :P

share|improve this answer
    
+1 - good idea, and good example. thanks. –  Tim Medora Feb 18 '11 at 6:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.