DBA > Interview Resource

DataBase Administrator (DBA) Interview Questions and Answers

Part:   1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40  41  42  43  44  45  46  47  48  49  50  51 

What is a DBA?

A DBA is a Database Administrator, and this is the most common job that you find a database specialist doing. There are Development DBAs and Production DBAs.
A Development DBA usually works closely with a team of developers and gets more involved in design decisions, giving advice on performance and writing good SQL.
That can be satisfying at a human level because you are part of a team and you share the satisfaction of the team's accomplishments.
A Production DBA (on the other hand) is responsible for maintaining Databases within an organisation, so it is a very difficult and demanding job. He or she, often gets involved when all the design decisions have been made, and has simply to keep things up and running.
Therefore, of course, it is also a rewarding job, both financially and in terms of job satisfaction. But it's a more 'lonely' job than being a Development DBA.


1. What DBA activities did you to do today?

This is a loaded question and almost begs for you to answer it with "What DBA activities do you LIKE to do on a daily basis?." And that is how I would answer this question. Again, do not get caught up in the "typical" day-to-day operational issues of database administration. Sure, you can talk about the index you rebuilt, the monitoring of system and session waits that were occurring, or the space you added to a data file, these are all good and great and you should convey that you understand the day-to-day operational issues. What you should also throw into this answer are the meetings that you attend to provide direction in the database arena, the people that you meet and talk with daily to answer adhoc questions about database use, the modeling of business needs within the database, and the extra time you spend early in the morning or late at night to get the job done. Just because the question stipulates "today" do not take "today" to mean "today." Make sure you wrap up a few good days into "today" and talk about them. This question also begs you to ask the question of "What typical DBA activities are performed day to day within X Corporation?"


2. What are the different modes of mounting a Database with the Parallel Server?

Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.


3. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode?

Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.


4. Do you consider yourself a development DBA or a production DBA and why?

You take this as a trick question and explain it that way. Never in my database carrier have I distinguished between "development" and "production." Just ask your development staff or VP of engineering how much time and money is lost if development systems are down. Explain to the interviewer that both systems are equally important to the operation of the company and both should be considered as production systems because there are people relying on them and money is lost if either one of them is down. Ok you may be saying, and I know you are, that we lose more money if the production system is down. Ok, convey that to the interviewer and you won't get anyone to disagree with you unless your company sells software or there are million dollar deals on the table that are expecting the next release of your product or service.

5. What are the Large object types suported by Oracle?

Answer1
1)bfile - Up to 4 gigabytes –> File locators that point to a read-only binary object outside of the database
2)blob - Up to 4 gigabytes. –> LOB locators that point to a large binary object within the database
3)clob - Up to 4 gigabytes. –> LOB locators that point to a large character object within the database
4)nclob - Up to 4 gigabytes. –>LOB locators that point to a large NLS character object within the database


Answer2
These are the large object type supported bye oracle
• CLOB and LONG for large fixed-width character data
• NCLOB for large fixed-width national character set data
• BLOB and LONG RAW for storing unstructured data
• BFILE for storing unstructured data in operating system files


6. Diffrence between a “where” clause and a “having” claus

Answer1
The order of the clauses in a query syntax using a GROUP BY clause is as follows:
select …where..group by…having…order by…
Where filters, group by arranges into groups, having applies based on group by clause. Having is applied with group by clause.

Answer2
In SQL Server, procedures and functions can return values. (In Oracle, procedures cannot directly return a value).
The major difference with a function is that it can be used in a value assignment. Such as:
–system function
Declare @mydate datetime
Set @mydate = getdate()

–user function (where the user has already coded the function)
Declare @My_area
Set @My_area = dbo.fn_getMy_area(15,20)

Answer3
1.”where” is used to filter records returned by “Select”
2.”where” appears before group by clause
3.In “where” we cannot use aggrigate functions like where count(*) >2 etc
4.”having” appears after group by clause
5.”having” is used to filter records returned by “Group by”<
6.In”Having” we can use aggrigate functions like where count(*) >2 etc there are two more


7. Shall we create procedures to fetch more than one record?

Yes. We can create procedures to fetch more than a row. By using CURSOR commands we could able to do that.
Ex:
CREATE OR REPLACE PROCEDURE myprocedure IS
CURSOR mycur IS select id from mytable;
new_id mytable.id%type;
BEGIN
OPEN mycur;
LOOP
FETCH mycur INTO new_id;
exit when mycur%NOTFOUND;
–do some manipulations–
END LOOP;
CLOSE mycur;
END myprocedure;
In this example iam trying to fetch id from the table mytable. So it fetches the id from each record until EOF.

(EXIT when mycur%NOTFOUND-is used to check EOF.
For further informations, refer CURSORS.

(Continued on next part...)

Part:   1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  37  38  39  40  41  42  43  44  45  46  47  48  49  50  51