Monday, December 31, 2012

These are some interview question and answer asked during my recent
interview. Oracle interview questions are very important during any programming
job interview. Interviewer always want to check how comfortable we are with
any database either we go for Java developer position or C, C++programmer position .So here I have discussed
some basic question related with oracle database. Apart from these questions
which is very specific to Oracle database you may find some general questions
related to database fundamentals and SQL e.g. Difference
between correlated and noncorrelated subquery in databaseor truncate
vs delete in SQL etc. Some of the most important topics in Oracle Interview
questions are SQL, date, inbuilt function, stored procedure and less used
features like cursor, trigger and views. These questions also gives an idea
about formats of questions asked during Oracle Interview.

Answer: This is one of the frequently asked Oracle Interview questions. I
have seen this question every now and then. By the way SYSDATE function
is used in oracle to find current date and time of operating system on which
the database is running return type of function is DATE

Answer: BCP or bulk copy tool
is one type of command line tool for unload data from database came into
existence after oracle 8 .it is used to import or export data from tables and
views but it will not copy structure of data same. Main advantage is fast
mechanism for copying data and we can take backup of important data easily.

Question
6: What are the extensions used by oracle reports

Answer : Oracle reports are used to make business enable to provide
information of all level within or outside in secure way. REP file and RDF
file extensions are used by oracle report.

Question
7: What is Save Points in Oracle database?

Answer : SAVE POINTS are used to divide a transaction into smaller parts.
It enables rolling back part of a transaction. Maximum of five save points are
allowed. Whenever we encounter error we can rollback from the point where we
set our SAVEPOINT.This is useful for multistage transaction and conditional
transaction where commit and rollback depend on certain condition. This is another commonly asked Oracle Interview Question and since save points are also available in other database e.g. SQL Server, some time Interviewer follow up with differences with other database as well.

Question
8: How will you convert string to a date in oracle database?

Answer : This Oracle Interview questions is some time asked as follow up
of previous Oracle Interview questions related to converting date to char in
Oracle. By the wayto_ date
function is used to convert string to a date function.

Answer : This is one of my favorite Oracle
Interview question. Hash cluster is one of the techniques to store the
table in a hash cluster to improve the performance of data retrieval .we apply
hash function on the table row’s cluster key value and store in the hash
cluster. All rows with the same hash key value are stores together on disk.key
value is same like key of index cluster ,for data retrieval Oracle applies the
hash function to the row's cluster key value.

Question
10: What is Snap shot in Oracle database?

Answer : Snapshots are read-only copies of a master table located on a remote
node which is periodically refreshed to reflect changes made to the master
table.

That’s all on this list of Oracle Interview questions and answers.
This can be a good recap before appearing to any programming job interview,
where questions from Oracle database is expected. You may want to prepare some
SQL Interview question as well along with these Oracle questions, as questions
related to SQL query e.g. How
to find second highest salary in SQL or How
to find duplicate records in table is quite popular on various Oracle Interviews.

I think, most popular question on Oracle interviews are, How to find difference between two dates in Oracle database? Can you please provide multiple answers for this question, and also reasoning behind, choosing best way to do that?

I was asked to find number of days between two dates in Java, and questions like find the orders which are more than one months old or 15 days old. As follow-up they asked me how to find number of months between two dates. So, they were pretty detailed and touching different aspect of date and time in Oracle.

1) How do you find MONTH and YEAR from a date in Oracle?Answer : By using EXTRACT function, you can use it to get MONTH and YEAR from a DATE object. By the way, you can also use EXTRACT function to find number of months, years between two dates , as follows :

Extracting YEAR from DATE, and finding number of YEARS between dates :

I was looking for some short Oracle database questions for telephonic round of interview, but didn't find any good list, so end up creating my own.

what is explain plan in oracle?Explain plan describe the execution plan choosen to execute a SQL query by Query Engine.

What is $ORACLE_BASE and where it is defined? $ORACLE_BASE is similar to $JAVA_HOME, it's an environment variable points to thelocation of Oracle installation folder in disk. In Windows, you can defined it under user defined environment variable.

What is index in Oracle Database?An index is a database object which is applied to a column to make searching easy in database. Index is normally used to improve SQL query performance.

How does a Query executes in Oracle? Can you list steps involved?Following steps are involved in parsing SQL query :1) parsing2) binding3) execute4) fetch

What is init.ora file, where does it used, when it is read?what is TOAD?Difference beween segment, schema and table in Oracle database?