Oracle Interview questions

1. What are the different types of joins in Oracle?

   • In Oracle, the main types of joins are:

     • Inner Join: Returns records that have matching values in both tables.

     • Left (Outer) Join: Returns all records from the left table and the matched records from the right table.

     • Right (Outer) Join: Returns all records from the right table and the matched records from the left table.

     • Full (Outer) Join: Returns all records when there is a match in either left or right table.

2. What is an Index in Oracle?

   • An index is a database object that improves the speed of data retrieval operations on a database table at the cost of additional space and slower writes. It consists of an ordered list of entries, where each entry contains a reference to the corresponding table row.

3. What is a View in Oracle?

   • A view is a virtual table based on the result set of a SELECT query. It does not store any data on its own but retrieves data from the tables upon execution. Views can be used to simplify complex queries, enforce security, and provide a logical representation of data.

4. Explain the difference between DELETE and TRUNCATE in Oracle.

   • DELETE is a DML (Data Manipulation Language) operation that removes rows from a table based on a condition. It generates undo and redo data, allowing the operation to be rolled back.

   • TRUNCATE is a DDL (Data Definition Language) operation that removes all rows from a table. It deallocates space and generates minimal undo and redo data, making it faster than DELETE, but it cannot be rolled back.

5. What are Oracle Constraints?

   • Constraints are rules that enforce data integrity and define certain properties for columns or tables. Common constraints in Oracle include:

     • Primary Key: Uniquely identifies each row in a table.

     • Foreign Key: Ensures referential integrity between two tables.

     • Unique: Ensures that all values in a column are unique.

     • Check: Enforces a condition on a column.

6. Explain Oracle Data Pump.

   • Oracle Data Pump is a utility for exporting and importing data and metadata between Oracle databases. It provides fast data movement and support for advanced features such as parallelism and network-based import/export.

Exporting Data:
To export data from a table using Oracle Data Pump, you can use the expdp command. For example:
arduino
Copy code
expdp username/password DIRECTORY=datapump_directory DUMPFILE=export.dmp TABLES=table_name

1. • username/password: The credentials of the user performing the export.

   • DIRECTORY: The directory object where the export file will be written.

   • DUMPFILE: The name of the export file.

   • TABLES: The name of the table(s) to export.

Importing Data:
To import the data exported in the previous step, you can use the impdp command. For example:
arduino
Copy code
impdp username/password DIRECTORY=datapump_directory DUMPFILE=export.dmp

1. • username/password: The credentials of the user performing the import.

   • DIRECTORY: The directory object where the export file is located.

   • DUMPFILE: The name of the export file.

2. Additional Options:
   Oracle Data Pump provides various options to customize the export and import process. Some common options include:

   • INCLUDE: Specifies the type of objects to include in the export (e.g., TABLES, SCHEMAS, or FULL).

   • EXCLUDE: Specifies the type of objects to exclude from the export.

   • PARALLEL: Enables parallel execution of the export or import job for improved performance.

   • REMAP_SCHEMA: Allows you to map objects from one schema to another during the import process.

   • LOGFILE: Specifies the name of the log file to record the export or import session details.

Example:

expdp hr/hr DIRECTORY=dpump_dir DUMPFILE=hr_export.dmp LOGFILE=hr_export.log SCHEMAS=HR

This command exports all objects owned by the HR schema into the file hr_export.dmp located in the dpump_dir directory, with detailed logging recorded in hr_export.log.

impdp system/password DIRECTORY=dpump_dir DUMPFILE=hr_export.dmp LOGFILE=hr_import.log REMAP_SCHEMA=HR:NEW_HR

3. This command imports the data exported previously from the HR schema and remaps it to a new schema named NEW_HR.

Oracle Data Pump provides a flexible and efficient way to move data and metadata between Oracle databases, facilitating tasks such as database migrations, backups, and data warehousing operations.