Prepare for your Oracle interview with this comprehensive guide featuring 30 essential questions and answers. Covering basic, intermediate, and advanced topics, these questions target freshers, candidates with 1-3 years of experience, and professionals with 3-6 years in Oracle database administration and development. Each answer provides clear, practical insights to help you succeed.
Basic Oracle Interview Questions (1-10)
1. What is an Oracle database?
An Oracle database is software that allows applications to store and retrieve data efficiently. It consists of data files storing user data and an instance managing memory structures for database access.[7]
2. What are the physical components of an Oracle database?
The main physical components include data files (store data), control files (metadata about database structure), redo log files (transaction logs), and archive log files (for recovery).[1]
3. What are data files in Oracle?
Data files are physical files on disk that store the actual database data, including tables, indexes, and other objects. Each tablespace consists of one or more data files.[2]
4. What are control files?
Control files contain metadata about the database structure, such as database name, data file locations, redo log information, and checkpoint details. They are critical for database recovery.[1]
5. What is the relationship between database, tablespace, and data file?
A database contains tablespaces, which are logical storage containers. Each tablespace maps to one or more data files that physically store the data on disk.[2]
6. Differentiate between Oracle Instance and Database.
An Oracle Instance consists of memory structures (SGA, PGA) and background processes running in RAM. The database refers to the physical files (data files, control files) stored on disk.[7]
7. What is SYSTEM tablespace?
SYSTEM tablespace stores core database objects, data dictionary tables, and metadata essential for database operation.[4]
8. What is SYSAUX tablespace?
From Oracle 10g onward, SYSAUX stores auxiliary data like AWR statistics, session information, and execution plans, offloading from SYSTEM tablespace.[4]
9. What is Oracle Flexible Architecture (OFA)?
OFA is a standard for organizing database files across multiple disks to optimize I/O performance and improve manageability.[4]
10. What does SGA stand for, and what does it include?
System Global Area (SGA) is shared memory containing buffer cache, shared pool, redo log buffer, and other components for database operations.[1]
Intermediate Oracle Interview Questions (11-20)
11. How do you create and manage user accounts and privileges in Oracle?
Create users with CREATE USER username IDENTIFIED BY password; Grant privileges using GRANT privilege TO username; Manage with REVOKE and roles for grouped permissions.[1]
12. What are the different types of Oracle database objects?
Common objects include tables, views, indexes, sequences, procedures, functions, packages, triggers, and synonyms.[2]
13. Explain the ANALYZE command in Oracle.
The ANALYZE command collects statistics about tables, indexes, and clusters to help the optimizer choose efficient execution plans.[2]
14. What is the difference between Primary Key and Unique Key?
Primary Key uniquely identifies rows and cannot be NULL. Unique Key also enforces uniqueness but allows one NULL value.[2]
15. What tools do you use to monitor Oracle database performance?
Key tools include AWR (Automatic Workload Repository), ADDM (Automated Database Diagnostics Monitor), STATSPACK, TKPROF, and OEM (Oracle Enterprise Manager).[4]
16. What are the optimizers used in Oracle?
Rule-Based Optimizer (RBO) uses predefined rules without statistics. Cost-Based Optimizer (CBO) analyzes statistics to select the lowest-cost execution plan.[4]
17. What is an Explain Plan?
Explain Plan shows the execution plan chosen by the optimizer for a SQL statement, helping identify performance bottlenecks.[4]
18. What is a bitmap index and when is it used?
Bitmap indexes store rowids in bitmap format, ideal for low-cardinality columns in data warehousing environments.[4]
19. What is a B-tree index and when is it used?
B-tree indexes use a balanced tree structure, preferred for high-cardinality columns in OLTP environments.[4]
20. How do you start an Oracle database?
Use SQL*Plus: STARTUP NOMOUNT; then ALTER DATABASE MOUNT; followed by ALTER DATABASE OPEN; or simply STARTUP;[1]
Advanced Oracle Interview Questions (21-30)
21. What is Oracle Real Application Clusters (RAC)?
RAC enables multiple servers to access a single database simultaneously, providing high availability, scalability, and fault tolerance.[5]
22. Explain Oracle Data Guard components.
Components include Primary Database (main), Standby Database (copy), Data Guard Broker (management), Log Transport Services (redo shipping), and Log Apply Services (synchronization).[5]
23. What is a trace file in Oracle?
Trace files capture diagnostic data when processes detect errors. Enable with ALTER SESSION SET SQL_TRACE=TRUE; for performance analysis.[6]
24. What are the virtual tables available during trigger execution?
:NEW and :OLD are pseudo-records representing new and old column values during INSERT, UPDATE, or DELETE trigger execution.[2]
25. How can you restrict users from accessing a database during maintenance?
Put database in restricted mode: SHUTDOWN IMMEDIATE; STARTUP RESTRICT; Only users with RESTRICTED SESSION privilege can connect.[1]
26. What are the main steps after manual database creation?
Execute catalog.sql (creates data dictionary views) and catproc.sql (creates procedural objects like PL/SQL packages).[7]
27. Can you change database block size after creation?
No, the database block size is fixed at creation and cannot be changed without recreating the database.[7]
28. In a scenario at Zoho where query performance degrades during peak hours, how would you optimize?
Generate AWR report to identify top SQL, analyze Explain Plan, gather fresh statistics with DBMS_STATS, rebuild fragmented indexes, and tune high-load queries.[4][1]
29. At Atlassian, if standby database falls behind primary, what steps do you take?
Check Log Apply Services status, verify log transport, resolve gaps with ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; then resume apply.[5]
30. For Adobe’s high-volume transaction system, explain index strategy for optimal performance.
Use B-tree for high-cardinality OLTP columns, bitmap for low-cardinality reporting, partition large indexes, regularly rebuild fragmented ones, and monitor with V$OBJECT_USAGE.[4]
