Oracle technical interview questions and answers are crucial for candidates preparing for database-related roles such as Database Developer, SQL Programmer, DBA, or Data Analyst. Oracle is widely used in enterprise applications, so interviewers frequently test a candidate’s concepts in SQL, PL/SQL, joins, indexing, triggers, views, transaction management, and performance tuning. Companies like TCS, Wipro, Infosys, Capgemini, and Cognizant often include Oracle questions in both technical interviews and written tests. This guide covers the most frequently asked Oracle interview questions with clear explanations, practical examples, and beginner-friendly definitions. Whether you are preparing for a campus placement or an experienced-level interview, mastering these Oracle concepts will help you answer confidently. You can also download Oracle interview PDFs and practice company-wise mock tests to enhance your preparation.
Database professionals can deepen their Oracle expertise by studying PL/SQL programming and understanding DBMS concepts thoroughly
Showing 10 of 113 questions
51. What are the Characteristics of Data Files ?
A data file can be associated with only one database. Once created a data file cant change size. One or more data files form a logical unit of database storage called a tablespace.
52. How to define Data Block size ?
A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE data blocks. Block size is specified in INIT.ORA file and cant be changed latter.
53. What does a Control file Contain ?
A Control file records the physical structure of the database. It contains the following information.
Database Name
Names and locations of a databases files and redolog files.
Time stamp of database creation.
54. What is difference between UNIQUE constraint and PRIMARY KEY constraint ?
A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY cant contain Nulls.
55. What is Index Cluster ?
A Cluster with an index on the Cluster Key
56. When does a Transaction end ?
When it is committed or Rollbacked.
57. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command ? What are the factors that affect OPTIMIZER in choosing an Optimization approach ?
Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.
58. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.
59. How does one create a new database? (for DBA)
One can create and modify Oracle databases using the Oracle “dbca” (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software.
One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone.
60. What database block size should I use? (for DBA)
Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your “operating system block size” to be