Practice here the top Oracle Interview Questions and Answers, which are mostly asked during Oracle Job Interviews.
Oracle Interview Questions
- 1) What is Oracle?
- 2) Define Oracle database
- 3) Explain Oracle instance.
- 4) Explain Parameter file in Oracle.
- 5) Explain Oracle’s Server parameter file.
- 6) Explain Oracle’s System Global Area (SGA).
- 7) Explain user account with reference to Oracle.
- 8) Which are the five query types available in Oracle?
- 9) What do you mean by a transaction?
- 10) Differentiate between function and procedure in Oracle.
- 11) Can there be more than one function with a similar name in a PL/SQL block?
- 12) Explain overloading. Can functions be overloaded?
- 13) Give the constructs of a package, function or a procedure.
- 14) Why do you create or replace procedures rather that drop and recreate.
- 15) Explain implicit cursor.
- 16) From the following identify the non schema object: packages, triggers, public synonyms, tables and indexes.
- 17) Does SQL*Plus have a PL/SQL Engine?
- 18) What is the limitation on the block size of PL/SQL?
- 19) How to read/write files from PL/SQL?
- 20) Explain the methods used to protect source code of PL/SQL.
- 21) Give the various exception types.
- 22) List the parts of a database trigger.
- 23) How many types of database triggers exist?
- 24) How would you change old and new values in an insert, delete and update triggers?
- 25) Explain cascading triggers.
Below are the list of Best Oracle Interview Questions and Answers
Oracle is a database server that is used to handle data in a structured manner. It lets its users to retrieve and to store data in a way that multiple users can access similar data at the same time. Oracle achieves this with high efficiency. Many security checks are followed to limit access to authorized users only and to retrieve data in case of accidental data loss.
An Oracle database can be termed as a compilation of data housed in a database server and treated like a large unit.
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.
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.
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.
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.
The five query types in Oracle are as follows:
- Compound queries,
- Nested queries,
- Correlated queries,
- Normal queries.
A group of SQL statements flanked by any 2 ROLLBACK and COMMIT statements is a transaction.
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.
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.
The constructs for a package, function or a procedure are:
- Variables and constants
In order to prevent Grants from getting dropped we create and replace procedures rather than drop and recreate.
It’s a cursor formed by Oracle internally for individual SQL.
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.
As of now a compiled/ parsed block of PL/SQL has a maximum size limitation of 64K and max. code size being 100K. The statement for querying existing procedure or the package size is as follows:
SQL> select * from dba_object_size where name = 'procedure_name'
Oracle 7.3 has a UTL_FILE package included in it which is used to read/ write files. The directory where you want to write to has to be in the INIT.ORA file. Prior to Oracle 7.3, DBMS_OUTPUT with the SQL*Plus SPOOL command was the only way to write a file.
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.
There are two exception types:
- User-defined &
- Pre defined.
Parts of a database trigger are
- Trigger statement or event,
- Trigger restriction and
- Trigger action
There exist 12 different types of database triggers. They are made up of different combinations of:
- Statement and row triggers,
- Before and after triggers,
- Update, delete and insert triggers.
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
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.
Triggers giving SELECT to the table on which they are written are termed as mutating triggers.
Triggers giving Update/Insert to the table possessing referential integrity constraint on the triggering table are termed as constraining triggers.
Clusters reduce the access time for joins and increases it for insert.
In a table, a check condition of a column can reference another column in the same table thus providing self referential integrity.
The various rollback segment states are:
- Needs recovery,
- Partly available,
- Offline and
Yes a rollback is possible to any savepoint.
A table can have maximum 254 columns.
The & operator signifies that a user input is needed for the PL SQL block variable. The && operator signifies that this variable’s value must be the same as inputted earlier by the user for same variable.
Yes, Parameters can be passed to explicit cursors. A cursor parameter could appear wherever a constant appears in a query.
CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;
The different types of rollback segments are:
- Private Available to particular instance and
- Public Available to all instances
Ceil and Floor are the functions of Oracle and both of them are completely opposite to each other.
- Oracle CEIL function: This function is used to return the smallest value of integer to the greater or equal value of the supplied number. In short, it rounds up the digit to the nearest whole number. Ceil is the short form used for the ceiling.
- Oracle FLOOR function: This function of Oracle is used to round down the value to the nearest whole number. The main purpose of using floor function is to return the value of the highest integer to the greater or equal of the lowest value of the supplied number.
Latest Interview Questions
Laravel Interview Questions
Illegal Interview Questions
Phone Interview Questions
SAP Interview Questions
Web API Interview Questions
Power Bi Interview Questions
Automation Testing Interview Questions
Tailwind CSS Interview Questions
Spring Interview Questions
Ruby on Rails Interview Questions
Digital marketing Interview Questions
Behavioral Interview Questions
Thermodynamics Interview Questions
React Js Interview Questions
Hibernate Interview Questions
Microservices Interview Questions
Rest API Interview Questions
Front End Developer Interview Questions
Active Directory Interview Questions
PowerShell Interview Questions
ADO.Net Interview Questions
Entity framework interview questions
Sinatra Framework Interview Questions
Python Pandas Interview Questions
TensorFlow Interview Questions
Python Pyramid Interview Questions
Python Interview Questions
Python Flask Interview Questions
OpenCV Interview Questions
Devops Interview Questions
Subscribe Our NewsLetter
Never Miss an Articles from us.