Oracle
Basic PL/SQL Interview Questions and Answers
1. What is Oracle Database, and why is it widely used?
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.
2. What is Oracle SQL, and how does it differ from PL/SQL?
Oracle SQL (Structured Query Language) is a standard language used to interact with databases, whereas PL/SQL (Procedural Language/SQL) is Oracle's procedural extension of SQL.
Differences:
SQL:
- 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;
PL/SQL:
- Supports procedural constructs like loops and conditions.
- Allows the creation of triggers, functions, and stored procedures.
3. Explain Oracle Architecture in detail.
Oracle Database architecture consists of the following major components:
Instance:
- Comprises the Memory Structure (SGA and PGA) and Background Processes (like DBWR, LGWR).
- Responsible for managing database operations.
Database:
- The physical storage, including datafiles, redo logs, and control files.
Processes:
- User Processes: Initiated by users to execute SQL.
- Server Processes: Handle user requests.
- Background Processes: Manage tasks like writing to disk and log handling.
Oracle architecture ensures efficient data storage, retrieval, and transaction management.
4. What are Oracle Datafiles, and why are they important?
Datafiles in Oracle store the actual database data. Each Oracle database consists of one or more datafiles, which can include table data, indexes, and other objects.
Key Points:
- Physical Structure: Datafiles are stored on the disk.
- Logical Storage: Datafiles are associated with tablespaces.
- Types: Temporary, undo, and permanent datafiles.
Datafiles are critical for maintaining the integrity and performance of the database.
5. What is a Tablespace in Oracle?
A tablespace is a logical storage unit in Oracle that groups related logical structures like tables and indexes.
Types of Tablespaces:
- SYSTEM and SYSAUX: Contain metadata and internal objects.
- Temporary Tablespace: Used for intermediate operations.
- Undo Tablespace: Tracks undo information for rollback.
Tablespaces enable efficient database management and storage allocation.
6. What are Oracle Database Constraints?
Constraints in Oracle ensure data integrity and consistency.
Types of Constraints:
- Primary Key: Ensures unique and non-null values.
- Foreign Key: Enforces referential integrity.
- Unique Constraint: Ensures unique values.
- Check Constraint: Validates conditions for data.
7. What is Oracle RAC (Real Application Clusters)?
Oracle RAC allows multiple servers to access the same database simultaneously.
Advantages:
- High availability.
- Scalability.
- Load balancing.
RAC ensures business continuity by distributing workloads across nodes.
8. What are Oracle Indexes?
Indexes improve the performance of SQL queries by reducing search time.
Types:
- B-Tree Indexes: Default index type.
- Bitmap Indexes: Efficient for low-cardinality columns.
- Unique Indexes: Enforce unique constraints.
Indexes should be used judiciously to optimize performance.
9. Explain Oracle Backup and Recovery.
Oracle offers comprehensive backup and recovery options using RMAN (Recovery Manager).
Backup Types:
- Full Backup: Includes the entire database.
- Incremental Backup: Captures changes since the last backup.
Recovery options include point-in-time recovery and disaster recovery.
10. What is a Materialized View in Oracle?
A materialized view stores the results of a query physically, improving query performance.
Uses:
- Replicating data.
- Aggregating data for reports.
11. What is a Sequence in Oracle, and how is it used?
A sequence in Oracle is an object that generates a unique set of numeric values. It is commonly used to create primary keys for tables.
Key Features:
- Auto Increment: Automatically generates sequential numbers.
- Cycle Option: Resets the sequence when it reaches the maximum value.
12. What is the Difference Between DELETE, TRUNCATE, and DROP in Oracle?
These commands perform different operations on database objects:
DELETE:
- Removes specific rows from a table.
- Can include a WHERE clause.
- Can be rolled back.
Example:
DELETE FROM employees WHERE emp_id = 101;
TRUNCATE:
- Removes all rows from a table but retains the structure.
- Cannot be rolled back.
Example:
TRUNCATE TABLE employees;
DROP:
- Deletes the table and its structure permanently.
Example:
DROP TABLE employees;
13. What is Oracle Partitioning, and Why is it Used?
Partitioning divides a large table into smaller, manageable pieces called partitions, improving query performance and management.
Types of Partitioning:
- Range Partitioning: Based on column ranges.
- List Partitioning: Based on a list of values.
- Hash Partitioning: Distributes rows evenly.
Partitioning enhances data retrieval and load balancing.
14. Explain Oracle Triggers and Their Types.
A trigger in Oracle is a procedural code that automatically executes in response to specific events on a table or view.
Types of Triggers:
- 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.
15. What is Oracle ASM (Automatic Storage Management)?
ASM is a storage management solution that simplifies the administration of Oracle Database storage.
Key Features:
- Automatic File Distribution: Ensures data is evenly distributed.
- High Availability: Supports redundancy.
- Easy Scalability: Simplifies adding or removing disks.
ASM is essential for managing large databases efficiently.
16. What is a Cursor in Oracle, and What Are Its Types?
A cursor in Oracle is a pointer that allows row-by-row processing of query results.
Types:
- Implicit Cursor: Automatically created for DML operations.
- Explicit Cursor: Defined by users for complex operations.
Example of Explicit Cursor:
DECLARE
CURSOR emp_cursor IS SELECT name, salary FROM employees;
BEGIN
FOR emp IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp.name || ' earns ' || emp.salary);
END LOOP;
END;
17. What is the Use of Oracle Data Pump?
Oracle Data Pump is a utility for high-speed import and export of database objects and data.
Key Features:
- Faster than traditional export/import tools.
- Supports fine-grained object selection.
- Enables parallel processing.
Command Example:
expdp username/password DIRECTORY=dpump_dir DUMPFILE=export_file.dmp LOGFILE=export.log FULL=Y
18. Explain Oracle’s ACID Properties.
ACID stands for Atomicity, Consistency, Isolation, and Durability, which ensure reliable database transactions:
- Atomicity: Transactions are all-or-nothing.
- Consistency: Ensures data integrity.
- Isolation: Prevents interference between transactions.
- Durability: Guarantees permanence after commit.
ACID properties are crucial for maintaining database reliability.
19. What is Oracle Flashback Technology?
Oracle Flashback Technology allows users to view and restore historical data.
Features:
- 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);
20. What Are Oracle Synonyms, and Why Are They Used?
A synonym is an alias for a database object like a table or view.
Benefits:
- Simplifies object access.
- Provides abstraction.
Example:
CREATE SYNONYM emp_synonym FOR hr.employees;
SELECT * FROM emp_synonym;
21. What is Oracle’s Data Guard?
Oracle Data Guard provides disaster recovery and data availability.
Components:
- Primary Database: Active database.
- Standby Database: Backup database.
Data Guard ensures minimal downtime during failures.
22. What is an Oracle View, and How is it Used?
A view is a virtual table based on a SQL query.
Benefits:
- Simplifies complex queries.
- Provides data security.
Example:
CREATE VIEW emp_view AS
SELECT emp_id, name FROM employees WHERE salary > 5000;
SELECT * FROM emp_view;
23. What is Oracle’s RMAN (Recovery Manager)?
RMAN is Oracle’s utility for database backup and recovery.
Features:
- Incremental backups.
- Automatic restoration.
- Validation of backups.
RMAN simplifies disaster recovery.
24. What Are Oracle Packages?
A package is a collection of related procedures, functions, and other PL/SQL constructs.
Example:
CREATE PACKAGE emp_pkg IS
PROCEDURE update_salary(emp_id NUMBER, increment NUMBER);
END;
Packages improve code organization.
25. What is Oracle PL/SQL Exception Handling?
PL/SQL supports exception handling to manage runtime errors.
Example:
BEGIN
SELECT salary INTO emp_salary FROM employees WHERE emp_id = 101;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found!');
END;
Exception handling ensures robust and error-free applications.
