Top 25 Oracle Interview Questions and Answers 2024

Editorial Team

Oracle Interview Questions and Answers

Getting a job or gig should not be a challenging task with enough preparation. This article will look at some of the questions about Oracle that one should expect in an interview. Take a look at the following:

1. What is Oracle?

Oracle is a popular and famous database by the renowned Oracle corporation. It primarily works on relational management concepts, explaining why it is also known as RDMBS. This database serves several functions, mainly data warehousing, online transaction processing, and enterprise grid computing. These are made possible by comprehensive and fully integrated cloud applications and platform services it houses.

2. What Do You Understand by View? How is it Different From a Table?

View stores the results of an SQL query for future reference. It is a user-defined database object that uses a virtual table to store data instead of keeping them physically. This explains why it is usually referred to as a logical table. To answer the second part of the question, views are different from tables in two main ways. One can update and delete tables but not views and, even though a table is capable of holding data, it cannot hold SQL query results like views.

3. Can You Mention the Attributes of a Cursor

A Cursor has four main attributes: %FOUND, % NOT FOUND, % IS OPEN, and %ROWCOUNT. The %FOUND detail usually gives INVALID_CURSOR where the cursor has been declared but closed, NULL if the cursor is open, but the fetch hasn’t occurred yet, TRUE where rows have been fetched, and FALSE when rows are returned. On the other hand, NOT FOUND offers INVALID_CURSOR where the cursor has been declared but closed; NULL if fetch is open only but hasn’t happened yet, FALSE where rows have been successfully fetched, and finally TRUE if fetching hasn’t happened yet. The third one returns TRUE if the cursor is open, while %ROWCOUNT returns fetched rows count.

4. What are Stored Procedures and Functions?

Stored procedures refer to sets of written SQL statements that perform a given task. They can be stored as groups in the database provided they are assigned names. One can share stored procedures with several programs once permission has been granted. On the other hand, functions are subprograms written to perform specific tasks. It is worth noting that these two are different even though they perform similar functions.

5. What are the Differences Between Stored Procedures and Functions, if Any?

Even though stored procedures and functions perform the same role, several differences exist. Stored procedures can return or not return a value, whereas functions must always return a value. The former can also return multiple values, but functions can only produce a single value. Secondly, stored procedures can call functions, whereas functions cannot call stored procedures. Thirdly, functions cannot use DML statements, while stored procedures can include update, delete, and insert statements. Lastly, functions cannot support Try/Catch blocks, whereas stored procedures support exception handling.

6. What Do You Understand by a Trigger?

A trigger is any stored program that can be executed automatically on the occurrence of a given event, an existence that is often achieved via programming. PL/SQL normally supports two types of triggers: row and statement level. A row-level trigger usually goes off only once when a given row is affected by a triggering event. On the other hand, a statement-level trigger is fired when the triggering event affects a table. Unlike the row-level trigger, the number of affected rows doesn’t usually matter.

7. Can You Differentiate DELETE and TRUNCATE Commands?

There are four main differences between these two commands. TRUNCATE is a data definition language describing data and its relationship in a database. In contrast, DELETE is a data manipulation language whose purpose is to delete data in the same database. Secondly,  TRUNCATE usually frees an object’s storage space, unlike the DELETE command. Thirdly, a TRUNCATE command will remove the rows but maintain the table structure. Therefore, it cannot be rolled back, which is possible with the DELETE command. Lastly, the TRUNCATE command is often faster than the latter.

8. Explain RAW Datatype

Raw datatype is a common type of data storage format that stores variable-length binary data or byte strings. Most people often confuse RAW and VARCHAR2, given their close resemblance, though they are entirely different. PL/SQL does not usually recognize RAW, meaning that no conversion can happen when any RAW data is transferred to another system. How then can this data be manipulated? RAW data can either be inserted in a table or queries, explaining why it is a special data type.

9. What Do You Understand by Joins? What are the Different Types of Joins?

Joins are generally used to extract data from several tables through an array of standard columns and conditions. Therefore, a join clause refers to a join operation in relational algebra that combines columns from different tables into a new one. There are six different types of joins: INNER JOIN, SEMI JOIN, ANTI JOIN, EQUI JOIN, CROSS JOINS/CARTESIAN PRODUCT, and OUTER JOIN. In inner joins, only the data common in both tables is selected while semi-joins match rows of two relations, finally showing the matching rows of the relation mentioned on the left side. All the joints mentioned are important and have their purposes.

10. Can You Differentiate Between VARCHAR2 and VARCHAR?

Most people confuse VARCHAR and VARCHAR2 because they store character strings with variable lengths. However, they have two main differences that every database operator or user should note. VARCHAR only keeps characters of up to 2000 bytes, whereas VARCHAR2 is capable of handling twice the capacity-4000 bytes. VARCHAR can hold the space for all defined characters during the declaration process regardless of the number of unused characters, while VARCHAR2 will release this unused space.

11. Can You Tell the Difference Between a Primary and Unique Key?

The primary key normally identifies each table row uniquely, whereas the unique key helps avoid a duplicate value in the column table. There are three main differences between these two keys. First, there can only be one primary key in a given table, whereas there can be multiple unique keys in a table. Secondly, the primary key can never hold a null value, whereas the unique key can do the same for several null values. Lastly, the primary key is often a clustered index, while unique keys are non-clustered indices.

12. What Do You Know about Aggregate Function in Oracle?

Aggregate functions can conduct summary operations on different values, resulting in a single value. They are essential when coding. Some of the aggregate values that one can use are AVG, MIN, MAX, SUM, COUNT, and STDEV. The AVG function returns the average value of a group; MIN returns the lowest value in a set of non-NULL values; MAX returns the maximum value; SUM gives the total sum of a given numeric column while STDEV is used to calculate the standard deviation of the records of rows selected by the SELECT statement.

13. What Do You Know about UNION, UNION ALL, MINUS, and INTERACT?

They are a set of operators meant to help a given user fetch data from two or multiple tables simultaneously, which usually occurs when similar columns and data types are in the source table. However, it is worth mentioning that they all serve different purposes. The UNION operator returns all the rows from the table save for the duplicate rows. On the other hand, UNION ALL returns the rows from both tables as well as the duplicate rows. INTERSECT usually returns only the standard rows in both tables, while MINUS returns rows from only the first table.

14. Define a Database Transaction. How Many TCL Statements are Available in Oracle?

A database transaction results from the execution of a number of SQL statements at a go. Oracle, therefore, introduced translation control statements whose primary purposes are to control the implementation of the SQL statements through a set of statements. There are three TCLs, namely: COMMIT, SAVEPOINT, and ROLLBACK. The first one( COMMIT)  is used to make transactions permanent. ROLLBACK, as the name suggests, rolls back the state of the database to last the commit point, whereas SAVEPOINT specifies a transaction point for the purposes of facilitating a rollback.

15. What is a Database Object?

A database object stores data or data references in databases. Several types of database objects exist, such as views, indexes, stored procedures, constraints, triggers, and tables. Views are virtual tables with rows and columns that can be created by selecting fields from the tables in the database, while indexes have keys built from table or views columns. Stored procedures are savable SQL codes that can be used and reused, while constraints specify rules for the data found in the table.

16. Is it Possible to Save Images in a Database?

Yes. Even though people with no experience with databases may have reservations on this, it is possible to save images in databases in terms of a BLOB. This stands for a binary large object, a data type that holds photos. (Note that there are different types of data types in SQL). It can also store audio, video files, and several binary executables. It is also worth noting that BLOB can hold data of up to 4GB, giving room for large audio and video files.

17. What Do You Understand by a Data Dictionary?

A data dictionary is an important object. It is usually created with the advent of a new database. It is database-specific and contains all the metadata required in a database. These include definitions, names, and attributes on data elements used or found in databases or information systems. It is worth pointing out that this dictionary is always owned by the SYS user and contains a set of read-only tables. The database dictionary, unlike virtual tables, usually is physically stored in the SYSTEM tablespace. They are often detailed to help users navigate through the set database.

18. Explain a Deadlock Situation

A deadlock situation refers to several users waiting for a given data simultaneously even though they cannot access it since they have locked each other, resulting in blocked user sessions. The good news is that Oracle can detect such situations and correct them by rolling back the statement related to the transaction detecting the deadlock. However, one can avoid a deadlock by using LOCK IN SHARE mode, where a user uses a lower isolation level like READ COMMITTED. Conducting different operations in a consistent order all the time also results in queues and not a deadlock.

19. What Do You Know about %ROWTYPE and %TYPE?

These two are some of the most common attributes in PL/SQL capable of inheriting different types of table data defined in a given database. They are generally used to prove data independence and integrity. The %ROWTYPE defines a complete row of records with a similar structure as a table, while the %TYPE is often used to declare a variable that should have a similar data type to a table column. It is worth pointing out that the PL/SQL codes automatically update when any data type or precision changes in the database.

20. Mention Some of the Parameters that Can be Passed through a Stored Procedure

Parameters are generally used to exchange data between different stored procedures and applications calling the stored procedures. Various types of parameters can be passed through a stored procedure. These are IN, OUT, and INOUT. All these should be defined as the procedure gets declared. The IN or input procedures let the caller pass a data value to a stored procedure. In contrast, the INOUT value passes the initial value to a subprogram before giving the caller an updated value.

21. Differentiate a Global and Local Variable

As the name suggests, a global variable gets defined at the beginning of a given program and survives the entire period till the end. They are usually accessed by any methods or procedures within the program itself. In contrast, the local variable is often short-lived and has limited access. It is generally limited to the method or procedure where the variable is declared. It is also accurate to say that these two variables differ in scope. All in all, they must be declared in the declaration section of a package. 

22. Can You Mention the Components of a Physical Database Structure of an Oracle Database

There are three components of a physical database structure in an Oracle database. These are one or more data files, one or multiple control files, and a minimum of two redo log files. Redo log files are the operating system files that Oracle uses to log the transactions performed against the database. A control file in Oracle is a tiny binary file that captures the physical structure of the database. It contains the database name and locations of the relevant data and redoes log files. Lastly, the data files refer to the operating system files that store the data found in the database.

23. What are the Components of a Logical Database Structure in Oracle?

Unlike a physical database structure, the logical database structure comprises two main components: tablespace and the database’s schema objects. The tablespaces are logical units that collectively store all database data. They are usually made up of one or multiple files known as data files that must conform to Oracle’s operating system. The schema object refers to the structure describing how the database is stored. It contains information on functions, views, databases, clusters, and tables.

24. What Do You Know about Hot Backup and Cold Backup in Oracle?

A hot backup, also known as online or dynamic backup, occurs when the database is active. It is performed on a given data set when the database is actively online and normally accessible to users. On the other hand, a cold backup, also known as an offline backup, is a database backup conducted when the database is offline and cannot be accessed for an update. It is worth noting that the latter experiences a downtime since users are often barred from accessing the data.

25. Do You Know the Memory Layers in the Oracle Shared Pool?

Oracle shared pools have two layers: the library cache and the data dictionary cache. The library cache is memory found within the SGA used to store SQL statements. It also caches different PL/SQL objects and statements. The main reason why this cache was introduced was to counter the costliness occasioned by CPU cycles in parsing. The data dictionary cache, also known as the row cache, has internal data structures for an Oracle database. It contains the memory areas that house the dictionary data.

Conclusion

These are some of the standard Oracle Interview questions you are likely to encounter in a related interview. Ensure that you go through them as you work on other interview aspects such as posture, pronunciation, and answering techniques that can help you ace your interview. We wish you all the best in your interview.