Moving Data
Scope
This domain tests data movement: Data Pump, SQL*Loader, external tables, and transportable tablespaces. Expect questions on where dump files are written and how to remap objects.
Core rules
- Data Pump uses expdp and impdp, directory objects, and server-side dump files. Jobs run on the database server; the client only issues commands. Parameters include REMAP_SCHEMA, REMAP_TABLESPACE, NETWORK_LINK, and CONTENT.
- DBA_DATAPUMP_JOBS and DBA_DATAPUMP_SESSIONS monitor jobs. Data Pump replaces the legacy exp/imp utilities.
- SQL*Loader loads external files with a control file; direct path bypasses the buffer cache and can be faster but has restrictions. External tables use ORACLE_LOADER or ORACLE_DATAPUMP access drivers.
- Transportable tablespaces move datafiles with metadata; source and target must have compatible platforms or use cross-platform conversion.
Worked example
CREATE DIRECTORY dpdir AS '/u01/dp'; GRANT READ, WRITE ON DIRECTORY dpdir TO hr; expdp hr/hr DIRECTORY=dpdir DUMPFILE=hr.dmp LOGFILE=hr.log SCHEMAS=HR; impdp system/... DIRECTORY=dpdir DUMPFILE=hr.dmp REMAP_SCHEMA=HR:HR2; The dump file is created on the server, not the client.
Traps
A directory object is a database object, not an OS path grant alone. Data Pump dump files are server-side. SQL*Loader direct path can disable indexes. Transportable tablespaces require endianness compatibility. Data Pump is not the old exp utility.