Prepare for your Oracle job interview with these 30 essential Oracle interview questions covering basic, intermediate, and advanced topics. Ideal for freshers, candidates with 1-3 years, and 3-6 years of experience in Oracle database administration and development. Questions progress from conceptual basics to practical scenarios and advanced Oracle architecture concepts.
Basic Oracle Interview Questions (1-10)
1. What is an Oracle Database?
An Oracle Database is a Relational Database Management System (RDBMS) that stores data in related tables. It provides scalability, reliability, and features like high availability for handling large data volumes and transactions.
2. Differentiate between VARCHAR and VARCHAR2 in Oracle.
VARCHAR and VARCHAR2 both store variable-length character strings. VARCHAR2 is preferred as it reserves exact space used, while VARCHAR may pad spaces in some scenarios. Use VARCHAR2 for better space efficiency.
3. Describe an Oracle table.
An Oracle table is a database object that stores data in rows and columns, similar to a spreadsheet. Each column has a defined data type, and tables form the basic structure for organizing relational data.
4. Explain the relationship among database, tablespace, and data file in Oracle.
A database contains tablespaces, which are logical storage containers. Tablespaces consist of one or more data files, which are physical files on disk storing the actual data.
5. What are the various Oracle database objects?
Oracle database objects include tables, views, indexes, sequences, synonyms, procedures, functions, packages, and triggers. These objects help organize, access, and manage data efficiently.
6. What is the purpose of the ANALYZE command in Oracle?
The ANALYZE command collects statistics about tables, indexes, and clusters to help the optimizer choose efficient execution plans for SQL queries.
7. What is RAW datatype in Oracle?
RAW datatype stores binary data of variable length up to 2000 bytes. It does not undergo character set conversion, making it suitable for data like encrypted values or binary images.
8. What are aggregate functions in Oracle?
Aggregate functions perform calculations on multiple rows and return a single value, such as SUM, COUNT, AVG, MAX, and MIN. They are used with GROUP BY for summarized data.
9. Explain temporal data types in Oracle.
Temporal data types handle date and time: DATE (date and time), TIMESTAMP (date, time with fractional seconds), INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND for duration calculations.
10. What are the differences between Primary Key and Unique Key?
Primary Key uniquely identifies rows and cannot be NULL or duplicated. Unique Key also prevents duplicates but allows one NULL value. Every table can have one Primary Key but multiple Unique Keys.
Intermediate Oracle Interview Questions (11-20)
11. What types of joins are used in writing subqueries in Oracle?
Subqueries commonly use INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN to combine data from multiple tables within the subquery for complex filtering.
12. What are the two virtual tables available during database trigger execution?
:NEW and :OLD are virtual tables in triggers. :NEW refers to new column values (for INSERT/UPDATE), and :OLD refers to previous values (for UPDATE/DELETE).
13. What is SYSTEM tablespace in Oracle?
SYSTEM tablespace stores core database objects, data dictionary tables, and metadata essential for database operation. It is created automatically during database creation.
14. What is SYSAUX tablespace?
SYSAUX tablespace, introduced in Oracle 10g, stores auxiliary data like AWR statistics, session information, and execution plans, offloading from SYSTEM tablespace.
15. Differentiate between Rule-Based Optimizer (RBO) and Cost-Based Optimizer (CBO).
RBO uses fixed rules without statistics. CBO analyzes statistics to choose the lowest-cost execution plan, providing better performance for complex queries.
16. What is an Explain Plan in Oracle?
Explain Plan shows the execution plan chosen by the optimizer for a SQL statement. Use EXPLAIN PLAN FOR statement to analyze and optimize query performance.
17. When is a Bitmap index used in Oracle?
Bitmap indexes are used for low-cardinality columns in data warehousing environments, storing rowids as bitmaps for efficient queries on columns with few distinct values.
18. When is a B-tree index preferred in Oracle?
B-tree indexes suit high-cardinality columns in OLTP environments, maintaining sorted key values with rowid pointers for fast range scans and equality searches.
19. What is Oracle Flexible Architecture (OFA)?
OFA organizes database files across multiple disks for optimal I/O performance and manageability, following standardized naming and directory structures.
20. What happens if the listener goes down with 100 users connected to the Oracle database?
Existing connections remain active as the listener only handles new connections. Current sessions continue without interruption until they disconnect.
Advanced Oracle Interview Questions (21-30)
21. What are the roles of an Oracle DBA?
Oracle DBA manages database maintenance, backups, recovery, performance tuning, security enforcement, and applies patches for optimal operation.
22. Differentiate between Oracle DBA and Oracle Developer roles.
DBA focuses on backend management like backups and tuning. Developer handles coding, data modeling, PL/SQL procedures, and database object creation.
23. What is AWR in Oracle?
Automatic Workload Repository (AWR) collects performance statistics automatically, enabling analysis of database workload, bottlenecks, and optimization recommendations.
24. Explain ADDM in Oracle.
Automated Database Diagnostic Monitor (ADDM) analyzes AWR data to provide performance diagnostics and actionable recommendations for tuning.
25. What is STATSPACK in Oracle?
STATSPACK generates performance reports from snapshots, helping identify issues before AWR, though AWR offers more automation in modern versions.
26. Describe Oracle Real Application Clusters (RAC).
RAC enables multiple instances to access a single database across clustered servers, providing high availability, scalability, and fault tolerance if one node fails.
27. What are the components of Oracle Data Guard?
Data Guard includes Primary Database, Standby Database, Data Guard Broker for management, Log Transport Services for redo shipping, and Log Apply Services for synchronization.
28. Can you change the database block size after creation in Oracle?
No, database block size cannot be changed after creation. It must be set correctly during database creation based on workload requirements.
29. What are the main steps after manual database creation in Oracle?
After manual creation, execute catalog.sql to create data dictionary views and catproc.sql to install PL/SQL packages and procedures.
30. Scenario: At Zoho, a production Oracle database shows slow queries on a high-cardinality column. What indexing strategy would you recommend, and why?
Recommend a B-tree index for the high-cardinality column in this OLTP scenario at Zoho. B-tree handles frequent inserts/updates and range queries efficiently, unlike bitmap indexes suited for low-cardinality data warehousing.
