1. Oracle Database Overview
Oracle Database is structured into physical and logical components that store data securely while ensuring data integrity, concurrency, and availability.
Key Concepts
- Instance: An instance is the collection of memory structures and processes that manage the database. Each instance has a unique System Global Area (SGA) and background processes.
- Database: A database is a collection of physical files that store data.
- Tablespace: A tablespace is a logical storage unit that contains one or more datafiles.
- Schema: A schema is a collection of database objects, such as tables, views, indexes, and procedures, owned by a user.
- Datafile: A datafile is a physical file on the disk that contains the actual data stored in tables.
- Redo Logs: Redo logs record all changes made to the database, ensuring recoverability.
- Control File: A control file contains information about the structure of the database, including the database name and the locations of datafiles and redo logs.
2. SQL Fundamentals
Structured Query Language (SQL) is the standard language for interacting with relational databases. SQL commands can be broadly categorized into Data Definition Language (DDL), Data Manipulation Language (DML), Data Query Language (DQL), and Data Control Language (DCL).
Data Definition Language (DDL)
DDL commands are used to define, modify, and delete database objects such as tables, views, and indexes.
CREATE TABLE:
The CREATE TABLE statement is used to create a new table.
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
salary NUMBER
);
The ALTER TABLE statement modifies an existing table (add columns, modify column types, drop columns).
-- Add a column
ALTER TABLE employees ADD (email VARCHAR2(100));
-- Modify a column
ALTER TABLE employees MODIFY (salary NUMBER(10, 2));
-- Drop a column
ALTER TABLE employees DROP COLUMN email;
The DROP TABLE statement deletes a table and its data from the database.
DROP TABLE employees;
The TRUNCATE TABLE statement removes all rows from a table but preserves its structure.
TRUNCATE TABLE employees;
DML commands are used to insert, update, delete, and merge data in the database.
INSERT INTO:
The INSERT INTO statement adds new rows to a table.
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', SYSDATE, 60000);
The UPDATE statement modifies existing data in a table.
UPDATE employees
SET salary = 70000
WHERE employee_id = 1;
The DELETE statement removes rows from a table.
DELETE FROM employees
WHERE employee_id = 1;
The MERGE statement is used to perform an “upsert” operation, which means either updating an existing row or inserting a new row if it doesn’t exist.
MERGE INTO employees e
USING (SELECT 1 AS employee_id FROM dual) d
ON (e.employee_id = d.employee_id)
WHEN MATCHED THEN
UPDATE SET salary = 75000
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name, hire_date, salary)
VALUES (1, 'John', 'Doe', SYSDATE, 60000);
The SELECT statement is used to query data from one or more tables.
Basic SELECT Query:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;
JOIN statements are used to combine rows from two or more tables based on related columns.
- INNER JOIN: Returns rows that have matching values in both tables.
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
- LEFT JOIN (OUTER JOIN): Returns all rows from the left table and matching rows from the right table.
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
- CROSS JOIN: Returns the Cartesian product of both tables (i.e., all possible combinations of rows).
SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;
The GROUP BY clause groups rows with the same values into summary rows, and the HAVING clause filters groups based on conditions.
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
The ORDER BY clause is used to sort query results.
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
DCL commands manage access to the database, allowing administrators to grant and revoke privileges.
GRANT:
The GRANT statement is used to give a user or role access rights to the database.
GRANT SELECT, INSERT ON employees TO user1;
The REVOKE statement is used to remove previously granted access rights.
REVOKE INSERT ON employees FROM user1;
3. PL/SQL Programming
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s procedural extension to SQL, allowing you to write complex logic and control structures within the database.
PL/SQL Block Structure:
A PL/SQL block consists of three sections:
- DECLARE: Optional section for defining variables, constants, and cursors.
- BEGIN: The main executable section where SQL and procedural statements are written.
- EXCEPTION: Optional section for handling exceptions (errors).
- END: Marks the end of the block.
DECLARE
v_salary NUMBER(10, 2);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 1;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
/
IF-THEN-ELSE:
The IF-THEN-ELSE control structure is used for conditional branching.
IF v_salary > 50000 THEN
DBMS_OUTPUT.PUT_LINE('High salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Low salary');
END IF;
PL/SQL supports different types of loops: LOOP, WHILE, and FOR.
- Basic LOOP:
LOOP
DBMS_OUTPUT.PUT_LINE('This is a loop');
EXIT; -- Exits the loop
END LOOP;
- WHILE LOOP:
WHILE v_salary > 50000 LOOP
DBMS_OUTPUT.PUT_LINE('Salary is high');
v_salary := v_salary - 5000; -- Decrement salary
END LOOP;
- FOR LOOP:
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration ' || i);
END LOOP;
Cursors are used to retrieve and manipulate rows returned by a query.
Explicit Cursors:
An explicit cursor is defined and controlled by the programmer.
DECLARE
CURSOR emp_cursor IS SELECT first_name, salary FROM employees;
v_first_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_first_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_first_name || ': ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;
/
An implicit cursor is automatically created for SELECT statements that return a single row.
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 1;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
PL/SQL provides mechanisms for handling exceptions (runtime errors).
Predefined Exceptions:
Oracle provides several predefined exceptions for common errors, such as NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE.
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows found.');
END;
You can define your own exceptions for custom error handling.
DECLARE
e_salary_too_high EXCEPTION;
BEGIN
IF v_salary > 100000 THEN
RAISE e_salary_too_high;
END IF;
EXCEPTION
WHEN e_salary_too_high THEN
DBMS_OUTPUT.PUT_LINE('Salary is too high.');
END;
4. Oracle Database Administration
Users and Roles:
Oracle provides a robust system for managing users and their access rights through roles.
Creating Users:
The CREATE USER statement is used to create a new user account in the database.
CREATE USER john IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
Use the GRANT statement to give a user access to system or object privileges.
GRANT CONNECT, RESOURCE TO john;
GRANT SELECT, INSERT ON employees TO john;
Roles simplify the management of user privileges by grouping them together.
CREATE ROLE hr_role;
GRANT SELECT, INSERT, UPDATE ON employees TO hr_role;
GRANT hr_role TO john;
Tablespaces allow you to organize data storage in Oracle databases.
Creating a Tablespace:
CREATE TABLESPACE data_ts
DATAFILE '/u01/app/oracle/oradata/mydb/data01.dbf' SIZE 100M;
To resize or add datafiles to an existing tablespace:
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/data01.dbf' RESIZE 200M;
Backups with RMAN (Recovery Manager):
RMAN is Oracle’s tool for performing backups and recoveries.
- Full Backup:
RMAN> BACKUP DATABASE;
- Incremental Backup:
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
- Recover the Database:
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
Oracle provides several tools and techniques for monitoring and tuning database performance.
Using EXPLAIN PLAN:
The EXPLAIN PLAN statement shows the execution plan for a SQL query.
EXPLAIN PLAN FOR
SELECT first_name, last_name FROM employees WHERE salary > 50000;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Indexes improve query performance by allowing the database to find rows more efficiently.
- Creating an Index:
CREATE INDEX idx_salary ON employees(salary);
- Rebuilding an Index:
ALTER INDEX idx_salary REBUILD;
5. Oracle Advanced Features
Partitioning:
Partitioning divides large tables into smaller, more manageable pieces, improving performance and manageability.
Creating a Partitioned Table:
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('01-JAN-2023', 'DD-MON-YYYY'))
);
Materialized views store the results of a query, making it faster to retrieve precomputed data.
Creating a Materialized View:
CREATE MATERIALIZED VIEW emp_sales_mv
AS SELECT e.first_name, e.last_name, s.amount
FROM employees e, sales s
WHERE e.employee_id = s.employee_id;
Oracle Data Pump is used for high-speed data export and import.
Exporting Data:
expdp john/password DIRECTORY=dpump_dir1 DUMPFILE=john.dmp SCHEMAS=john;
impdp john/password DIRECTORY=dpump_dir1 DUMPFILE=john.dmp SCHEMAS=john;
6. Conclusion
Oracle Database is a robust, scalable, and feature-rich RDBMS that is essential for enterprise-level applications. This Elysium Spark Note has covered Oracle fundamentals, including SQL, PL/SQL, database administration, and advanced features like partitioning and materialized views. By mastering these key concepts and tools, you’ll be able to manage Oracle databases effectively, optimize performance, and ensure data integrity and security.