Oracle Interview Questions

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

Download Oracle Interview Questions PDF

Oracle Interview Questions

Changing old and new values in an insert, delete and update triggers

 INSERT : new = new value, old = NULL

 DELETE : new = NULL, old = old value

 UPDATE : new = new value, old = old value

Parts of a database trigger are

  • Trigger statement or event,
  • Trigger restriction and
  • Trigger action

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.

The five query types in Oracle are as follows:

  • Compound queries,
  • Nested queries,
  • Correlated queries,
  • Subqueries
  • Normal queries.

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.

Parameter file is a file having a track of initialization parameters and the matching values. The two types of Oracle supported parameter files are:

  • Initialization parameter file – Text version and
  • Server parameter file – Binary version

Based on individual installations users can specify their individual initialization parameters.

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.

Triggers giving Update/Insert to the table possessing referential integrity constraint on the triggering table are termed as constraining triggers.

Overloading happens when an object is performing various functions based on the number or data types of the parameters passed through it. Yes, Functions can be overloaded.

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

The different types of rollback segments are:

  • Private Available to particular instance and
  • Public Available to all instances

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

Yes a rollback is possible to any savepoint.