Oracle Interview Questions


Here are some Oracle interview questions you can ask your candidates, unlike most of the questions you see online, they don't test the knowledge of syntax, or buzz words, or something specific and trivial that can be easily looked up. I really dislike when people start asking interview questions "what is the name of oracle view...", DBA's don't need to remember it, they should know how to use google to look it up. If you have other questions that could be added to the list, if you have interview questions on other topics, or if oyu have general comments, please let me know.

Question: You have an after insert trigger on some table, you are being asked to add a "truncate table" statement to this trigger to truncate another table in the same schema every time this trigger if fired, can you do it? why or why not?
Answer: No, you can do it, because "truncate table" is a DDL command and not DML command. You can't issue DDL commands in a trigger because they implicitly commit. You can't issue COMMIT statements in a tigger, because you have to be able to rollback the entire original transaction

Question: How do you resize redo logs?
You have to drop and recreate them.

Question: If you have a scanario when you have to store person information and address information. Person can have more than one address and more than one person can live at any address. What is the minimal number of tables to store it without having duplicate data?
This is an example of many to many relationship, you need 3 tables.

Question: What does Oracle node in the RAC environemt write to the voting disk and what happens if it stops writing to the voting disk?
Node write its heartbeat to the voting disks, and if it stops it gets evicted from the cluster.

Question: You have setup a dataguard database. If you add a new tablespace to the primary database, will it be automatically added to the standby database?
It should, if everything is setup correctly. You have to setup STANDBY_FILE_MANAGEMENT to AUTO in the spfile.

Question: If you are not using Recovery catalog with RMAN, where is the RMAN metadata (Information about what was backed up and when) stored?
Target's database control file.

Question: You have a very very large table for which you need to change columns in primary key (assuming the new columns are also unique). You need to drop the old one and create a new one, however, ALTER TABLE ADD PRIMARY KEY takes too long, are there options to speed the operation up?
Yes, because ALTER TABLE ADD PRIMARY KEY.. option doesn't allow you to parallelize the operation. You can separately create a unique index on the columns that will be in primary key with PARALLEL option, and then use that index in ALTER TABLE ADD PRIMARY KEY statement

Question: How can you change an owner of a table?
You can't do it in Orale. You can either issue a statement CREATE TABLE as SELECT * FROM.. or use data pump to expoirt/import