Backend interview prep

Start

Oracle Basics Interview Questions

Master Oracle Database fundamentals with interview-focused questions covering Oracle architecture, database vs instance, Oracle editions, SQL*Plus, SQL Developer, data types, ROWID, ROWNUM, DUAL table, data dictionary, constraints, and production best practices.

Introduction

Oracle Database is one of the world's most powerful Enterprise Relational Database Management Systems (RDBMS).

It is widely used in

  • Banking
  • Insurance
  • Telecommunications
  • Government
  • Healthcare
  • ERP Systems
  • Financial Services

Oracle is known for

  • High Availability
  • Scalability
  • Security
  • Performance
  • Disaster Recovery
  • Enterprise Features

Understanding Oracle fundamentals is essential for Java Developers, Database Engineers, Oracle Developers, and Solution Architects.

Oracle Architecture Overview

flowchart LR

Application --> OracleListener --> OracleInstance

OracleInstance --> SGA

OracleInstance --> BackgroundProcesses

OracleInstance --> DatabaseFiles

DatabaseFiles --> DataFiles

DatabaseFiles --> RedoLogs

DatabaseFiles --> ControlFiles

1. What is Oracle Database?

Answer

Oracle Database is an Enterprise Relational Database Management System (RDBMS) developed by Oracle Corporation.

It stores data

  • Securely
  • Reliably
  • Efficiently

using

  • Tables
  • Rows
  • Columns

Oracle supports

  • SQL
  • PL/SQL
  • Transactions
  • ACID
  • High Availability

2. Why is Oracle popular?

Oracle is widely used because of

  • Enterprise Reliability
  • High Performance
  • Advanced Security
  • Scalability
  • RAC
  • Data Guard
  • Flashback Technology
  • Backup & Recovery

3. What is an Oracle Database?

Oracle Database is the collection of

  • Data Files
  • Control Files
  • Redo Log Files

These files permanently store the data.

4. What is an Oracle Instance?

Oracle Instance is the running memory and background processes that access the database.

Instance consists of

  • SGA
  • Background Processes

Without an instance, the database files cannot be accessed.

Database vs Instance

Database Instance
Physical Files Memory + Processes
Stores Data Accesses Data
Persistent Temporary
Exists on Disk Exists in Memory

Database vs Instance

flowchart LR

OracleInstance --> SGA

OracleInstance --> Processes

Processes --> OracleDatabase

OracleDatabase --> DataFiles

5. What is Oracle Client?

Oracle Client allows applications to connect to Oracle Database.

Examples

  • SQL*Plus
  • SQL Developer
  • JDBC Driver
  • OCI

6. What is SQL*Plus?

SQL*Plus is Oracle's command-line client.

Used for

  • SQL
  • PL/SQL
  • Administration
  • Scripting

Example

SELECT * FROM employees;

7. What is Oracle SQL Developer?

Oracle SQL Developer is Oracle's graphical IDE.

Features

  • SQL Editor
  • PL/SQL Debugger
  • Schema Browser
  • Reports
  • Data Export

Oracle Client Tools

flowchart LR

SQLDeveloper --> OracleDatabase

SQLPlus --> OracleDatabase

JDBC --> OracleDatabase

8. What are Oracle Editions?

Oracle provides several editions

  • Enterprise Edition
  • Standard Edition 2
  • Express Edition (XE)
  • Personal Edition

9. What is Oracle Enterprise Edition?

Enterprise Edition provides advanced features

  • RAC
  • Partitioning
  • Data Guard
  • Advanced Security
  • Advanced Compression

10. What is Oracle Express Edition (XE)?

Free version of Oracle.

Suitable for

  • Learning
  • Development
  • Small Applications

11. What are Oracle Data Types?

Common data types

Numeric

  • NUMBER
  • FLOAT

Character

  • CHAR
  • VARCHAR2
  • NCHAR
  • NVARCHAR2

Date

  • DATE
  • TIMESTAMP

Large Objects

  • CLOB
  • BLOB
  • NCLOB

Others

  • RAW
  • XMLTYPE
  • JSON (supported in newer versions)

12. Difference between CHAR and VARCHAR2?

CHAR VARCHAR2
Fixed Length Variable Length
Pads Spaces No Padding
Faster Fixed Comparisons Saves Storage

13. What is NUMBER datatype?

Stores

  • Integer
  • Decimal
  • Floating Numbers

Example

salary NUMBER(10,2)

14. What is DATE datatype?

Stores

  • Date
  • Time

Example

hire_date DATE

15. What is TIMESTAMP?

Stores

  • Date
  • Time
  • Fractional Seconds

Provides higher precision than DATE.

16. What are Constraints?

Constraints enforce data integrity.

Common constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK

17. What is ROWID?

ROWID is the physical address of a row inside Oracle.

Properties

  • Unique within a table
  • Automatically generated
  • Fast row lookup

Example

SELECT ROWID,

employee_id

FROM employees;

ROWID

flowchart LR

Table --> ROWID --> PhysicalLocation

18. What is ROWNUM?

ROWNUM is a pseudo-column representing the order rows are returned.

Example

SELECT *

FROM employees

WHERE ROWNUM <= 10;

19. Difference between ROWID and ROWNUM?

ROWID ROWNUM
Physical Address Sequential Number
Permanent for Row Generated During Query
Fast Lookup Result Limiting

20. What is the DUAL table?

DUAL is a special one-row, one-column table.

Used for

  • Calculations
  • Functions
  • Expressions

Example

SELECT SYSDATE

FROM DUAL;

21. Why is DUAL useful?

Allows execution of expressions without referencing application tables.

Example

SELECT 10 + 20

FROM DUAL;

22. What is the Data Dictionary?

The Data Dictionary contains metadata about database objects.

Examples

  • Tables
  • Indexes
  • Users
  • Constraints
  • Privileges

23. Common Data Dictionary Views

  • USER_TABLES
  • USER_OBJECTS
  • USER_INDEXES
  • ALL_TABLES
  • DBA_TABLES
  • DBA_USERS

Example

SELECT table_name

FROM user_tables;

24. What is SQL Execution Flow?

SQL

↓

Parser

↓

Optimizer

↓

Execution Plan

↓

Execution Engine

↓

Database

↓

Result

SQL Execution Flow

flowchart LR

SQL --> Parser --> Optimizer --> ExecutionPlan --> ExecutionEngine --> OracleDatabase

25. What is ACID?

Oracle fully supports

  • Atomicity
  • Consistency
  • Isolation
  • Durability

using transaction management.

26. Banking Example

Tables

  • Customer
  • Account
  • Transaction

Protected using

  • Primary Keys
  • Foreign Keys
  • Transactions

27. E-Commerce Example

Tables

  • Product
  • Customer
  • Order
  • Payment

Managed using Oracle constraints and indexes.

28. HR Example

Tables

  • Employee
  • Department
  • Salary
  • Attendance

Oracle ensures consistency using constraints.

29. Advantages of Oracle Database

  • Enterprise Grade
  • ACID Transactions
  • High Security
  • High Availability
  • Excellent Performance
  • RAC Support
  • Flashback Technology
  • Data Guard
  • Partitioning
  • Advanced Backup & Recovery

30. Limitations

  • Commercial Licensing Cost
  • Complex Administration
  • Higher Hardware Requirements
  • Steeper Learning Curve

31. Common Oracle Beginner Mistakes

  • Confusing Database and Instance
  • Using CHAR unnecessarily
  • Ignoring execution plans
  • Missing indexes
  • Selecting unnecessary columns
  • Poor schema design

Enterprise Best Practices

  • Use VARCHAR2 instead of CHAR unless fixed-length storage is required.
  • Define primary keys for every table.
  • Use foreign keys to maintain relationships.
  • Use NUMBER with appropriate precision.
  • Review execution plans using EXPLAIN PLAN.
  • Keep statistics updated.
  • Monitor tablespace growth.
  • Secure user accounts with least privilege.
  • Backup databases regularly.
  • Monitor performance continuously.

Quick Revision

Topic Key Point
Oracle Database Enterprise RDBMS
Database Physical Files
Instance Memory + Processes
SQL*Plus Command Line Tool
SQL Developer GUI Tool
ROWID Physical Row Address
ROWNUM Row Number
DUAL One-row System Table
Data Dictionary Database Metadata
NUMBER Numeric Type
VARCHAR2 Variable Length String
ACID Reliable Transactions

Interview Tips

Interviewers frequently ask

  • What is Oracle Database?
  • Difference between Database and Instance.
  • What is SQL*Plus?
  • What is SQL Developer?
  • Explain ROWID.
  • Explain ROWNUM.
  • What is the DUAL table?
  • What is the Data Dictionary?
  • VARCHAR2 vs CHAR.
  • Give a real-world Oracle use case.

Always explain that Oracle Database consists of physical database files, while an Oracle Instance is the memory structures (SGA) and background processes that manage access to those files. This distinction is one of the most frequently asked Oracle interview questions.

Summary

Oracle Database is a powerful enterprise RDBMS designed for high-performance, secure, and highly available applications. It combines SQL, PL/SQL, advanced storage management, transaction processing, and enterprise-grade features such as RAC, Data Guard, and Flashback Technology.

Understanding Oracle fundamentals—including databases, instances, client tools, data types, pseudo-columns, data dictionary views, and SQL execution flow—provides the foundation for mastering Oracle architecture, PL/SQL, performance tuning, backup and recovery, and RAC in enterprise environments.