Oracle Database Tutorial 56:Data Pump impdp table and Duplicate (Remap_table ) table

Summary

In this Oracle Database tutorial, Manish from RebellionRider.com demonstrates how to import tables using the impdp Data Pump utility. He walks viewers through importing a table from a dump file and shows how to rename a table during the import process using the REMAP_TABLE parameter. The tutorial includes detailed steps on connecting to the database, executing import commands, and handling common parameters such as DIRECTORY, DUMPFILE, LOGFILE, and TABLES. This tutorial also emphasizes error tracking using log files and offers tips for efficient data management in Oracle.

Takeaways

• 😀 Importing tables using the impdp Data Pump utility in Oracle allows you to load data from dump files into a target system.
• 😀 You can specify the schema and table you want to import with the 'TABLES' parameter in the impdp command.
• 😀 The 'DUMPFILE' parameter specifies the location of the dump file that contains the exported data to be imported.
• 😀 Always use the 'LOGFILE' parameter to create a log file, which helps track errors during the import process.
• 😀 To import a table, ensure you are at the command prompt, not inside SQL Developer, when executing the impdp command.
• 😀 You can drop a table before importing it using impdp, ensuring no existing data interferes with the import.
• 😀 The 'DIRECTORY' parameter in the impdp command should point to the directory object where the dump file is located.
• 😀 When importing, you need to specify the table name in the 'TABLES' parameter, which defines what is being imported from the dump file.
• 😀 To rename a table during the import process, use the 'REMAP_TABLE' parameter with the old and new table names separated by a colon.
• 😀 The 'REMAP_TABLE' parameter allows you to import a table with a new name without affecting the original table in the schema.
• 😀 Always verify the import by querying the table in SQL Developer to ensure that the data and structure have been successfully loaded.

Q & A

What is the main topic of the tutorial?

The main topic of the tutorial is how to import tables using the `impdp` Data Pump Import utility in Oracle Database, as well as how to rename a table during the import process using the `REMAP_TABLE` parameter.

What is the significance of the `impdp` Data Pump Import utility?

`impdp` is an Oracle utility used to load data from dump files created by the `expdp` Data Pump Export utility. It allows users to import tables, schemas, or entire databases into an Oracle database.

What is the prerequisite to running the `impdp` command?

The prerequisite to running the `impdp` command is that the data must first be exported using the `expdp` Data Pump Export utility, and the dump file must be available for import.

How do you drop a table before importing it in this tutorial?

In the tutorial, the `superheros` table is dropped using the SQL command: `DROP TABLE superheros;`. This is done to simulate the import process, as the table needs to be removed before it can be imported again.

What command is used to import a table using `impdp`?

The command used to import a table is: `impdp hr/hr@ORCL DIRECTORY=exp_table DUMPFILE=superhero.dmp LOGFILE=sh_imp.log TABLES=superheros;`. This command specifies the directory, dump file, log file, and the table to import.

What is the purpose of the `DIRECTORY` parameter in the `impdp` command?

The `DIRECTORY` parameter specifies the directory object where the dump files are located. The value is a directory object in Oracle, not a file path.

What is the role of the `LOGFILE` parameter in the `impdp` command?

The `LOGFILE` parameter allows the user to specify the name of the log file where the details of the import process are recorded. This is useful for troubleshooting if errors occur during the import.

What does the `REMAP_TABLE` parameter do in the `impdp` command?

The `REMAP_TABLE` parameter allows the user to rename a table during the import process. It takes two values: the original table name and the new table name, separated by a colon.

What is the advantage of using the `REMAP_TABLE` parameter in table import?

The advantage of using the `REMAP_TABLE` parameter is that it allows you to import the same table with a new name, without affecting existing tables in the target schema. It is particularly useful when you want to create a copy of a table without deleting or overwriting the original.

Can you perform an `impdp` import command directly in SQL Developer?

No, you cannot execute the `impdp` command directly in SQL Developer. The `impdp` command must be run from the command prompt, not from within SQL Developer. You first exit SQL Developer and run the command from the system's command line interface.