Moving Data

Copyright © 2007, Oracle. All rights reserved.

 Objectives

After completing this lesson, you should be able to:

• Describe ways to move data
• Create and use directory objects
• Use SQL*Loader to load data from a non-Oracle
database (or user files)
• Use external tables to move data via platform-
independent files
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data
between Oracle databases

17 - 2 Copyright © 2007, Oracle. All rights reserved.

 Moving Data:
General Architecture

SQL*Loader expdp impdp Other clients

Data Pump

DBMS_DATAPUMP
Data/Metadata Movement Engine

Oracle Oracle
Loader DataPump Direct Path API Metadata API

External Table API

17 - 3 Copyright © 2007, Oracle. All rights reserved.

 Directory Objects: Overview .

17 - 4 Copyright © 2007, Oracle. All rights reserved.

 Creating Directory Objects

17 - 5 Copyright © 2007, Oracle. All rights reserved.

 SQL*Loader: Overview

Input data files Control file

SQL*Loader Rejected
Field processing
Discarded Accepted
Record selection
Selected
Bad
Oracle server file
Discard file Rejected
Inserted
(optional)

Log file

17 - 6 Copyright © 2007, Oracle. All rights reserved.

 Loading Data with SQL*Loader

17 - 7 Copyright © 2007, Oracle. All rights reserved.

 SQL*Loader Control File

The SQL*Loader control file instructs SQL*Loader about:

• Location of the data to be loaded
• Data format
• Configuration details:
– Memory management
– Record rejection
– Interrupted load handling details
• Data manipulation details

17 - 8 Copyright © 2007, Oracle. All rights reserved.

 Loading Methods

Data Block
insert writes

Table
HWM

Conventional Load Direct Path Load

Uses COMMIT Uses data saves (faster operation)

Always generates redo entries Generates redo only under specific

conditions

Enforces all constraints Enforces only PRIMARY KEY, UNIQUE, and

NOT NULL

Fires INSERT triggers Does not fire INSERT triggers

Can load into clustered tables Does not load into clusters

Allows other users to modify Prevents other users from making changes
tables during load operation to tables during load operation

17 - 9 Copyright © 2007, Oracle. All rights reserved.

 External Table Population

• Unloading data to external files with the

ORACLE_DATAPUMP access driver
• No modifications of external tables

CREATE TABLE … AS SELECT INSERT … SELECT

Unloading Loading

External files
Tables (proprietary format) Tables

17 - 10 Copyright © 2007, Oracle. All rights reserved.

 Using External Tables

• Data can be used directly from the external file or

loaded into another database.
• Resulting files can be read only with the
ORACLE_DATAPUMP access driver.
• You can combine generated files from different sources
for loading purposes.

From Oracle Database From external file

17 - 11 Copyright © 2007, Oracle. All rights reserved.

 External Table Population with ORACLE_DATAPUMP

CREATE TABLE emp_ext

(first_name, last_name, department_name)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_dir
LOCATION ('emp1.exp','emp2.exp','emp3.exp')
)
PARALLEL
AS
SELECT e.first_name,e.last_name,d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND
d.department_name in
('Marketing', 'Purchasing');

17 - 12 Copyright © 2007, Oracle. All rights reserved.

 External Table Population with ORACLE_LOADER

CREATE TABLE extab_employees

(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER DEFAULT DIRECTORY extab_dat_dir
ACCESS PARAMETERS
( records delimited by newline
badfile extab_bad_dir:'empxt%a_%p.bad'
logfile extab_log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name,
hire_date char date_format date mask "dd-mon-yyyy“))
LOCATION ('empxt1.dat', 'empxt2.dat') )
PARALLEL REJECT LIMIT UNLIMITED;

17 - 13 Copyright © 2007, Oracle. All rights reserved.

 Oracle Data Pump: Overview

As a server-based facility for high-speed

data and metadata movement, Oracle Data Pump:
• Is callable via DBMS_DATAPUMP
• Provides the following tools:
– expdp
– impdp
– Web-based interface
• Provides data access methods:
– Direct path
– External tables
• Detaches from and reattaches to long-running jobs
• Restarts Data Pump jobs

17 - 14 Copyright © 2007, Oracle. All rights reserved.

 Oracle Data Pump: Benefits

• Fine-grained object and data selection

• Explicit specification of database version
• Parallel execution
• Estimation of export job space consumption
• Network mode in a distributed environment
• Remapping capabilities during import
• Data sampling and metadata compression
• Compression of data during an export
• Security through encryption
• Remapping of data
• Ability to export XMLType data as CLOBs

17 - 15 Copyright © 2007, Oracle. All rights reserved.

 Data Pump Export and Import: Overview
expdp Database
client link

Source Target
Data Pump Server
job process
Database Database
Master Dump Dump Master
table file set file set table

“Network mode”

Server Data Pump

process job

impdp
client

17 - 16 Copyright © 2007, Oracle. All rights reserved.

 Data Pump Utility: Interfaces and Modes

• Data Pump Export and Import interfaces:

– Command line
– Parameter file
– Interactive command line
– Enterprise Manager
• Data Pump Export and Import modes:
– Full
– Schema
– Table
– Tablespace
– Transportable tablespace

17 - 17 Copyright © 2007, Oracle. All rights reserved.

 Fine-Grained Object Selection .

17 - 18 Copyright © 2007, Oracle. All rights reserved.

 Advanced Feature: Sampling

• Task: Create test data.

• Method: Specify a percentage of data to be sampled
and unloaded from the source database.
Example: To unload 44% of the HR.EMPLOYEES table
SAMPLE="HR"."EMPLOYEES":44

Example: To unload 30% of the entire export job (because

no table name is specified)
expdp hr/hr DIRECTORY=DATA_PUMP_DIR
DUMPFILE=sample1.dmp SAMPLE=30

17 - 19 Copyright © 2007, Oracle. All rights reserved.

 Export Options: Files

17 - 20 Copyright © 2007, Oracle. All rights reserved.

 Data Pump File Locations

Order of precedence of file locations:

• Per-file directory
• DIRECTORY parameter
• DATA_PUMP_DIR environment variable
• DATA_PUMP_DIR directory object

17 - 21 Copyright © 2007, Oracle. All rights reserved.

 Scheduling and Running a Job

17 - 22 Copyright © 2007, Oracle. All rights reserved.

 Data Pump File Naming and Size

17 - 23 Copyright © 2007, Oracle. All rights reserved.

 Data Pump Import

17 - 24 Copyright © 2007, Oracle. All rights reserved.

 Data Pump Import: Transformations

You can remap:

• Data files by using REMAP_DATAFILE
• Tablespaces by using REMAP_TABLESPACE
• Schemas by using REMAP_SCHEMA
REMAP_DATAFILE = 'C:\oradata\tbs6.f':'/u1/tbs6.f'

17 - 25 Copyright © 2007, Oracle. All rights reserved.

 Data Pump Import: Transformations

Using TRANSFORM, you can also :

• Exclude from tables and indexes
– STORAGE and TABLESPACE clauses
– STORAGE clause only
• Re-create object identifiers of abstract data types
• Change extent allocations and file size
TRANSFORM =
SEGMENT_ATTRIBUTES|STORAGE|OID|PCTSPACE:{y|n|v}[:object type]

17 - 26 Copyright © 2007, Oracle. All rights reserved.

 Data Pump: Performance Considerations

Maximizing job performance with the PARALLEL parameter

Master coordinator

Parallel
execution

Generated
files

Example:
expdp hr/hr FULL=y
DUMPFILE=dp_dir1:full1%U.dmp, dp_dir2:full2%U.dmp
FILESIZE=2G PARALLEL=3
LOGFILE=dp_dir1:expfull.log JOB_NAME=expfull

17 - 27 Copyright © 2007, Oracle. All rights reserved.

 Performance Initialization Parameters

• Data Pump performance can be affected by:

– DISK_ASYNCH_IO
– DB_BLOCK_CHECKING
– DB_BLOCK_CHECKSUM
• Set the following high to enable maximum parallelism:
– PROCESSES
– SESSIONS
– PARALLEL_MAX_SERVERS
• Size generously:
– Shared pool
– Undo tablespace

17 - 28 Copyright © 2007, Oracle. All rights reserved.

 Data Pump Access Path: Considerations

Data Pump automatically selects one of

the following access paths: Database
• Direct path
• External tables if data includes:
– Encrypted columns External Direct
tables path
– Clustered tables
– Different partition at unload and
load time
– Others Database

17 - 29 Copyright © 2007, Oracle. All rights reserved.

 Using Enterprise Manager to Monitor
Data Pump Jobs

17 - 30 Copyright © 2007, Oracle. All rights reserved.

 Data Dictionary

View information about external tables in:

• [DBA| ALL| USER]_EXTERNAL_TABLES
• [DBA| ALL| USER]_EXTERNAL_LOCATIONS
• [DBA| ALL| USER]_TABLES
• [DBA| ALL| USER]_TAB_COLUMNS

17 - 31 Copyright © 2007, Oracle. All rights reserved.

 Summary

In this lesson, you should have learned how to:

• Describe ways to move data
• Create and use directory objects
• Use SQL*Loader to load data from a non-Oracle
database (or user files)
• Use external tables to move data via
platform-independent files
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data
between Oracle databases

17 - 32 Copyright © 2007, Oracle. All rights reserved.

 Practice 17 Overview:
Moving Data

This practice covers the following topics:

• Using the Data Pump Export Wizard to select database
objects to be exported
• Monitoring a Data Pump Export job
• Using the Data Pump Import Wizard to import tables to
your database
• Using the Load Data Wizard to load data into your
database
• Loading data by using the command line

17 - 33 Copyright © 2007, Oracle. All rights reserved.