Oracle Interview Questions

Have you prepared for your interview, can you answer these basic Interview questions on Oracle.

Best Oracle Interview Questions and answers online

#1 Does SQL*Plus have a PL/SQL Engine?

No. SQL*Plus doesn’t contain PL/SQL engine, unlike Oracle Forms. Because of which all PL/SQL is sent to the database engine to get executed which increases the efficiency. Each SQL statement is individually sent to the database and not stripped off.

#2 Explain Oracle’s System Global Area (SGA).

It is the memory area which contains shared data like SQL statements shared pool and buffer cache, between all users. As soon as an Oracle database instance starts the SGA is allocated. Value changes become effective during subsequent startup.

#3 Define Oracle database

An Oracle database can be termed as a compilation of data housed in a database server and treated like a large unit.

#4 From the following identify the non schema object: packages, triggers, public synonyms, tables and indexes.

#5 Differentiate between function and procedure in Oracle.

A function is used to return a single value whereas a procedure doesn’t return any value. It returns multiple variables. This is achieved by passing variables by reference through OUT parameter.

#6 Which are the five query types available in Oracle?

The five query types in Oracle are as follows:

  • Compound queries,
  • Nested queries,
  • Correlated queries,
  • Subqueries
  • Normal queries.
Also Prepare: Frequently Asked OrientDB interview questions

#7 Can there be more than one function with a similar name in a PL/SQL block?

#8 Explain cascading triggers.

When a triggered is fired due to a stamen in another trigger body then the triggers are termed as cascading triggers. There can be a maximum of 32 cascading triggers.

#9 Explain the methods used to protect source code of PL/SQL.

Source codes of PL/SQL V2.2 made available with Oracle 7.2 are protected by implementing the binary wrapper. A stand-alone function does this by transforming the source code of PL/SQL to a portable binary object code. Thus the software can be distributed without the proprietary methods and algorithms getting exposed. Such scripts can still be understood and executed by SQL*DBA and SQL*Plus. The only precaution to be taken is that “decode” command shouldn’t be available.

#10 Give the various rollback segment states.

The various rollback segment states are:

  • Invalid,
  • Needs recovery,
  • Partly available,
  • Offline and
  • Online.

#11 Why do you create or replace procedures rather that drop and recreate.

In order to prevent Grants from getting dropped we create and replace procedures rather than drop and recreate.

#12 Explain Oracle instance.

Each operating Oracle database is associated with an Oracle instance. As soon as a database server starts the database, it is assigned a memory area by Oracle called System Global Area (SGA) and starts one or more than one processes. The SGA and Oracle processes together are referred to as an Oracle instance. The process and memory of an instance are used to efficiently handle data used by multiple users.

#13 Explain user account with reference to Oracle.

Every user is given particular attributes identified by a username termed as a user account. The below can be incorporated into the user attributes:

  • Passwords to access database,
  • Roles and privileges,
  • Default tablespace containing database objects and
  • Default temporary tablespace facilitating query handling workspace.

#14 Explain mutating triggers.

Triggers giving SELECT to the table on which they are written are termed as mutating triggers.

#15 Can a parameter be passed to a cursor?

Yes, Parameters can be passed to explicit cursors. A cursor parameter could appear wherever a constant appears in a query.

Example:

CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
Ask a Question