About Career Partners Events Contact Management Site Map   
 
 
 
 
   
  Oracle 11i Financials    
  Oracle 10g DBA    
  
   
   
   
   
   
   
Interview Question (Oracle)
   
   
 
 
 
 
 
 
 
         
 

Have you been thinking about switching jobs and are uncertain about that technical interview process? Try taking the 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, 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. Moreover if you want to be more expert, you may think of BASE Limited for your DBA training. Good Luck.

     
   
 

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  
       
   
       
 

6.

Explain the difference between a hot backup and a cold backup and the benefits associated with each  
 

7.

You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?  
 

8.

Explain the difference between a data block, an extent and a segment.  
 

9.

Give two examples of how you might determine the structure of the table DEPT.  
 

10.

Where would you look for errors from the database engine?  
 

11.

Compare and contrast TRUNCATE and DELETE for a table.  
 

12.

Give the reasoning behind using an index.  
 

13.

Give the two types of tables involved in producing a star schema and the type of data they hold.  
 

14.

What type of index should you use on a fact table?  
 

15.

Give two examples of referential integrity constraints.  
 

16.

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?  
 

17.

Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.  
 

18.

What command would you use to create a backup control file?  
 

19.

Give the stages of instance startup to a usable state where normal users may access it.  
 

20.

What column differentiates the V$ views to the GV$ views and how?  
 

21.

How would you go about generating an EXPLAIN plan?  
 

22.

How would you go about increasing the buffer cache hit ratio?  
 

23.

Explain an ORA-01555  
 

24.

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.  
 

25.

How would you determine the time zone under which a database was operating?  
 

26.

Explain the use of setting GLOBAL_NAMES equal to TRUE.  
 

27.

What command would you use to encrypt a PL/SQL application?  
 

28.

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.  
 

29.

Explain the use of table functions.  
 

30.

Name three advisory statistics you can collect.  
 

31.

Where in the Oracle directory tree structure are audit traces placed?  
 

32.

Explain materialized views and how they are used.  
 

33.

When a user process fails, what background process cleans up after it?  
 

34.

What background process refreshes materialized views?  
 

35.

How would you determine what sessions are connected and what resources they are waiting for?  
 

36.

Describe what redo logs are.  
 

37.

How would you force a log switch?  
 

38.

Give two methods you could use to determine what DDL changes have been made.  
 

39.

What does coalescing a tablespace do?  
 

40.

What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?  
 

41.

Name a tablespace automatically created when you create a database.  
 

42.

When creating a user, what permissions must you grant to allow them to connect to the database?  
 

43.

How do you add a data file to a tablespace?  
 

44.

How do you resize a data file?  
 

45.

What view would you use to look at the size of a data file?  
 

46.

What view would you use to determine free space in a tablespace?  
 

47.

How would you determine who has added a row to a table?  
 

48.

How can you rebuild an index?  
 

49.

Explain what partitioning is and what its benefit is.  
 

50.

You have just compiled a PL/SQL package but got errors, how would you view the errors?  
 

51.

How can you gather statistics on a table?  
 

52.

How can you enable a trace for a session?  
 

53.

What is the difference between the SQL*Loader and IMPORT utilities?  
 

54.

Name two files used for network connection to a database.  
 

56.

What is a mutating table error and how can you get around it?  
 

57.

Describe the use of %ROWTYPE and %TYPE in PL/SQL  
 

58.

Describe the use of PL/SQL tables  
 

59

When is a declare statement needed ?  
 

60.

In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?  
 

61.

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?  
 

62.

How can you find within a PL/SQL block, if a cursor is open?  
 

63.

How can you generate debugging output from PL/SQL?  
 

64.

What are the types of triggers?  
 

65.

Give one method for transferring a table from one schema to another:  
 

66.

What is the purpose of the IMPORT option IGNORE? What is it?s default setting?  
 

67.

You have a rollback segment in a version 7.2 database that has expanded beyond optimal, how can it be restored to optimal?  
 

68.

If the DEFAULT and TEMPORARY tablespace clauses are left out of a CREATE USER command what happens? Is this bad or good? Why?  
 

69.

  What are some of the Oracle provided packages that DBAs should be aware of?  
 

70.

What happens if the constraint name is left out of a constraint clause?  
 

71.

What happens if a tablespace clause is left off of a primary key constraint clause?  
 

72.

What is the proper method for disabling and re-enabling a primary key constraint?  
 

73.

What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause?  
 

74.

When should more than one DB writer process be used? How many should be used?  
 

75.

You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?  
 

76.

What causes the "snapshot too old" error? How can this be prevented or mitigated?  
 

77.

How can you tell if a database object is invalid?  
 

78.

A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?  
 

79.

A developer is trying to create a view and the database won?t let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege and SELECT grants on the tables he is using, what is the problem?  
 

80.

If you have an example table, what is the best way to get sizing data for the production table implementation?  
 

81.

How can you find out how many users are currently logged into the database? How can you find their operating system id?  
 

82.

How can you determine if an index needs to be dropped and rebuilt?  
 

83.

How can variables be passed to a SQL routine?  
 

84.

A tablespace has a table with 30 extents in it. Is this bad? Why or why not.  
 

85.

How do you set up tablespaces during an Oracle installation?  
 

86.

You see multiple fragments in the SYSTEM tablespace, what should you check first?  
 

87.

What are some indications that you need to increase the SHARED_POOL_SIZE parameter?  
 

88.

What is the general guideline for sizing db_block_size and db_multi_block_read for an application that does many full table scans?  
 

89.

What is the fastest query method for a table?  
 

90.

Explain the use of TKPROF? What initialization parameter should be turned on to get full TKPROF output?  
 

91.

When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?  
 

92.

When should you increase copy latches? What parameters control copy latches?  
 

93.

Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?  
 

94.

Describe hit ratio as it pertains to the database buffers. What is the difference between instantaneous and cumulative hit ratio and which should be used for tuning?  
 

95.

Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?  
 

96.

When looking at the estat events report you see that you are getting busy buffer waits. Is this bad? How can you find what is causing it?  
 

97.

If you see contention for library caches how can you fix it?  
 

98.

If a tablespace has a default pctincrease of zero what will this cause (in relationship to the smon process)?  
 

99.

If you see statistics that deal with "undo" what are they really talking about?  
 

100.

How can you tell if a tablespace has excessive fragmentation?  
 

101.

If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?  
 

102.

If you see the value for reloads is high in the estat library cache report is this a matter for concern?  
 

103

You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?  
 

104

You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?  
 

105

You see multiple extents in the temporary tablespace. Is this a problem?  
 

106

A user selects from a sequence and gets back two values, his select is:
SELECT pk_seq.nextval FROM dual;
What is the problem?
 
     
 
Comments, Suggestions, Requests: info@baseltd.com Cell:+8801713144220.
Copyright; 2006 @ BASE Limited.