OracleDB Interview Questions and Answers

1. What is an Oracle database?
  • An Oracle database is a relational database management system (RDBMS) developed by Oracle Corporation. It is widely used for storing, managing, and retrieving data in a structured format.
2. What is the difference between an Oracle schema and an Oracle user?
  • An Oracle schema is a collection of database objects, such as tables, views, indexes, etc., that belong to a single user. An Oracle user is the account under which a schema resides. In other words, a schema is associated with a user.
3. What are the types of indexes in Oracle?
  • The main types of indexes in Oracle are:
    • Normal Index
    • Unique Index
    • Bitmap Index
    • Function-based Index
    • Clustered Index
    • Domain Index
4. What is the difference between a primary key and a unique key in Oracle?
  • A primary key uniquely identifies each row in a table and does not allow NULL values. A unique key also enforces uniqueness but allows a NULL value, unlike the primary key.
5. What are the different types of joins in Oracle?
  • The main types of joins in Oracle are:
    • Inner Join
    • Left Join
    • Right Join
    • Full Outer Join
    • Self Join
    • Cross Join
6. What is a foreign key in Oracle?
  • A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It ensures referential integrity between the two tables.
7. What is a table space in Oracle?
  • A tablespace in Oracle is a logical storage unit that groups related logical structures together. Tablespaces are used to store data in a physical file on the disk.
8. What are the different types of table spaces in Oracle?
  • The main types of tablespaces in Oracle are:
    • System Tablespace
    • Undo Tablespace
    • Temporary Tablespace
    • Permanent Tablespace
9. What is the difference between TRUNCATE and DELETE in Oracle?
  • The main differences between TRUNCATE and DELETE are:
    • TRUNCATE is a DDL command, whereas DELETE is a DML command.
    • TRUNCATE removes all rows from the table and cannot be rolled back, whereas DELETE can be rolled back.
    • TRUNCATE is faster as it does not log individual row deletions.
10. What is a sequence in Oracle?
  • A sequence is an object in Oracle used to generate unique numbers, often used for generating primary key values.
11. What is a cursor in Oracle?
  • A cursor is a pointer that is used to retrieve and manipulate rows in a result set from a SQL query. There are implicit cursors (automatically created by Oracle) and explicit cursors (created by the user).
12. What are the different types of cursors in Oracle?
  • The main types of cursors are:
    • Implicit Cursor
    • Explicit Cursor
13. What is a trigger in Oracle?
  • A trigger is a stored procedure that automatically executes (or "fires") in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE.
14. What is a view in Oracle?
  • A view is a virtual table based on the result of a SELECT query. It does not store data physically but presents data from one or more tables.
15. What is normalization in Oracle?
  • Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity by dividing large tables into smaller ones and ensuring relationships between them.
16. What is denormalization in Oracle?
  • Denormalization is the process of combining tables to increase query performance by reducing the number of joins. It is typically used for reporting or OLAP systems.
17. What is a synonym in Oracle?
  • A synonym is an alias or an alternative name for a database object like a table, view, sequence, or procedure, making it easier to reference.
18. What is a rollback in Oracle?
  • A rollback is a process of undoing changes made by a transaction. It restores the database to the state it was in before the transaction began.
19. What is a commit in Oracle?
  • A commit is used to permanently save the changes made by a transaction to the database.
20. What is the use of the `EXPLAIN PLAN` in Oracle?
  • The `EXPLAIN PLAN` is used to display the execution plan of a SQL query, which helps to understand how Oracle will execute a query and optimize its performance.
21. What is a materialized view in Oracle?
  • A materialized view is a precomputed query result that is stored physically on disk, unlike a regular view that is virtual. It is used to improve performance for complex queries.
22. What is the difference between `AND` and `OR` in SQL?
  • `AND` is used to combine multiple conditions where all conditions must be true, while `OR` is used to combine conditions where at least one condition must be true.
23. What is the difference between `INNER JOIN` and `OUTER JOIN`?
  • An `INNER JOIN` returns only the rows with matching values from both tables, while an `OUTER JOIN` returns rows that have matching values from one table and NULLs from the other table where no match exists.
24. What is the difference between `GROUP BY` and `ORDER BY`?
  • `GROUP BY` is used to group rows with similar values into summary rows, often used with aggregate functions. `ORDER BY` is used to sort the result set in ascending or descending order.
25. What is the purpose of the `ROWNUM` function in Oracle?
  • `ROWNUM` is used to assign a unique number to each row returned by a query, starting from 1 for the first row.
26. What are the advantages of using stored procedures in Oracle?
  • Stored procedures improve performance by reducing network traffic, increase security, and allow for reusability of code. They also encapsulate business logic within the database.
27. What is a database link in Oracle?
  • A database link is a connection between two Oracle databases that allows data to be queried or modified in one database from another.
28. What is a deadlock in Oracle?
  • A deadlock occurs when two or more transactions are waiting for each other to release locks on resources, preventing the transactions from being completed. Oracle automatically detects and resolves deadlocks.
29. What is the `NVL()` function in Oracle?
  • The `NVL()` function is used to replace NULL values with a specified value. It takes two arguments: the column and the value to replace NULLs with.
30. What is the purpose of the `TO_DATE()` function in Oracle?
  • The `TO_DATE()` function is used to convert a string into a date format in Oracle, allowing for proper date comparisons and manipulations.
31. What is the `DECODE()` function in Oracle?
  • The `DECODE()` function is used to implement conditional logic in SQL queries. It allows for a CASE-like structure to compare values and return specific results based on conditions.
32. What is the difference between `TRUNCATE` and `DROP` in Oracle?
  • `TRUNCATE` removes all rows from a table, but the table structure remains intact. `DROP` completely removes the table, including its structure and data.
33. What is the `SYSDATE` function in Oracle?
  • The `SYSDATE` function returns the current system date and time in Oracle. It is used for time-based calculations and comparisons.
34. What is the difference between `SELECT INTO` and `INSERT INTO`?
  • `SELECT INTO` is used to select data and store it into a new table, while `INSERT INTO` is used to insert data into an existing table.
35. What is a partitioned table in Oracle?
  • A partitioned table is a large table that is divided into smaller, more manageable pieces called partitions. This helps improve performance and manageability for large datasets.
36. What is the `ROWID` in Oracle?
  • ROWID is a unique identifier for rows in a table, providing the physical address of a row in the database. It can be used to directly access rows for fast retrieval.
37. What is the difference between `CHAR` and `VARCHAR2` in Oracle?
  • `CHAR` is used to store fixed-length character strings, while `VARCHAR2` is used to store variable-length character strings. `VARCHAR2` is preferred in most cases as it is more efficient.