Oracle Database is a multi-model relational database management system (RDBMS) developed by Oracle Corporation. It is designed for enterprise grid computing and provides a robust, efficient, and secure platform for managing large amounts of data. Oracle supports SQL for querying and managing data and PL/SQL for procedural programming.

It allows scalability, high availability, and supports various data types, partitioning, clustering, and in-memory processing. It also includes features like Real Application Clusters (RAC), Data Guard, and Flashback technology, which provide fault tolerance and disaster recovery. Oracle databases are used widely in enterprise environments due to their stability, performance, and extensive toolset for backup, security, and performance tuning.

A table is a database object that physically stores data in rows and columns. It is where actual data resides. A view, on the other hand, is a virtual table that provides a way to look at data from one or more tables through a SELECT query.

Views do not store data themselves; they display data dynamically when queried. Views can be used to simplify complex queries, restrict access to sensitive data, and present data in a specific format. While tables are used for data storage, views act as a layer of abstraction or security over those tables.

PL/SQL stands for Procedural Language extensions to SQL. It is Oracle's procedural language that integrates SQL with the features of procedural programming languages like loops, conditions, and exception handling. PL/SQL allows for creating powerful applications that can manage and manipulate data effectively. Developers use PL/SQL to write stored procedures, functions, packages, and triggers.

These blocks of code can be reused and are stored in the database for performance and security. PL/SQL enhances the capabilities of SQL by supporting procedural constructs, which helps in writing complex business logic inside the database, reducing the need for client-side logic.

A cursor is a database object used in PL/SQL to retrieve and manipulate query results row by row. Oracle provides two types of cursors: implicit and explicit. Implicit cursors are automatically created by Oracle when a DML statement (like SELECT INTO) is executed. Explicit cursors are defined by the programmer for queries that return more than one row.

With explicit cursors, you can open the cursor, fetch each row individually, and close the cursor once processing is complete. Cursors are useful when row-by-row processing is required, such as during complex data transformations or when calling stored procedures on each row.

Constraints are rules applied to table columns to enforce data integrity in Oracle. They ensure that the data entered into a table meets specific criteria. Common types of constraints include:

• PRIMARY KEY – ensures uniqueness and not null.
• FOREIGN KEY – maintains referential integrity between tables.
• UNIQUE – ensures all values in a column are different.
• NOT NULL – prevents null values in a column.

• 1NF (First Normal Form): Eliminates repeating groups and ensures atomic values.
• 2NF (Second Normal Form): Achieves 1NF and removes partial dependencies.
• 3NF (Third Normal Form): Achieves 2NF and removes transitive dependencies.
• BCNF (Boyce-Codd Normal Form): A stronger version of 3NF that handles anomalies not covered by 3NF. Normalization promotes consistency, minimizes duplication, and enhances query performance in relational databases.

• B-tree Index: Default and efficient for high-cardinality columns.
• Bitmap Index: Efficient for columns with low cardinality.
• Function-Based Index: Created on expressions or functions.
• Unique Index: Ensures uniqueness of the indexed column(s). While indexes speed up SELECT operations, they can slow down INSERT, UPDATE, and DELETE operations due to overhead in maintaining the index structure.

sql
📄
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

A trigger in Oracle is a stored PL/SQL block that automatically executes when a specified event occurs in the database. Triggers are used for auditing, enforcing business rules, validating input data, and maintaining integrity across related tables. Triggers can be defined to fire BEFORE or AFTER INSERT, UPDATE, or DELETE operations on a table or view. There are row-level triggers (for each affected row) and statement-level triggers (once per operation). Example:

sql
📄
CREATE OR REPLACE TRIGGER trg_audit
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  :NEW.created_date := SYSDATE;
END;

Both functions and procedures are PL/SQL subprograms, but they serve different purposes. A function returns a single value and is often used in SQL queries. A procedure performs an action and may return none, one, or multiple values via OUT parameters. Functions are used when you need to compute and return a value, such as a salary calculation.

Procedures are better suited for operations like inserting records or processing logic that affects multiple tables. Syntax-wise, a function must include a RETURN clause, while a procedure does not. Both can accept parameters (IN, OUT, IN OUT) and are stored in the database.

Oracle Data Guard is a high availability and disaster recovery solution that ensures data protection and business continuity. It maintains one or more standby databases as copies of the primary database. These standby databases can be physical, logical, or snapshot. Data Guard automatically replicates changes from the primary to standby databases via redo logs. In case of a failure, a standby database can quickly be switched to primary, minimizing downtime.

It supports both manual and automatic failover, ensuring minimal data loss. Data Guard also offloads reporting and backups from the primary to standby databases, enhancing overall system performance and availability.

A package is a PL/SQL object that groups logically related procedures, functions, variables, and cursors into a single unit. A package has two parts: the specification (declaration) and the body (implementation). Using packages improves code organization, reuse, and security. Packages also provide performance benefits by reducing disk I/O through session-level memory caching. For example:

sql
📄
CREATE PACKAGE BODY math_pkg AS
  FUNCTION add(x NUMBER, y NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN x + y;
  END;
END math_pkg;

A synonym in Oracle is an alias or alternate name for another database object such as a table, view, sequence, or procedure. Synonyms provide easier and more flexible access to objects, especially when dealing with long or complex object names or accessing objects in another schema. There are two types: public and private synonyms. Public synonyms are accessible to all users, while private synonyms are accessible only within the user’s schema. Syntax example:

sql
📄
CREATE SYNONYM emp FOR hr.employees;

• INNER JOIN: Returns rows with matching values in both tables.
• LEFT OUTER JOIN: Returns all rows from the left table, and matched rows from the right.
• RIGHT OUTER JOIN: Returns all rows from the right table, and matched rows from the left.
• FULL OUTER JOIN: Returns all rows when there is a match in either table.

schema in Oracle is a logical collection of database objects—such as tables, views, indexes, sequences, procedures, and packages—that belong to a specific database user. When a user is created, a corresponding schema with the same name is also created. Although schemas are linked to users, they act as namespaces to help organize and manage objects. A single user can only access another schema’s objects if granted the appropriate privileges.

For example, hr.employees refers to the employees table in the hr schema. Using schemas helps in managing security, organizing data, and separating database components in a multi-user environment.

The Oracle Listener is a process that runs on the database server and manages incoming client connection requests. It listens for connection requests on a specified network protocol and port (default is TCP on port 1521) and then establishes a connection between the client and the appropriate database instance.

The listener uses a configuration file called listener.ora to define connection parameters and services. It supports multiple databases and services on the same server. Tools like lsnrctl are used to manage the listener. Without the listener, remote clients cannot access the Oracle database. It’s essential for distributed and client-server architecture.

Logical backup involves backing up database objects like tables, schemas, or the entire database in a human-readable format, such as using Data Pump (expdp/impdp) or the older exp/imp utilities. This backup includes the structure and data and is portable across platforms.

Redo logs in Oracle record all changes made to the database as a form of write-ahead logging, which is crucial for data recovery. They consist of redo entries (change vectors) that describe changes to data blocks. Redo logs are written by the Log Writer (LGWR) process and are used to recover the database in case of a crash.

Oracle maintains multiple redo log groups and members for fault tolerance. When a log group fills, it triggers a log switch, and the next group is used. Archived redo logs (if archiving is enabled) are essential for point-in-time recovery and Data Guard replication.

A hot backup is taken while the database is open and running, allowing users to access the database during the backup. It is suitable for systems that require 24x7 availability. Tablespaces must be placed in backup mode before copying the datafiles. Hot backups capture changes using archived redo logs.

A control file is a critical component of an Oracle database. It contains metadata required to start and manage the database, such as database name, timestamp of creation, location of datafiles and redo log files, checkpoint information, and RMAN backup details.

Oracle requires at least one control file to operate, but it is recommended to maintain multiple copies (multiplexing) for redundancy. Control files are updated continuously and are crucial during recovery. If a control file is lost or corrupted and no backup exists, the database cannot be mounted, making it essential for recovery and integrity of the system.

• Database Buffer Cache – stores copies of data blocks read from disk.
• Shared Pool – stores parsed SQL, PL/SQL code, and data dictionary info.
• Redo Log Buffer – stores redo entries before writing to redo logs.
• Large Pool and Java Pool – used for special operations like parallel execution and Java code. Proper sizing of SGA is critical for performance.

• Rapid provisioning and cloning of databases.
• Centralized management of multiple PDBs.
• Reduced overhead and better consolidation.
• Simplified patching and upgrades. This architecture is ideal for cloud environments and supports scalability with minimal resource duplication.

• Logins/logouts
• DML operations (INSERT, UPDATE, DELETE)
• DDL statements (CREATE, DROP)
• Access to specific tables or schemas Audit records are stored in DBA_AUDIT_TRAIL or OS files, depending on the configuration. Unified Auditing consolidates all audit trails into one and supports fine-grained auditing. This helps organizations meet security standards and detect suspicious or unauthorized activity in real-time.

• Key responsibilities include:
• Detecting and resolving node or process failures.
• Managing virtual IP addresses.
• Starting/stopping Oracle instances in a cluster.
• Managing dependencies and resource placement. Clusterware uses a voting disk and an OCR (Oracle Cluster Registry) to store cluster configuration and node information. It can also be used for non-database high-availability applications, making it a core component of Oracle’s grid infrastructure.