In this article, you will explore essential Oracle interview questions that can help you prepare effectively for your next job interview. Whether you’re a fresher or have experience, these questions cover a wide range of topics, including basic concepts, PL/SQL, performance tuning, and more. Let’s dive in!
Oracle Interview Questions with Answers
1. What is Oracle Database?
Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation, designed for efficient data storage and management. It offers scalability, robust security, and performance optimization, making it suitable for enterprise applications and data warehousing. Additionally, it supports multiple data models, including JSON and XML, enhancing its versatility.
2. What is the difference between “database” and “instance” in Oracle?
Database is defined as the set of files that is located on Oracle system. All data in Oracle system is in these database files. An instance consists of the processes and memory areas that Oracle database uses. Together, a database and an instance make up a database system.
3. What are the various ways to enlarge a database in Oracle?
The size of a database is the collective size of the tablespaces that constitute the database. You can enlarge a database in three ways:
- Add a datafile to a tablespace
- Add a new tablespace
- Increase the size of a datafile
4. Which command and package is used to view structure of table?
DESCRIBE command lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure. The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.
5. What is the difference between a data block, an extent and a segment?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
6. What are the advantages of PL/SQL Oracle?
PL/SQL is a Procedural Language extension of Structured Query Language (SQL).Oracle PL/SQL allows you to perform data manipulation operation those are safe and flexible. Advantages of PL/SQL are as follows:
- Procedural language support
- Error handling feature
- Reduces Network traffic
- Portable to any operating system
7. What is the difference between oracle global index and a local index?
A global index is a one-to-many relationship, allowing one index partition to map to many table partition. A local index is a one-to-one mapping between index partition and a table partition. Local indexes allow for a cleaner “divide and conquer” approach for generating fast SQL execution plans.
8. What type of Indexes are available in Oracle?
There are four regular types of Indexes in oracle
- Normal index
- Bitmap indexes
- Function based indexes
- Domain indexes
9. What is the purpose of ANALYZE command in Oracle?
ANALYZE command is used to perform various functions as listed below:
- It helps in identifying migrated and chained rows of the table or cluster
- It helps in validating the structure of the object
- It helps in collecting the statistics about object used by the optimizer
- It helps in deleting statistics used by object from the data dictionary
10. What is DUAL table in Oracle?
The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.
11. Why do we need materialized views in Oracle?
You can use materialized views to increase the speed of queries on very large databases. Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes when an existing materialized view can and should be used to satisfy a request.
12. What is the difference between varchar and varchar2 data types?
Varchar can store upto 2000 bytes and varchar2 can store upto 4000 bytes. Varchar will occupy space for NULL values and Varchar2 will not occupy any space.
13. What are temporal data types in Oracle?
Oracle provides following temporal data types:
- Date Data Type – Different formats of Dates
- TimeStamp Data Type – Different formats of Time Stamp
- Interval Data Type – Interval between dates and time
14. What are different attributes of cursor in Oracle?
- %FOUND – Returns TRUE if the fetch of cursor is executed successfully.
- %NOT FOUND – Returns True if no row was returned
- %ISOPEN – Returns true if the cursor is open
- %ROWCOUNT – Returns the number of rows fetched.
15. What are the different Oracle database objects?
Different database objects are as follows:
- Tables: Tables are the basic unit of data storage in an Oracle Database
- Views: It is a virtual table that does not physically exist
- Indexes: It is a performance-tuning method of allowing faster retrieval of data
- Synonyms: It is used as Alias name for tables
- Sequences: It is used to generate unique numbers
- Tablespace: They are the bridge between certain physical and logical components of the Oracle database
16. What is Bulk Copy feature in Oracle?
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.
17. What is the difference between partial and full backup?
Full backup is the starting point for all other backups and contains all the data in the folders and files that are selected to be backed up. Because the full backup stores all files and folders, frequent full backups result in faster and simpler restore operations.
A partial backup is any operating system backup short of a full backup, taken while the database is open or shut down. The following are all examples of partial database backups: a backup of all datafiles for an individual tablespace.
18. What are the datatypes available in PL/SQL?
There are two types of datatypes in PL/SQL:
- Scalar datatypes: Single values with no internal components
- Composite datatypes: Data items that have internal components that can be accessed individually
19. What are the advantages of differential backup?
A differential backup is a cumulative backup of all changes made since the last full backup, i.e., the differences since the last full backup. The advantage to this is the quicker recovery time, requiring only a full backup and the last differential backup to restore the entire data repository.
20. What is “Snapshot” in Oracle database?
A snapshot is a replica of a target master table from a single point in time. Oracle uses snapshots, also referred to as materialized views, to replicate data to non-master sites in a replicated environment and to cache “expensive” queries in a data warehouse environment.
21. What is the difference between NO DATA FOUND and %NOTFOUND in Oracle?
NO DATA FOUND is an exception which is raised when either an implicit query returns no data, or you attempt to reference a row in the PL/SQL table which is not yet defined. SQL%NOTFOUND, is a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row.
22. What is the difference between stored procedure and function?
Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.
23. What are bind variables in Oracle?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.
24. What is the difference between parameter file and control file?
Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters. Control files record the physical structure of the data files and redo log files. They contain database name, databases location, data files, redo log files and time stamp.
25. What are object views in Oracle?
An object view is a virtual object table. Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables from data – of either built-in or user-defined types – stored in the columns of relational or object tables in the database.
Advanced Oracle Interview Questions with Answers
26. What is the difference between hot and cold backup?
Hot backup is taken when database is running and data is being updated, added, or read by its users but doesn’t handle running transactions. Only database management system (DBMS) facility can be used to take hot backups. Cold backup is taken when database is offline, not accessible for updating and users cannot do anything on the database.. DBMS as well as operating system facilities can be used to take the cold backup.
27. What is the difference between row chaining and row migration?
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle Database stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW.
However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle Database migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block.
28. What are the advantages of object views in Oracle?
- Using object views can lead to better performance. Relational data that make up a row of an object view traverse the network as a unit, potentially saving many round trips
- You can fetch relational data into the client-side object cache and map it into C or C++ structures so 3GL applications can manipulate it just like native structures
- Object views provide a gradual migration path for legacy data
- Object views provide for co-existence of relational and object-oriented applications
- Object views provide the flexibility of looking at the same relational or object data in more than one way
29. What is the difference between PCTFREE and PCTUSED parameter?
The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment.
30. Which goals are achieved using “Snapshot” in Oracle?
You might use a snapshot to achieve one or more of the following goals:
- Ease Network Loads: User load is distributed across multiple database servers
- Mass Deployment: Allows you to rollout a database infrastructure to hundreds or thousands of users.
- Data Subsetting: Snapshots allow you to replicate data based on column- and/or row-level subsetting
- Disconnected Computing: Unlike multimaster replication, snapshots do not require a dedicated network link.
31. Why do we need ROWID snapshots in Oracle?
For backwards compatibility, Oracle supports ROWID snapshots in addition to the default primary key snapshots. A ROWID snapshot is based on the physical row identifiers (ROWIDs) of the rows in a master table. ROWID snapshots should be used only for snapshots based on master tables from an Oracle7 database, and should not be used when creating new snapshots based on master tables from Oracle release 8.0 or greater databases.
32. What is the difference between ARCHIVELOG mode and NOARCHIVELOG mode?
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
33. What is the difference between pre-select and pre-query?
A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically. Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued. Pre-query trigger fires before Pre-select trigger.
34. Which pseudocolumn is used for ordering the results of query in Oracle?
ROWNUM is used to limit the number of rows returned by a query. If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. Using ROWNUM in a query can affect view optimization.
35. What is the difference between LOV and List Item?
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
36. Which of the following privileges are required for path load?
The following privileges are required for a load:
- You must have INSERT privileges on the table to be loaded
- You must have DELETE privileges on the table to be loaded, when using the REPLACE or TRUNCATE option to empty old data from the table before loading the new data in its place
37. What are the benefits of Bigfile Tablespaces?
Advantages of Bigfile Tablespaces are as follows:
- Bigfile tablespaces can significantly increase the storage capacity of an Oracle database.
- Bigfile tablespaces simplify management of datafiles in ultra large databases by reducing the number of datafiles needed. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
- They simplify database management by providing datafile transparency.
38. What are ORACLE PRECOMPILERS?
A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Example: SQLJ for Java
39. What is difference between AD_BUGS and AD_APPLID_PATCHES?
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been patched in the Oracle Applications installation.
AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content then they are considered distinct and this table will therefore hold 2 records.
40. What is the difference between physical and logical backup?
Physical backups are copies of the physical files used in storing and recovering a database. These files include data files, control files, and archived redo logs. Logical backups contain logical data such as tables and stored procedures. You can use Oracle Data Pump to export logical data to binary files, which you can later import into the database.
41. Explain Oracle memory structure?
The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.
42. What is the difference between PFILE and SPFILE?
A PFILE is a Static, text file that initializes the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions).
43. What is the difference between SMON and PMON processes?
SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.
44. Why do we need standby database in Oracle?
A standby database is a database replica created from a backup of a primary database. By applying archived redo logs from the primary database to the standby database, you can keep the two databases synchronized.
A standby database has the following main purposes:
- Disaster protection
- Protection against data corruption
- Supplemental reporting
45. What is the purpose of RMAN in oracle?
Recovery Manager (RMAN) is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies backing up, restoring, and recovering database files. The RMAN environment consists of the utilities and databases that play a role in backing up your data.
46. What are the advantages of Database Duplication?
You can perform the following tasks in a duplicate database:
- Test backup and recovery procedures
- Test an upgrade to a new release of Oracle Database
- Test the effect of applications on database performance
- Create a standby database
- Generate reports
47. What is the purpose of data dictionary in Oracle?
The data dictionary is a repository of metadata (Information about information), about all the information inside the database. This repository is owned by SYS, and is stored principally in the SYSTEM tablespace, though some components are stored in the SYSAUX tablespace. The data dictionary is composed of tables and views.
48. What is a trace file and how is it created?
Trace File are trace (or dump) file that Oracle Database creates to help you diagnose and resolve operating problems. Each server and background process can write an associated trace file. When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files. When an internal error is detected by a process or user process, it dumps information about the error to its trace. This can be used for tuning the database.
49. What is hash cluster and why do we need in Oracle?
Storing a table in a hash cluster is an optional way to improve the performance of data retrieval. A hash cluster provides an alternative to a non-clustered table with an index or an index cluster. Hashing is useful when you have the following conditions:
- Most queries are equality queries on the cluster key
- The tables in the hash cluster are primarily static in size so that you can determine the number of rows and amount of space required for the tables in the cluster
50. What is the difference between direct path and convention path loading?
A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. A direct path load is faster than the conventional path.
Useful Resources:
