# 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

* Oracle: Database Utilities SQL*Loader Concepts
* Oracle: Database Utilities Overview of Oracle Data Pump
* Oracle: Database Utilities Data Pump Export
* Oracle: Database Utilities Data Pump Import

## Multimedia

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

## Activities
### SQL*Loader

* Use SQL*Loader.
** Create a text file with the following information. Save the file as regions.txt. ID, REGION 101, Eastern Europe 102, Western Europe 103, North America 104, Central America 105, South America 106, Central Asia 107, Eastern Asia 108, Southern Asia 109, Southeastern Asia 110, Western Asia 111, Eastern Africa 112, Central Africa 113, Northern Africa 114, Southern Africa 115, Western Africa
** Create an SQL*Loader control file with the following information. Save the file as regions.ctl. OPTIONS (SKIP=1) LOAD DATA INFILE 'regions.txt' BADFILE 'regions.bad' DISCARDFILE 'regions.dsc' APPEND INTO TABLE HR.REGIONS FIELDS TERMINATED BY ',' TRAILING NULLCOLS (REGION_ID INTEGER EXTERNAL(3), REGION_NAME)
** Use SQL*Loader to load the data using the following command: sqlldr userid=system/<password> control=regions.ctl
** Verify the import using the following query: SELECT * FROM HR.REGIONS
** Clean up using the following query: DELETE FROM HR.REGIONS WHERE REGION_ID >= 101;

### External Tables

* Use an external table.
** Create a directory object pointing to an existing operating system directory using the following query: CREATE DIRECTORY external_files AS '<path>';
** Put the regions.txt text file from above in the specified directory.
** Create an external table referencing the text file using the following query: CREATE TABLE HR.REGIONS_FILE ( REGION_ID NUMBER(3), REGION_NAME VARCHAR2(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY external_files ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE SKIP 1 FIELDS TERMINATED BY ',' ) LOCATION ('regions.txt') )
** Select data from the external table using the following query: SELECT * FROM HR.REGIONS_FILE
** Use the following query to load the data into the REGIONS table: INSERT INTO HR.REGIONS(REGION_ID, REGION_NAME) (SELECT REGION_ID, REGION_NAME FROM HR.REGIONS_FILE);
** Verify the import using the following query: SELECT * FROM HR.REGIONS
** Clean up using the following queries: DELETE FROM HR.REGIONS WHERE REGION_ID >= 101; DROP TABLE HR.REGIONS_FILE; DROP DIRECTORY external_files;

### Spool

* Use spool to export a comma-separated-values copy of the REGIONS table.
** Review Charito: How to Write to a CSV File Using Oracle SQL*Plus.
** Use SQL*PLUS and run the following commands: set colsep , set pagesize 0 set trimspool on spool regions.csv SELECT * FROM HR.REGIONS; spool off
** 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

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

### Oracle Data Pump

* Identify the data pump directory using the following query: SELECT * FROM DBA_DIRECTORIES;
* Use Enterprise Manager Data Pump Export.
** Use Enterprise Manager Database Control / Data Movement / Export to Export Files to export the HR.REGIONS table.
** Navigate to the directory specified in the export and open the exported file using a text editor. Observe the file format and contents.
* Use command-line Data Pump Export.
** Use the following command to export the HR schema: expdp system/<password> schemas=hr dumpfile=hr.dmp
* Use command-line Data Pump Import.
** Use the following command to import HR.REGIONS into the OE schema: impdp system/<password> dumpfile=hr.dmp remap_schema=hr:oe tables=hr.regions
** Use the following query to verify the import: SELECT * FROM OE.REGIONS;
** use the following query to clean up the import: DROP TABLE OE.REGIONS;

## See Also

* Oracle: SQL*Loader Concepts
* Oracle: SQL*Loader Command-Line Reference
* Oracle: External Table Concepts
* Oracle: Data Pump
* Oracle: Using SQL Developer for Importing and Exporting
* Charito: How to Write to a CSV File Using Oracle SQL*Plus
* Stack Exchange: Oracle - Automate Export/Unload of Data