Oracle Database Administration/Moving Data

This lesson introduces moving data.

Objectives and Skills
Objectives and skills for the moving portion of Oracle Database Administration I certification include:
 * Moving Data
 * Describe and use methods to move data (Directory objects, SQL*Loader, External Tables)
 * Explain the general architecture of Oracle Data Pump
 * Use Data Pump Export and Import to move data between Oracle databases

Readings

 * 1) Oracle: Database Utilities SQL*Loader Concepts
 * 2) Oracle: Database Utilities Overview of Oracle Data Pump
 * 3) Oracle: Database Utilities Data Pump Export
 * 4) Oracle: Database Utilities Data Pump Import

Multimedia

 * 1) YouTube: Using Oracle SQL Loader
 * 2) YouTube: Data Pump, Oracle DBA, export whole schema and import selected objects

SQL*Loader

 * 1) Use SQL*Loader.
 * 2) Create a text file with the following information. Save the file as.
 * 3) Create an SQL*Loader control file with the following information. Save the file as.
 * 4) Use SQL*Loader to load the data using the following command:
 * 5) Verify the import using the following query:
 * 6) Clean up using the following query:

External Tables

 * 1) Use an external table.
 * 2) Create a directory object pointing to an existing operating system directory using the following query:
 * 3) Put the   text file from above in the specified directory.
 * 4) Create an external table referencing the text file using the following query:
 * 5) Select data from the external table using the following query:
 * 6) Use the following query to load the data into the REGIONS table:
 * 7) Verify the import using the following query:
 * 8) Clean up using the following queries:

Spool

 * 1) Use spool to export a comma-separated-values copy of the REGIONS table.
 * 2) Review Charito: How to Write to a CSV File Using Oracle SQL*Plus.
 * 3) Use SQL*PLUS and run the following commands:
 * 4) Exit SQL*PLUS and list the files in the current directory (DIR or LS). Open the regions.csv file and view the results.

SQL Developer

 * 1) Use SQL Developer to export a comma-separated-values copy of the REGIONS table.
 * 2) Review Oracle: Using SQL Developer for Importing and Exporting.
 * 3) Follow the instructions for the Oracle: Example: Exporting Data to a Microsoft Excel File

Oracle Data Pump

 * 1) Identify the data pump directory using the following query:
 * 2) Use Enterprise Manager Data Pump Export.
 * 3) Use Enterprise Manager Database Control / Data Movement / Export to Export Files to export the HR.REGIONS table.
 * 4) Navigate to the directory specified in the export and open the exported file using a text editor. Observe the file format and contents.
 * 5) Use command-line Data Pump Export.
 * 6) Use the following command to export the HR schema:
 * 7) Use command-line Data Pump Import.
 * 8) Use the following command to import HR.REGIONS into the OE schema:
 * 9) Use the following query to verify the import:
 * 10) use the following query to clean up the import: