Oracle Database Administration/Moving Data
This lesson introduces moving data.
Objectives and Skills
editObjectives and skills for the moving portion of Oracle Database Administration I certification include:[1]
- 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
editMultimedia
editActivities
editSQL*Loader
edit- 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;
- Create a text file with the following information. Save the file as
External Tables
edit- 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;
- Create a directory object pointing to an existing operating system directory using the following query:
Spool
edit- 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
edit- 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
edit- 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 the following command to export the HR schema:
- 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;
- Use the following command to import HR.REGIONS into the OE schema:
See Also
editReferences
edit