Unit 1: Introduction to an Oracle Database [12 Hours]

An Oracle Database is a relational database system designed to store, organize, retrieve, and protect large amounts of data while supporting many users at the same time. This unit explains how Oracle is organized internally through its memory, process, and storage structures, how a Database Administrator (DBA) manages the system, and how users interact with Oracle through SQL*Plus. It also covers the main Structured Query Language (SQL) concepts used to define database objects, manipulate data, combine tables, and retrieve related information.

Tested Topics

1. Oracle database memory structure, SGA, PGA and Oracle Instance — 4x (2025, 2023, 2021, 2021)
2. Oracle database architecture: memory, process and storage structures — 2x (2025, 2021)
3. DBA roles and responsibilities — 2x (2023, 2021)
4. Table creation — 2x (2026, 2021)
5. Primary key constraints — 2x (2026, 2021)
6. Check constraints — 2x (2026, 2021)
7. Table renaming — 2x (2026, 2021)
8. INSERT and DELETE operations — 2x (2026, 2021)
9. Foreign key constraints — 2x (2026, 2021)
10. Column modification and renaming — 2x (2026, 2021)
11. Indexing and index creation guidelines — 1x (2023)
12. Table partitioning — 1x (2023)
13. Sequence — 1x (2025)
14. Synonym — 1x (2021)

1.1 Oracle Database Architecture

Definition

Oracle Database architecture is the organized arrangement of memory areas, operating-system processes, and storage files that work together to store data and process user requests.

A user does not directly open an Oracle datafile and search for a record. The request passes through Oracle's running programs and memory first. Those components locate or modify the required information and communicate with the files stored on disk.

Detailed Explanation

An Oracle database system can be understood through three major structures:

• Memory structure: temporary working memory used while Oracle is running.
• Process structure: programs that perform database work such as executing SQL, writing changed data, and recovering from failures.
• Storage structure: physical files and logical storage units in which database information is permanently organized.

Oracle Database and Oracle Instance

Two terms must be separated clearly:

• Oracle database: the collection of physical files containing permanent database information.
• Oracle instance: the memory structures and Oracle background processes that are currently running and managing the database.

The basic relationship is:

User request → Oracle instance processes the request → instance reads or changes the Oracle database files → result is returned to the user.

How a Request Moves Through Oracle

Consider the following query:

SELECT name, balance
FROM bank
WHERE acc_no = 101;

The general flow is:

1. The client sends the SQL statement to Oracle.
2. A server process receives and processes the request.
3. Oracle examines the Shared Pool to determine whether the SQL statement has already been parsed.
4. Required data blocks are searched for in the Database Buffer Cache.
5. If the blocks are not already in memory, Oracle reads them from the datafiles.
6. The server process obtains the required row and prepares the result.
7. The result is returned to the client application.

For a modification such as an UPDATE, additional structures participate. The changed block remains in the buffer cache for a period, redo information is generated, Log Writer (LGWR) writes redo to the online redo log, and Database Writer (DBWn) eventually writes changed blocks to datafiles.

1.2 Oracle Instance and Memory Structure

Definition

An Oracle instance is the combination of Oracle's shared memory structures and background processes that are started to access and manage an Oracle database.

The database files may remain on disk even when Oracle is shut down. When Oracle starts, it allocates memory and starts background processes. Together, these running components form the instance.

Detailed Explanation

The two main Oracle memory structures are:

• System Global Area (SGA): shared memory used by the instance and accessible to Oracle processes.
• Program Global Area (PGA): private memory associated with an individual server or background process.

System Global Area (SGA)

The System Global Area (SGA) is a shared memory region allocated when an Oracle instance starts. Multiple server and background processes use information stored in it.

The major SGA components are:

Database Buffer Cache

The Database Buffer Cache stores copies of data blocks read from database datafiles.

When a user requests a row, Oracle first checks whether the required block is already in the buffer cache. If it exists there, Oracle can use the memory copy rather than perform another physical disk read.

When a block is modified, the changed copy in memory becomes a dirty buffer. DBWn later writes dirty buffers back to datafiles.

Shared Pool

The Shared Pool stores information that can be reused by multiple sessions. Two major areas within it are:

• Library Cache: stores parsed SQL statements, execution information, and executable forms of SQL and Procedural Language/Structured Query Language (PL/SQL) code.
• Data Dictionary Cache: stores frequently accessed metadata about database objects, users, privileges, tables, columns, and other database definitions.

If equivalent SQL already exists in the library cache, Oracle may reuse the existing parsed representation. This is called a soft parse. When Oracle must perform full parsing and optimization, the operation is called a hard parse.

Redo Log Buffer

The Redo Log Buffer temporarily holds information describing changes made to the database.

Change occurs → redo information enters the Redo Log Buffer → LGWR writes redo records to online redo log files.

Redo information is essential for database recovery because it records changes that can be reapplied after a failure.

Large Pool

The Large Pool is an optional memory area used for large memory allocations that should not compete heavily with the Shared Pool. It can support operations such as shared server session memory, backup and recovery operations, and some parallel execution activities.

Java Pool

The Java Pool provides memory for Java code and Java Virtual Machine-related activity inside the Oracle database when database-side Java is used.

Streams Pool

The Streams Pool is a memory area used by certain Oracle data movement, replication, and related features that require shared memory.

Program Global Area (PGA)

The Program Global Area (PGA) is a private memory region belonging to an individual Oracle process. Unlike the SGA, it is not generally shared among processes.

A PGA can contain:

• Private SQL area: information related to the SQL statement being handled by that process.
• Session memory: information associated with the user's session.
• SQL work areas: temporary memory used for operations such as sorting, hashing, and joining data.

For example, an ORDER BY operation may use a PGA work area to sort rows. If the operation is larger than the available memory, Oracle may use temporary storage on disk.

SGA and PGA Comparison

Instance Startup Relationship

At a high level, Oracle startup establishes the instance and then gives that instance access to the database:

Allocate SGA → start background processes → locate database control information → open the database files → database becomes available to users.

1.3 Oracle Process Structure

Definition

Oracle process structure consists of user processes, server processes, and background processes that cooperate to execute SQL statements and maintain the database.

Memory stores information, but processes perform the actual work. They receive requests, search memory, access database files, write changes, maintain checkpoints, and recover database information when necessary.

Detailed Explanation

User Process

A user process is created when a database user runs an application or client program that communicates with Oracle.

Examples include:

• SQL*Plus
• A Java application
• A web application connected to Oracle
• A database administration tool

The user process sends SQL requests but normally does not directly manipulate Oracle database files.

Server Process

A server process performs work on behalf of a user's session. Its responsibilities can include:

• Parsing SQL statements.
• Checking permissions.
• Creating execution plans.
• Reading blocks from datafiles into memory.
• Changing data in memory.
• Returning query results to the client.

Background Processes

Background processes perform database-wide maintenance and coordination. Important Oracle background processes include the following.

How Processes Cooperate During an UPDATE

Suppose the following statement is executed:

UPDATE bank
SET balance = balance + 1000
WHERE acc_no = 101;

The general sequence is:

1. The user process submits the UPDATE statement.
2. The server process parses and executes the statement.
3. The relevant data block is located in the Database Buffer Cache or read from a datafile.
4. The server process modifies the block in memory.
5. Redo describing the change is placed in the Redo Log Buffer.
6. LGWR writes redo information to an online redo log at the required time.
7. DBWn later writes the modified block from memory to the corresponding datafile.

The separation between redo writing and datafile writing helps Oracle provide transaction durability and efficient disk operations.

1.4 Oracle Storage Structure

Definition

Oracle storage structure describes how database information is organized both physically in operating-system files and logically inside the Oracle database.

Physical storage answers, “Which files are stored on disk?” Logical storage answers, “How does Oracle organize the information inside those files?”

Detailed Explanation

Physical Database Files

The three principal physical file types of an Oracle database are:

Datafiles

Datafiles contain the actual stored database data, including table and index blocks. Every tablespace is associated with one or more datafiles.

Control Files

Control files contain critical structural information about the database, including information about database files, redo logs, checkpoints, and database state.

Oracle needs valid control-file information to identify and manage the database structure.

Online Redo Log Files

Online redo log files store redo records describing changes made to database data. They are used during recovery to reconstruct changes that had occurred before a failure.

Supporting Files

Oracle also uses supporting files such as:

• Server Parameter File (SPFILE) or text initialization parameter file for instance configuration.
• Password file for certain privileged administrative authentication.
• Archived redo log files when redo logs are archived for recovery purposes.
• Backup files maintained as part of a backup and recovery strategy.

Logical Storage Structure

Oracle's logical storage hierarchy is commonly represented as:

Database → Tablespaces → Segments → Extents → Data Blocks

Tablespace

A tablespace is a logical storage container in an Oracle database. It groups related logical objects and is physically represented by one or more datafiles.

Segment

A segment is storage allocated for a particular database object or storage purpose. Examples include table segments and index segments.

Extent

An extent is a set of contiguous database blocks allocated to a segment. As an object requires more storage, Oracle can allocate additional extents.

Data Block

An Oracle data block is the smallest logical unit of database storage used by Oracle for reading and writing database information.

Connection Between Physical and Logical Storage

A tablespace is logical, but its information must ultimately exist in physical datafiles.

Table row → stored in Oracle data block → block belongs to extent → extent belongs to table segment → segment belongs to tablespace → tablespace uses one or more datafiles.

1.5 Database Administration and DBA Roles

Definition

Database administration is the management, maintenance, protection, monitoring, and operation of a database system so that data remains available, secure, correct, and recoverable.

A Database Administrator (DBA) is the person responsible for performing or coordinating these database management activities.

Detailed Explanation

A database serves applications continuously and contains valuable organizational information. The DBA manages both technical resources and operational policies needed to keep that database reliable.

Major Roles and Responsibilities of a DBA

Common DBA Specializations

In a small organization, one person may perform most DBA responsibilities. Larger organizations can divide them into specialized roles.

• System DBA: focuses on installation, configuration, availability, storage, and infrastructure.
• Application DBA: supports databases used by particular applications and works closely with developers.
• Security DBA: concentrates on users, privileges, auditing, and database protection.
• Performance DBA: investigates SQL and database performance problems.
• Backup and recovery DBA: concentrates on backup, restore, disaster recovery, and data protection.

Example of DBA Work

Suppose a university database is running out of storage and student result queries have become slow. The DBA may:

Check tablespace usage → identify storage shortage → add or resize storage → examine expensive SQL → inspect indexes and execution plans → correct the cause → continue monitoring.

1.6 SQL*Plus

Definition

SQL*Plus is an Oracle command-line tool used to enter SQL statements, execute Procedural Language/Structured Query Language programs, run scripts, format query output, and perform database-related tasks.

It provides a text-based environment where a user connects to an Oracle database, enters commands, receives results, and can save repeated commands inside script files.

Detailed Explanation

SQL*Plus accepts two broad kinds of commands:

• SQL and PL/SQL statements: commands executed by the Oracle database.
• SQL*Plus commands: commands interpreted by SQL*Plus itself to control the session, variables, display, scripts, and output.

Connecting to Oracle

sqlplus username/password@service_name

After a connection is established, SQL statements can be entered at the SQL prompt.

SELECT * FROM employees;

Common SQL*Plus Commands

SQL Statement and SQL*Plus Command Difference

Running a Script

If a file named report.sql contains SQL statements, it can be executed with:

@report.sql

Scripts become especially useful when substitution variables and ACCEPT commands are added, because the same script can process different user-supplied values.

1.7 Accepting Values at Runtime

Definition

Accepting values at runtime means allowing a SQL*Plus script to receive input from the user while the script is executing instead of fixing every value inside the SQL statement beforehand.

One script can therefore be reused for different account numbers, department numbers, names, dates, or other input values.

Detailed Explanation

SQL*Plus supports runtime input mainly through substitution variables and the ACCEPT command.

Single Ampersand Substitution Variable

A variable beginning with a single ampersand prompts for a value when the variable has not already been defined.

SELECT *
FROM employees
WHERE department_id = &dept_no;

If the user enters:

20

SQL*Plus substitutes the supplied value into the statement before sending it to the database.

Character Values

Character substitution values usually need SQL quotation marks in the statement:

SELECT *
FROM employees
WHERE job_id = '&job';

If the user enters IT_PROG, the resulting condition becomes:

WHERE job_id = 'IT_PROG'

Double Ampersand

A double ampersand can define a substitution variable so that the supplied value can be reused in later references during the SQL*Plus session.

SELECT *
FROM employees
WHERE department_id = &&dept_no;

SELECT COUNT(*)
FROM employees
WHERE department_id = &dept_no;

The first reference requests the value and defines it. The second reference can reuse the stored value.

ACCEPT Command

The SQL*Plus ACCEPT command gives more control over prompting and input.

ACCEPT dept_no NUMBER PROMPT 'Enter department number: '

SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = &dept_no;

The command can specify the expected input type and a meaningful prompt.

PROMPT Command

The PROMPT command displays a message in SQL*Plus.

PROMPT Employee Search Report

UNDEFINE Command

UNDEFINE removes a substitution variable so that a later reference can request a new value.

UNDEFINE dept_no

Runtime Input Flow

Script encounters variable → SQL*Plus requests input → user supplies value → SQL*Plus substitutes value → completed SQL statement is sent to Oracle → Oracle returns the result.

1.8 Overview of SQL Commands

Definition

Structured Query Language (SQL) is the standard language used with relational databases to define database objects, manipulate stored rows, and retrieve information.

In this unit, SQL is used for two broad purposes: building database structures and working with the data stored inside those structures.

Detailed Explanation

The syllabus focuses on Data Definition Language (DDL), Data Manipulation Language (DML), joins, and subqueries.

Data Definition Language

DDL works with database object definitions. Major object types covered in this unit include:

• Tables
• Constraints
• Indexes
• Views
• Synonyms
• Sequences
• Partitioned tables
• Materialized views

Common DDL Statements

DDL and DML Difference

1.9 Tables and Constraints

Definition

A table is a relational database object that stores data in rows and columns. A constraint is a rule applied to table data to prevent invalid or inconsistent values from being stored.

The table provides the structure, while constraints protect the quality of the information placed inside that structure.

Detailed Explanation

Creating a Table

The general Oracle syntax is:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Example:

CREATE TABLE student (
    student_id NUMBER,
    name VARCHAR2(50),
    address VARCHAR2(100),
    dob DATE
);

Oracle commonly uses VARCHAR2 for variable-length character data, NUMBER for numeric data, and DATE for date and time information.

Types of Constraints

Primary Key

A primary key identifies each row uniquely. A table can have only one primary-key constraint, although that key can contain more than one column.

ALTER TABLE student
ADD CONSTRAINT pk_student
PRIMARY KEY (student_id);

A primary-key value cannot be NULL, and duplicate primary-key values are not permitted.

Foreign Key

A foreign key creates a relationship between tables. Values in the child foreign-key column normally reference existing values in a primary key or unique key of the parent table.

ALTER TABLE employee
ADD department_id NUMBER;

ALTER TABLE employee
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES department(department_id);

The referenced department(department_id) column must satisfy the requirements for a referenced key, normally through a PRIMARY KEY or UNIQUE constraint.

Check Constraint

A CHECK constraint restricts values according to a condition.

ALTER TABLE employee
ADD CONSTRAINT chk_employee_salary
CHECK (salary > 10000);

Oracle rejects a new or modified row when the CHECK condition is violated.

Adding a Column

ALTER TABLE employee
ADD email VARCHAR2(100);

Modifying a Column

The size or selected properties of an existing column can be changed with ALTER TABLE.

ALTER TABLE employee
MODIFY (name VARCHAR2(100));

Renaming a Column

ALTER TABLE employee
RENAME COLUMN name TO ename;

Renaming a Table

ALTER TABLE emp
RENAME TO employee;

Worked Example: Bank Table

The following sequence demonstrates table creation, constraints, table renaming, row insertion and deletion, a foreign key, column modification, and column renaming.

Column names such as Acc-no and ph-no contain hyphens, which would require quoted identifiers in Oracle. The example uses conventional identifiers acc_no and ph_no to avoid quoted-name complications.

Step 1: Create the Table

CREATE TABLE blank (
    acc_no NUMBER,
    name VARCHAR2(50),
    address VARCHAR2(100),
    ph_no VARCHAR2(20),
    balance NUMBER(12,2)
);

Step 2: Add Primary Key

ALTER TABLE blank
ADD CONSTRAINT pk_blank
PRIMARY KEY (acc_no);

Step 3: Add Check Constraint

ALTER TABLE blank
ADD CONSTRAINT chk_blank_balance
CHECK (balance > 300);

Step 4: Rename the Table

ALTER TABLE blank
RENAME TO bank;

Step 5: Insert Rows

INSERT INTO bank
VALUES (101, 'Asha', 'Kathmandu', '9800000001', 5000);

INSERT INTO bank
VALUES (102, 'Bikash', 'Pokhara', '9800000002', 8500);

Step 6: Delete a Row

DELETE FROM bank
WHERE acc_no = 102;

Step 7: Add loan_id and Foreign Key

ALTER TABLE bank
ADD loan_id NUMBER;

ALTER TABLE bank
ADD CONSTRAINT fk_bank_loan
FOREIGN KEY (loan_id)
REFERENCES loan(loan_id);

This assumes that the loan table already exists and loan_id is an appropriate referenced key.

Step 8: Increase Name Length

ALTER TABLE bank
MODIFY (name VARCHAR2(100));

Step 9: Rename Name Column

ALTER TABLE bank
RENAME COLUMN name TO cname;

Worked Example: Employee Table

CREATE TABLE emp (
    emp_id NUMBER,
    name VARCHAR2(50),
    address VARCHAR2(100),
    dob DATE,
    salary NUMBER(10,2)
);

ALTER TABLE emp
ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);

ALTER TABLE emp
ADD CONSTRAINT chk_emp_salary
CHECK (salary > 10000);

ALTER TABLE emp
RENAME TO employee;

INSERT INTO employee
VALUES (1, 'Ram', 'Kathmandu', DATE '2000-05-20', 25000);

INSERT INTO employee
VALUES (2, 'Sita', 'Bhaktapur', DATE '2001-08-10', 30000);

DELETE FROM employee
WHERE emp_id = 2;

ALTER TABLE employee
ADD department_id NUMBER;

ALTER TABLE employee
ADD CONSTRAINT fk_emp_department
FOREIGN KEY (department_id)
REFERENCES department(department_id);

ALTER TABLE employee
MODIFY (name VARCHAR2(100));

ALTER TABLE employee
RENAME COLUMN name TO ename;

1.10 Indexes

Definition

An index is a database structure that provides a faster access path to rows by storing selected column values together with information that allows Oracle to locate the corresponding table rows efficiently.

Without a useful index, Oracle may need to examine many table rows to find a small result. An appropriate index can narrow the search much earlier.

Detailed Explanation

Indexes are especially useful when a large table is frequently searched through particular columns.

For example:

SELECT *
FROM employee
WHERE employee_id = 5001;

If employee_id has a suitable index, Oracle may use that index to locate the required row rather than scanning every employee row.

Creating an Index

CREATE INDEX idx_employee_name
ON employee(ename);

Composite Index

An index can contain more than one column:

CREATE INDEX idx_emp_dept_salary
ON employee(department_id, salary);

The order of columns in a composite index affects how the index can support different predicates.

Major Oracle Index Types

B-tree Index

A B-tree index is Oracle's common general-purpose index structure. It is suitable for columns with many distinct values and for equality or range searches.

Bitmap Index

A bitmap index represents indexed values through bitmaps. It can be effective for low-cardinality columns in read-heavy analytical environments, such as columns containing a small number of status categories.

Bitmap indexes are generally unsuitable for tables undergoing heavy concurrent row-by-row modifications because DML can cause greater locking and maintenance effects.

Function-Based Index

A function-based index stores the result of an expression or function.

CREATE INDEX idx_emp_upper_name
ON employee(UPPER(ename));

This can support a query such as:

SELECT *
FROM employee
WHERE UPPER(ename) = 'RAM';

Index Types Comparison

Why Indexing Is Important

• Faster searching: can reduce the number of table blocks Oracle must examine.
• Improved joins: indexes on useful join columns can make table matching more efficient.
• Efficient sorting and access paths: some queries can benefit from index ordering.
• Constraint support: Oracle can use indexes to enforce PRIMARY KEY and UNIQUE constraints.

Guidelines for Creating Indexes

1. Index columns frequently used in selective WHERE conditions. An index is most helpful when a condition normally returns a relatively small portion of a large table.
2. Consider columns used frequently in joins. Foreign-key and relationship columns are common index candidates depending on workload.
3. Avoid unnecessary indexes on very small tables. A full table scan can be cheaper when the table contains little data.
4. Avoid creating many indexes on heavily modified tables. INSERT, UPDATE, and DELETE operations must also maintain affected indexes.
5. Choose index type according to data and workload. B-tree indexes suit many transactional searches, while bitmap indexes fit selected analytical workloads.
6. Consider column selectivity. A column with many distinct values is often a stronger B-tree index candidate than one having very few values.
7. Consider expressions used by queries. If queries consistently use a function such as UPPER(column), a matching function-based index may help.
8. Do not duplicate an index without a reason. Existing primary-key, unique, or composite indexes may already provide the required access path.

Cost of Indexes

An index is not free. It consumes storage and must be maintained when indexed data changes.

More indexes → potentially faster selected reads → more index storage and additional DML maintenance.

1.11 Views

Definition

A view is a named SQL query stored in the database and presented to users like a virtual table.

A view can show selected rows or columns from one or more base tables without requiring users to write the full underlying query each time.

Detailed Explanation

A normal Oracle view primarily stores its query definition rather than storing a separate permanent copy of all query result rows.

Creating a View

CREATE VIEW emp_public AS
SELECT employee_id, ename, department_id
FROM employee;

The view can then be queried like a table:

SELECT *
FROM emp_public;

Why Views Are Used

• Simplification: hides complicated joins and calculations behind a reusable object.
• Controlled exposure: presents selected columns rather than exposing every table column.
• Logical independence: provides users with a stable logical representation of data.
• Reusable queries: a commonly used query receives a database object name.

Simple and Complex Views

WITH CHECK OPTION

WITH CHECK OPTION can prevent DML through a view from producing rows that no longer satisfy the view's defining condition.

CREATE VIEW high_salary_emp AS
SELECT employee_id, ename, salary
FROM employee
WHERE salary > 50000
WITH CHECK OPTION;

WITH READ ONLY

A view can be explicitly made read-only:

CREATE VIEW emp_readonly AS
SELECT employee_id, ename, salary
FROM employee
WITH READ ONLY;

1.12 Synonyms

Definition

A synonym is an alternative name for another Oracle database object such as a table, view, sequence, or stored program unit.

Instead of repeatedly writing a long owner-qualified object name, users can refer to a shorter synonym.

Detailed Explanation

Suppose a table is owned by the HR user:

HR.EMPLOYEES

A synonym can provide a shorter name:

CREATE SYNONYM emp
FOR hr.employees;

The object can then be referenced through the synonym where privileges allow:

SELECT *
FROM emp;

Why Synonyms Are Useful

• Simpler names: removes repeated schema qualification from object references.
• Location transparency: applications can reference a stable synonym while the underlying qualified object reference is managed separately.
• Convenience: shortens frequently used object names.
• Application independence: can reduce the need to place owner names throughout application SQL.

Private Synonym

A private synonym belongs to a particular schema.

CREATE SYNONYM staff
FOR hr.employees;

Public Synonym

A public synonym is available for name resolution to database users, subject to the required object privileges.

CREATE PUBLIC SYNONYM staff
FOR hr.employees;

Creating a public synonym requires the appropriate Oracle privilege.

Private and Public Synonyms

1.13 Sequences

Definition

A sequence is an Oracle database object that generates numeric values according to specified rules.

It is commonly used to generate identifiers without manually calculating the next number each time a new row is inserted.

Detailed Explanation

Creating a Sequence

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;

NEXTVAL

The NEXTVAL pseudocolumn obtains the next sequence value and advances the sequence.

SELECT emp_seq.NEXTVAL
FROM dual;

It can be used during INSERT:

INSERT INTO employee(employee_id, ename, salary)
VALUES (emp_seq.NEXTVAL, 'Anita', 35000);

CURRVAL

The CURRVAL pseudocolumn returns the current sequence value for the session after NEXTVAL has already been referenced for that sequence in the session.

SELECT emp_seq.CURRVAL
FROM dual;

Sequence Options

Complete Example

CREATE SEQUENCE order_seq
START WITH 1001
INCREMENT BY 1
NOCYCLE
CACHE 20;

INSERT INTO orders(order_id, customer_name)
VALUES (order_seq.NEXTVAL, 'Ramesh');

INSERT INTO orders(order_id, customer_name)
VALUES (order_seq.NEXTVAL, 'Sita');

The generated values can be 1001 and 1002 respectively.

Why Sequences Are Used

• Generate unique numeric candidates efficiently.
• Avoid manually searching for the current maximum identifier.
• Allow multiple sessions to obtain values without serializing the application around MAX(column) calculations.
• Can be reused independently of one particular table unless the application restricts their use.

1.14 Table Partitioning

Definition

Table partitioning divides one logical table into smaller physical storage pieces called partitions according to a partitioning rule.

Applications can still query the object as one table, while Oracle can manage and access selected portions separately.

Detailed Explanation

Large tables can contain millions or billions of rows. Treating the whole object as one storage unit can make data management more difficult. Partitioning allows data to be divided according to values such as date, region, category, or a hash function.

Benefits of Partitioning

• Partition pruning: Oracle can avoid reading unrelated partitions when query conditions identify the required partition range or key.
• Manageability: selected partitions can be maintained separately.
• Availability: some maintenance operations can be limited to specific partitions.
• Data lifecycle management: older or newer data can be managed by partition.
• Performance opportunities: appropriate partitioning can reduce the data that some operations need to process.

Range Partitioning

Range partitioning assigns rows to partitions according to ordered value ranges. Dates are a common example.

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_2025 VALUES LESS THAN (DATE '2026-01-01'),
    PARTITION sales_2026 VALUES LESS THAN (DATE '2027-01-01'),
    PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

Rows from 2025 can go into sales_2025, 2026 rows into sales_2026, and later values into the MAXVALUE partition.

List Partitioning

List partitioning places rows according to explicitly listed values.

CREATE TABLE customers (
    customer_id NUMBER,
    name VARCHAR2(50),
    region VARCHAR2(20)
)
PARTITION BY LIST (region) (
    PARTITION p_bagmati VALUES ('BAGMATI'),
    PARTITION p_gandaki VALUES ('GANDAKI'),
    PARTITION p_other VALUES (DEFAULT)
);

Hash Partitioning

Hash partitioning uses a hashing algorithm to distribute rows among a specified number of partitions.

CREATE TABLE transactions (
    transaction_id NUMBER,
    account_id NUMBER,
    amount NUMBER
)
PARTITION BY HASH (account_id)
PARTITIONS 4;

Hash partitioning is useful when even distribution is preferred and there is no natural range or list boundary.

Composite Partitioning

Composite partitioning combines partitioning methods. A table can first be partitioned using one method and then subpartitioned using another.

Examples include:

• Range-hash partitioning.
• Range-list partitioning.
• List-hash partitioning.

Partitioning Methods Comparison

Partition Pruning Example

For the partitioned SALES table:

SELECT SUM(amount)
FROM sales
WHERE sale_date >= DATE '2026-01-01'
AND sale_date < DATE '2027-01-01';

When the partitioning conditions allow it, Oracle can restrict access to relevant partition data instead of examining every partition.

1.15 Materialized Views

Definition

A materialized view is a database object that stores the result of a query physically and can refresh that stored result when underlying data changes.

A normal view stores a query definition and obtains current results when queried. A materialized view maintains stored query-result data that can be reused without recomputing the complete query every time.

Detailed Explanation

Materialized views are useful when a query is expensive and its result is repeatedly needed, especially in reporting and analytical systems.

Creating a Materialized View

CREATE MATERIALIZED VIEW dept_salary_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary
FROM employee
GROUP BY department_id;

The result of the GROUP BY query is physically stored as part of the materialized view.

Refresh Methods

Complete Refresh

A complete refresh recomputes the materialized view's defining query and replaces the stored result.

Fast Refresh

A fast refresh attempts to apply relevant incremental changes rather than recomputing the entire result. Fast refresh has additional requirements, and materialized view logs are commonly required depending on the materialized view definition.

Force Refresh

A force refresh allows Oracle to attempt a fast refresh when possible and use a complete refresh when the fast method cannot be performed.

Refresh Timing

• ON COMMIT: refresh is associated with transactions committing changes to relevant base tables when supported.
• ON DEMAND: refresh occurs when explicitly requested or scheduled through an appropriate mechanism.

View and Materialized View Comparison

1.16 Data Manipulation Language Operations

Definition

Data Manipulation Language (DML) consists of SQL statements used to add, change, and remove rows stored in database tables.

DDL creates the table structure. DML works with the actual records inside that structure.

Detailed Explanation

Major Oracle DML statements include INSERT, UPDATE, DELETE, and MERGE. SELECT is the principal statement used to retrieve data and is often studied alongside these operations.

INSERT

INSERT adds new rows.

INSERT INTO employee (
    employee_id,
    ename,
    salary
)
VALUES (
    101,
    'Rita',
    32000
);

When all table columns are supplied in their table order, the column list can be omitted, but explicitly listing the target columns usually makes the statement clearer and safer.

UPDATE

UPDATE changes values in existing rows.

UPDATE employee
SET salary = 35000
WHERE employee_id = 101;

DELETE

DELETE removes rows satisfying its condition.

DELETE FROM employee
WHERE employee_id = 101;

MERGE

MERGE can combine conditional update and insert behavior, often called an upsert pattern.

MERGE INTO employee e
USING new_employee n
ON (e.employee_id = n.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = n.salary
WHEN NOT MATCHED THEN
    INSERT (employee_id, ename, salary)
    VALUES (n.employee_id, n.ename, n.salary);

DELETE and TRUNCATE Difference

Importance of the WHERE Clause

The WHERE clause determines which rows UPDATE or DELETE affects.

DELETE FROM employee;

The statement above targets every row because no WHERE condition is present.

DELETE FROM employee
WHERE employee_id = 101;

This version targets only matching rows.

1.17 Joins

Definition

A join combines rows from two or more table sources by comparing related column values or another specified join condition.

Relational databases normally separate different subjects into different tables. Joins reconnect those related tables when a query needs information from more than one of them.

Detailed Explanation

Consider two tables:

EMPLOYEE

DEPARTMENT

Inner Join

An INNER JOIN returns rows for which the specified join condition matches.

SELECT e.employee_id,
       e.ename,
       d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;

Asha matches department 10 and Binod matches department 20. Chandra has no matching department value, so that row is not returned by this inner join.

Equijoin

An equijoin is a join in which the join condition uses equality.

ON e.department_id = d.department_id

The previous INNER JOIN is therefore also an equijoin.

Non-Equijoin

A non-equijoin uses a condition other than simple equality, often to match values to ranges.

SELECT e.ename, e.salary, g.grade
FROM employee e
JOIN salary_grade g
ON e.salary BETWEEN g.min_salary AND g.max_salary;

Left Outer Join

A LEFT OUTER JOIN returns every row from the left table plus matching rows from the right table. When no match exists, right-table columns contain NULL values.

SELECT e.ename, d.department_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id;

This can include Chandra even though no matching department exists.

Right Outer Join

A RIGHT OUTER JOIN returns every row from the right table plus matching rows from the left table.

SELECT e.ename, d.department_name
FROM employee e
RIGHT JOIN department d
ON e.department_id = d.department_id;

This can include Administration even when no employee belongs to that department.

Full Outer Join

A FULL OUTER JOIN returns matching rows plus unmatched rows from both sides.

SELECT e.ename, d.department_name
FROM employee e
FULL OUTER JOIN department d
ON e.department_id = d.department_id;

Self Join

A self join joins a table to another logical reference to the same table. Aliases distinguish the two roles.

SELECT e.ename AS employee_name,
       m.ename AS manager_name
FROM employee e
LEFT JOIN employee m
ON e.manager_id = m.employee_id;

Cross Join

A CROSS JOIN returns the Cartesian product: every row from one table paired with every row from the other table.

SELECT e.ename, s.shift_name
FROM employee e
CROSS JOIN shift s;

If EMPLOYEE has 5 rows and SHIFT has 3 rows, the cross join produces 15 row combinations.

Join Comparison

1.18 Subqueries

Definition

A subquery is a SQL query placed inside another SQL statement so that the result produced by the inner query can be used by the outer statement.

One query can first discover a value or set of values, and another query can use those results to decide which rows to return or modify.

Detailed Explanation

A basic subquery contains an inner query and an outer query.

SELECT ename, salary
FROM employee
WHERE salary > (
    SELECT AVG(salary)
    FROM employee
);

The logical idea is:

Inner query calculates average salary → result is passed to outer query → outer query returns employees whose salaries exceed that value.

Single-Row Subquery

A single-row subquery returns one row. Operators such as =, >, <, >=, and <= can be used when one comparable value is expected.

SELECT ename
FROM employee
WHERE department_id = (
    SELECT department_id
    FROM department
    WHERE department_name = 'IT'
);

Multiple-Row Subquery

A multiple-row subquery returns more than one row. Operators such as IN, ANY, or ALL can be used depending on the required comparison.

SELECT ename
FROM employee
WHERE department_id IN (
    SELECT department_id
    FROM department
    WHERE location_id = 100
);

IN Operator

IN tests whether a value equals any value returned by the subquery.

ANY Operator

ANY compares a value with the values returned by a subquery and succeeds when the comparison is true for at least one returned value.

ALL Operator

ALL requires the comparison to be true for every value returned by the subquery.

Correlated Subquery

A correlated subquery refers to a column from the outer query. Its evaluation is logically associated with rows considered by the outer query.

SELECT e1.ename, e1.salary
FROM employee e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employee e2
    WHERE e2.department_id = e1.department_id
);

This query finds employees whose salaries are above the average salary of their own departments.

EXISTS

EXISTS tests whether a subquery returns at least one row.

SELECT d.department_id, d.department_name
FROM department d
WHERE EXISTS (
    SELECT 1
    FROM employee e
    WHERE e.department_id = d.department_id
);

The department is returned when at least one matching employee exists.

Scalar Subquery

A scalar subquery returns exactly one value and can be used where a single expression is valid.

SELECT ename,
       salary,
       (SELECT AVG(salary) FROM employee) AS company_average
FROM employee;

Subquery Types Comparison

Join and Subquery Relationship

Joins and subqueries can sometimes express similar information requests, but their structures differ.

• A join directly combines row sources.
• A subquery uses the result of one query inside another statement.
• The Oracle optimizer can transform some logically equivalent query forms internally, so SQL should be selected primarily for correct logic and clear expression of the requirement.

Exam Questions

1. Write SQL commands for following:
   i. Create the table named blank having fields (Acc-no, name, address, ph-no, Balance)
   ii. Add primary key in above table
   iii. Add check constraints in the balance field, value of balance > 300.
   iv. Rename the table blank to Bank.
   v. Insert some rows and delete one of the record from the table Bank.
   viii. Add loan_id field as foreign key referencing to the loan.
   ix. Modify the name field to increase the character length.
   x. Rename the name field to cname. (2026)
2. Explain oracle database architecture in detail including memory, process, and storage structure of oracle database. (2025)
3. Explain the use of sequence in oracle database with example. (2025)
4. What is partitioning? Explain the different method of table partitioning. (2023)
5. What is indexing? Why it is important in database? Explain the different guidelines to create index in database. (2023)
6. What is database administration? Explain the roles and responsibilities of DBA. (2023)
7. Describe the memory structure of oracle database. Explain how PGA differs from SGA. Also explain about oracle instance and different components of SGA. (2023)
8. What do you mean by oracle Instance? Explain about the different data structures used in SGA. (2021)
9. What is synonym? why it is important? explain it with example. (2021)
10. Explain the term database administrator (DBA) and describe the different roles of DBA. (2021)
11. What do you mean by database management system? Mention the clear architecture of oracle database management system including different files, process and memory structure. (2021)
12. Write SQL commands for following
    i. Create the table named emp having fields (emp-id, name, address DOB, Salary)
    ii. Add primary key in above table
    iii. Add check constraints in the salary fields, value of salary> 10000
    iv. Rename the table emp to employee
    v. V Insert some rows and delete one of the record from the table employee
    viii.Add department_id field as foreign key referencing the table department.
    ix. Modify the name field to increase the character length
    x. Rename the name field to ename. (2021)

Whole Chapter Summary

Oracle Database Architecture

Oracle combines memory structures, processes, and storage structures. Users communicate with an Oracle instance, and the instance accesses the physical database files to execute requests.

Oracle Instance and Memory Structure

An instance consists primarily of the System Global Area (SGA) and background processes. The SGA is shared, while the Program Global Area (PGA) contains private process memory.

Oracle Process Structure

User processes originate requests, server processes perform work for sessions, and background processes maintain the database. DBWn handles dirty database blocks, while LGWR writes redo information.

Oracle Storage Structure

The principal physical structures are datafiles, control files, and online redo log files. The logical hierarchy is tablespace → segment → extent → data block.

Database Administration

A DBA manages database installation, users, security, storage, backup, recovery, performance, availability, and maintenance so that the database remains reliable and protected.

SQL*Plus

SQL*Plus is an Oracle client environment for SQL, PL/SQL, scripts, variables, and formatted output. SQL statements are processed by Oracle, while SQL*Plus commands control the client environment.

Accepting Values at Runtime

Substitution variables allow scripts to accept changing values during execution. & requests substitution, && can retain a value, and ACCEPT provides controlled input prompting.

SQL Commands

DDL defines database structures, while DML manipulates stored rows. SELECT retrieves data, joins combine row sources, and subqueries use one query's result inside another statement.

Tables and Constraints

Tables store rows and columns. PRIMARY KEY identifies rows, FOREIGN KEY connects related tables, CHECK restricts values, and ALTER TABLE changes table definitions.

Indexes

Indexes provide alternative access paths to data and can accelerate selective searches. They must be designed according to table size, selectivity, query patterns, and DML workload.

Views

A view is a stored query presented like a virtual table. It simplifies SQL and controls the representation of base-table data without normally storing a separate copy of every result row.

Synonyms

A synonym is an alternative object name. It reduces dependence on long schema-qualified names but does not provide privileges on the underlying object.

Sequences

A sequence independently generates numeric values. NEXTVAL generates the next number and CURRVAL returns the current session value after NEXTVAL has been used.

Table Partitioning

Partitioning divides a logical table into smaller partitions. Major methods include range, list, hash, and composite partitioning, with partition pruning providing potential access benefits.

Materialized Views

A materialized view physically stores a query result and periodically refreshes it. COMPLETE, FAST, and FORCE are major refresh approaches.

Data Manipulation Language

INSERT adds rows, UPDATE changes rows, DELETE removes rows, and MERGE can combine update and insert behavior. WHERE controls the rows targeted by UPDATE and DELETE.

Joins

Joins combine related table sources. Inner joins retain matches, outer joins preserve selected unmatched rows, self joins relate rows within one table, and cross joins produce all combinations.

Subqueries

A subquery places one SQL query inside another. Subqueries can return single or multiple rows, reference outer-query rows through correlation, or be tested using operators such as IN and EXISTS.

Key Formulas and Points Sheet

Last-Minute Revision Sheet