Chapter 18 Moving Data

Describe and Use Methods to Move Data (Directory Objects, SQL*Loader, External Tables)
SQL*Loader is user process with a control file to read the input data file and generate log file, bad file, reject file. There are two techniques: conventional (SGA, database buffer cache, generate redo and undo data) or direct path (bypasses database buffer cache, use PGA, no undo data generated, redo can be switched off).

Example control file script (depts.ctl (control file)):
load data
direct=true (direct path method is used)
infile 'depts.txt' (input data file)
badfile 'depts.bad' (bad file)
discardfile 'depts.dsc' (reject file)
append
into table dept....

depts.log (log file) will be generated.

Directory Object: More finely-grained access control
CREATE DIRECTORY directory_name AS 'path_to_os_directory';
Then grant the directory to user.
GRANT READ/WRITE ON DIRECTORY directory_name TO user_name;

External Table using Oracle Directory for input file:
Types:
oracle_loader: source:dept.txt
oracle_datadump: source:dept.dp, as select * from table_name
CREATE TABLE table_name (column datatype,...)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY directory_name...


Explain the General Architecture of Oracle Data Pump
expdp and impdp: launch, control, monitor
initiate job from user process. all the work done by server process generating DDL SQL files, Data Dump files (XML format), and log file.
by default: direct access datafiles using direct path
complex: use external table path
no control: direct path or external table path

DMnn: Data Pump Master
DWnn: Data Pump Worker
Pnnn: Parallel Execution Server

Control Queue: DMnn put, DWnn pick
Status Queue

Control Table

DATA_DUMP_DIR default

Use Data Pump Export and Import to Move Data Between Oracle Databases
Network mode through database link
Remapping
Compressed and Encrypted