Interview Question 11 : What is base tables ?

 

What Are Base Tables?

base tables are the actual physical tables that store data in the database.

Base tables are real tables created by users that physically store rows of data on disk.

    Base tables are Oracle’s internal dictionary tables stored in the SYSTEM        tablespace that hold metadata about database objects and are managed         exclusively by Oracle’s internal processes

Base tables are Oracle’s internal dictionary tables that physically store metadata—that is, data about other database objects such as tables, indexes, users, privileges, and storage structures. These tables are maintained in a binary format and are managed entirely by the Oracle database engine.

Base tables are also known as dictionary tables because they form the foundation of the data dictionary. Copies of frequently accessed dictionary information are loaded into the data dictionary cache in the SGA and are aged out (flushed) as needed to optimize performance.

These tables reside in the SYSTEM tablespace and must never be modified directly by users. Any direct modification or corruption of base tables can render the database unstable or unusable.

Only Oracle’s internal background processes and kernel code are allowed to access and modify base tables as part of normal database operations.

 

They form the foundation of all database objects.

---

Simple Definition (Interview‑Friendly)

A base table is a table that directly stores data in database datafiles, unlike views or derived tables which only store definitions.

---

Key Characteristics of Base Tables

✅ Physical Storage

• Data is stored in datafiles
• Occupies storage (tablespaces, segments, blocks)
• Exists independently

✅ Created by Users

Created using:

CREATE TABLE employee (

emp_id NUMBER,

emp_name VARCHAR2(50),

salary NUMBER

);

This creates a base table.

---

✅ Can Be Directly Modified

You can:

INSERT

UPDATE

DELETE

SELECT

directly on base tables.

---

✅ Independent of Other Objects

• Exists even if views are dropped
• Other objects (views, indexes) depend on base tables

---

Base Tables vs Views (Very Important)

Aspect	Base Table	View
Data stored	✅ Yes	❌ No
Physical storage	✅ Yes	❌ No
Can exist alone	✅ Yes	❌ No
Derived from	❌ Nothing	✅ Base tables
performance	Faster	Depends on query

✅ Views are logical layers on top of base tables

---

Base Tables vs Derived Tables

Derived Table:

SELECT * FROM (

SELECT emp_id, salary FROM employee

• Temporary
• Exists only during query execution
• Uses base tables underneath

Base table = source of truth

---

Base Tables in Oracle (Internal View)

In Oracle:

• Base tables exist as segments
• Stored in tablespaces
• Managed by the instance (SGA, buffer cache)

Oracle dictionary base tables include:

• USER_TABLES
• ALL_TABLES
• DBA_TABLES

---

System Base Tables (Oracle Internals)

Oracle internally uses base tables to store metadata.

Examples:

• OBJ$
• TAB$
• COL$

⚠️ These are Oracle’s internal base tables
❌ Should never be modified directly by users

---

Why Base Tables Are Important (DBA View)

✅ Data Integrity

• Constraints (PK, FK, CHECK)
• Enforced at base table level

✅ Performance

• Indexes are built on base tables
• Stats collected on base tables

✅ Recovery

• Redo & undo tracked per base table
• RMAN backs up base tables

---

Real‑World Analogy

📁 Filing Cabinet Analogy

• Base table = Physical file folder
• View = Shortcut
• Index = Table of contents

Delete folder → shortcuts break
Delete shortcut → folder still exists

---

Common Interview Questions & Answers

Q1: Are views base tables?

❌ No
✅ Views depend on base tables

---

Q2: Where is base table data stored?

✅ In database datafiles

---

Q3: Can base tables exist without views?

✅ Yes

---

Q4: Are indexes base tables?

❌ No
✅ Indexes are separate segments created on base tables

---

One‑Line Summary (Perfect to Memorize)

Base tables are the physical database tables that store actual data and form the foundation for views, indexes, and other dependent database objects.

---

DBA Takeaway

🔥 No base tables = No database