Oracle Database, developed by Oracle Corporation, is a multi-model relational database management system (RDBMS). It is widely used for its scalability, reliability, and ability to handle large datasets across distributed environments. Oracle supports features such as PL/SQL, data partitioning, and advanced security measures, making it suitable for enterprise-level applications.

Key features of Oracle Database:

• High Availability: Through features like Real Application Clusters (RAC).
• Backup and Recovery: Offers RMAN (Recovery Manager) for efficient data recovery.
• Performance Tuning: Includes tools like Automatic Workload Repository (AWR) and SQL Performance Analyzer.

Oracle is commonly used in industries like banking, healthcare, and e-commerce due to its robust architecture and advanced capabilities.

• Executes one query at a time.
• Used for DML (Data Manipulation Language), DDL (Data Definition Language), and DCL (Data Control Language).
• Example: SELECT * FROM employees;

• Supports procedural constructs like loops and conditions.
• Allows the creation of triggers, functions, and stored procedures.

• Comprises the Memory Structure (SGA and PGA) and Background Processes (like DBWR, LGWR).
• Responsible for managing database operations.

• The physical storage, including datafiles, redo logs, and control files.

• User Processes: Initiated by users to execute SQL.
• Server Processes: Handle user requests.
• Background Processes: Manage tasks like writing to disk and log handling.

• Physical Structure: Datafiles are stored on the disk.
• Logical Storage: Datafiles are associated with tablespaces.
• Types: Temporary, undo, and permanent datafiles.

• SYSTEM and SYSAUX: Contain metadata and internal objects.
• Temporary Tablespace: Used for intermediate operations.
• Undo Tablespace: Tracks undo information for rollback.

• Primary Key: Ensures unique and non-null values.
• Foreign Key: Enforces referential integrity.
• Unique Constraint: Ensures unique values.
• Check Constraint: Validates conditions for data.

• High availability.
• Scalability.
• Load balancing.

• B-Tree Indexes: Default index type.
• Bitmap Indexes: Efficient for low-cardinality columns.
• Unique Indexes: Enforce unique constraints.

• Full Backup: Includes the entire database.
• Incremental Backup: Captures changes since the last backup.

• Replicating data.
• Aggregating data for reports.

• Auto Increment: Automatically generates sequential numbers.
• Cycle Option: Resets the sequence when it reaches the maximum value.

• Removes specific rows from a table.
• Can include a WHERE clause.
• Can be rolled back.

• Removes all rows from a table but retains the structure.
• Cannot be rolled back.

• Deletes the table and its structure permanently.

• Range Partitioning: Based on column ranges.
• List Partitioning: Based on a list of values.
• Hash Partitioning: Distributes rows evenly.

• Before Triggers: Execute before an operation (e.g., BEFORE INSERT).
• After Triggers: Execute after an operation (e.g., AFTER UPDATE).
• Instead Of Triggers: Used for views.

Example:

CREATE TRIGGER emp_salary_check

BEFORE INSERT OR UPDATE ON employees

FOR EACH ROW

BEGIN

  IF :NEW.salary < 0 THEN

    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');

  END IF;

END;

Triggers enforce business rules at the database level.

• Automatic File Distribution: Ensures data is evenly distributed.
• High Availability: Supports redundancy.
• Easy Scalability: Simplifies adding or removing disks.

• Implicit Cursor: Automatically created for DML operations.
• Explicit Cursor: Defined by users for complex operations.

• Faster than traditional export/import tools.
• Supports fine-grained object selection.
• Enables parallel processing.

• Atomicity: Transactions are all-or-nothing.
• Consistency: Ensures data integrity.
• Isolation: Prevents interference between transactions.
• Durability: Guarantees permanence after commit.

• Flashback Query: View past data using AS OF clause.
• Flashback Table: Restore a table to a previous state.
• Flashback Database: Rewind the entire database.

Example:

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

• Simplifies object access.
• Provides abstraction.

• Primary Database: Active database.
• Standby Database: Backup database.

• Simplifies complex queries.
• Provides data security.

• Incremental backups.
• Automatic restoration.
• Validation of backups.