Oracle & Unix Technical Interview
Oracle Technical Interview
By James Koopmann
Have you been thinking about switching jobs and are uncertain about that technical interview process? Try taking my technical interview and see if you are up it.
It seems the job market is starting to move a bit.
If you are uncomfortable in your current position or if you just want to test the waters, it might be time to brush off your resume and hit the road. Before you do, you should also brush up on some of your DBA skills so that you are ready for the dreaded technical interview.
Here is a list of questions I think any true DBA should be able to answer. If you do not know the answers to these questions, not to fear, take a bit of time, research the answers, and then go and hit the road. Some of the questions are about you, your personality and how you conduct yourself.
Other questions are technical in nature concerning Oracle and operating systems. When answering the questions, both when you go through this interview and when you are in a real interview, try to answer completely and include any real-life experiences you have.
Just answering questions with a yes/no or minimum answer does not show a true interest in the field of database administration and how it can impact the company.
Most people want to know how you apply the knowledge and what you have learned from it.
Personal
1. What DBA activities did you to do today?
2. What is your typical day like?
3. What other parts of your organization do you interact with and how?
4. Do you consider yourself a development DBA or a production DBA and why?
5. Are you a nuts-n-bolts DBA or a tools-n-props DBA
Technical - Oracle
1. Explain the difference between a hot backup and a cold backup and the benefits associated
with each
2. You have just had to restore from backup and do not have any control files. How would you go
about bringing up this database?
3. How do you switch from an init.ora file to a spfile?
4. Explain the difference between a data block, an extent and a segment.
5. Give two examples of how you might determine the structure of the table DEPT.
6. Where would you look for errors from the database engine?
7. Compare and contrast TRUNCATE and DELETE for a table.
8. Give the reasoning behind using an index.
9. Give the two types of tables involved in producing a star schema and the type of data they
hold.
10. . What type of index should you use on a fact table?
11. . Give two examples of referential integrity constraints.
12. A table is classified as a parent table and you want to drop and re-create it. How would you
do this without affecting the children tables?
13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the
benefits and disadvantages to each.
14. What command would you use to create a backup control file?
15. Give the stages of instance startup to a usable state where normal users may access it.
16. What column differentiates the V$ views to the GV$ views and how?
17. How would you go about generating an EXPLAIN plan?
18. How would you go about increasing the buffer cache hit ratio?
19. Explain an ORA-01555
20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
21. How would you determine the time zone under which a database was operating?
22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
23. What command would you use to encrypt a PL/SQL application?
24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
25. Explain the use of table functions.
26. Name three advisory statistics you can collect.
27. Where in the Oracle directory tree structure are audit traces placed?
28. Explain materialized views and how they are used.
29. When a user process fails, what background process cleans up after it?
30. What background process refreshes materialized views?
31. How would you determine what sessions are connected and what resources they are waiting
for?
32. Describe what redo logs are.
33. How would you force a log switch?
34. Give two methods you could use to determine what DDL changes have been made.
35. What does coalescing a tablespace do?
36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
37. Name a tablespace automatically created when you create a database.
38. When creating a user, what permissions must you grant to allow them to connect to the
database?
39. How do you add a data file to a tablespace?
40. How do you resize a data file?
41. What view would you use to look at the size of a data file?
42. What view would you use to determine free space in a tablespace?
43. How would you determine who has added a row to a table?
44. How can you rebuild an index?
45. Explain what partitioning is and what its benefit is.
46. You have just compiled a PL/SQL package but got errors, how would you view the errors?
47. How can you gather statistics on a table?
48. How can you enable a trace for a session?
49. What is the difference between the SQL*Loader and IMPORT utilities?
50. Name two files used for network connection to a database.
Technical - UNIX
1. How do you list the files in an UNIX directory while also showing hidden files?
2. How do you execute a UNIX command in the background?
3. What UNIX command will control the default file permissions when files are created?
4. Explain the read, write, and execute permissions on a UNIX directory.
5. What is the difference between a soft link and a hard link?
6. Give the command to display space usage on the UNIX file system.
7. Explain iostat, vmstat and netstat.
8. How would you change all occurrences of a value using VI?
9. Give two UNIX kernel parameters that effect an Oracle install
10. Briefly, how do you install Oracle software on UNIX.
I hope that these interview questions were not too hard. Remember these are "core" DBA
questions and not necessarily related to the Oracle options that you may encounter in some
interviews.
Take a close look at the requirements for any job and try to extract questions that
they may ask from manuals and real life experiences.
For instance, if they are looking for a DBA to run their databases in RAC environments, you should try to determine what hardware and software they are using BEFORE you get to the interview.
This would allow you to brush up on particular environments and not be caught off-guard. Good luck!
By James Koopmann
Have you been thinking about switching jobs and are uncertain about that technical interview process? Try taking my technical interview and see if you are up it.
It seems the job market is starting to move a bit.
If you are uncomfortable in your current position or if you just want to test the waters, it might be time to brush off your resume and hit the road. Before you do, you should also brush up on some of your DBA skills so that you are ready for the dreaded technical interview.
Here is a list of questions I think any true DBA should be able to answer. If you do not know the answers to these questions, not to fear, take a bit of time, research the answers, and then go and hit the road. Some of the questions are about you, your personality and how you conduct yourself.
Other questions are technical in nature concerning Oracle and operating systems. When answering the questions, both when you go through this interview and when you are in a real interview, try to answer completely and include any real-life experiences you have.
Just answering questions with a yes/no or minimum answer does not show a true interest in the field of database administration and how it can impact the company.
Most people want to know how you apply the knowledge and what you have learned from it.
Personal
1. What DBA activities did you to do today?
2. What is your typical day like?
3. What other parts of your organization do you interact with and how?
4. Do you consider yourself a development DBA or a production DBA and why?
5. Are you a nuts-n-bolts DBA or a tools-n-props DBA
Technical - Oracle
1. Explain the difference between a hot backup and a cold backup and the benefits associated
with each
2. You have just had to restore from backup and do not have any control files. How would you go
about bringing up this database?
3. How do you switch from an init.ora file to a spfile?
4. Explain the difference between a data block, an extent and a segment.
5. Give two examples of how you might determine the structure of the table DEPT.
6. Where would you look for errors from the database engine?
7. Compare and contrast TRUNCATE and DELETE for a table.
8. Give the reasoning behind using an index.
9. Give the two types of tables involved in producing a star schema and the type of data they
hold.
10. . What type of index should you use on a fact table?
11. . Give two examples of referential integrity constraints.
12. A table is classified as a parent table and you want to drop and re-create it. How would you
do this without affecting the children tables?
13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the
benefits and disadvantages to each.
14. What command would you use to create a backup control file?
15. Give the stages of instance startup to a usable state where normal users may access it.
16. What column differentiates the V$ views to the GV$ views and how?
17. How would you go about generating an EXPLAIN plan?
18. How would you go about increasing the buffer cache hit ratio?
19. Explain an ORA-01555
20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
21. How would you determine the time zone under which a database was operating?
22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
23. What command would you use to encrypt a PL/SQL application?
24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
25. Explain the use of table functions.
26. Name three advisory statistics you can collect.
27. Where in the Oracle directory tree structure are audit traces placed?
28. Explain materialized views and how they are used.
29. When a user process fails, what background process cleans up after it?
30. What background process refreshes materialized views?
31. How would you determine what sessions are connected and what resources they are waiting
for?
32. Describe what redo logs are.
33. How would you force a log switch?
34. Give two methods you could use to determine what DDL changes have been made.
35. What does coalescing a tablespace do?
36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
37. Name a tablespace automatically created when you create a database.
38. When creating a user, what permissions must you grant to allow them to connect to the
database?
39. How do you add a data file to a tablespace?
40. How do you resize a data file?
41. What view would you use to look at the size of a data file?
42. What view would you use to determine free space in a tablespace?
43. How would you determine who has added a row to a table?
44. How can you rebuild an index?
45. Explain what partitioning is and what its benefit is.
46. You have just compiled a PL/SQL package but got errors, how would you view the errors?
47. How can you gather statistics on a table?
48. How can you enable a trace for a session?
49. What is the difference between the SQL*Loader and IMPORT utilities?
50. Name two files used for network connection to a database.
Technical - UNIX
1. How do you list the files in an UNIX directory while also showing hidden files?
2. How do you execute a UNIX command in the background?
3. What UNIX command will control the default file permissions when files are created?
4. Explain the read, write, and execute permissions on a UNIX directory.
5. What is the difference between a soft link and a hard link?
6. Give the command to display space usage on the UNIX file system.
7. Explain iostat, vmstat and netstat.
8. How would you change all occurrences of a value using VI?
9. Give two UNIX kernel parameters that effect an Oracle install
10. Briefly, how do you install Oracle software on UNIX.
I hope that these interview questions were not too hard. Remember these are "core" DBA
questions and not necessarily related to the Oracle options that you may encounter in some
interviews.
Take a close look at the requirements for any job and try to extract questions that
they may ask from manuals and real life experiences.
For instance, if they are looking for a DBA to run their databases in RAC environments, you should try to determine what hardware and software they are using BEFORE you get to the interview.
This would allow you to brush up on particular environments and not be caught off-guard. Good luck!
can you provide answers to some of the questions..