Prerequisites

• To get the most from this tutorial, you need to be an Oracle “techie”, having experience with:
  • Oracle export and import utilities
  • Oracle9i external tables

What’s New?

• Data Pump!
  • Data Pump Export
  • Data Pump Import
  • DBMS_DATAPUMP and DBMS_METADATA
• New external tables access driver
  • ORACLE_DATAPUMP

Data Pump

Data Pump Concepts

• Next generation export / import
• New utility for high-speed data movement
  • To / from OS “dump” files
    • Like original exp / imp, only better
  • Between databases
    • “Network export / import” operations

Major Differences

• Server-side background processes perform all operations
  • All I/O done on server!
• expdp / impdb are thin clients
  • Calls to datapump API (DBMS_DATAPUMP)
• New parameters
  • Replace original parameters
    • SQLFILE replaces SHOW
  • Add functionality
    • FLASHBACK_TIME
• Many new features
  • Parallelism, restartability, API, etc.

Data Pump Modes

• Full
  • Entire database
  • Requires EXP_FULL_DATABASE role
• Schema
  • Similar to OWNER=
  • Default
• Table
  • Tables, partitions, and their dependent objects
• Tablespace
  • Tables contained in tablespace, and dependents
  • Requires EXP_FULL_DATABASE role
• Transportable Tablespace
  • Metadata for tables and dependents in specified tablespaces

Data Pump Access Methods

• Direct path
  • Supported for many export and import operations
  • Multiple, parallel direct path streams
• External table
  • Used, for example, if filter with QUERY parameter
  • 10g supports write to external tables
  • Parallel query and parallel DML support
• Datapump automatically chooses method

Parallel Processing

• Enterprise Edition feature
• Provides better performance then exp / imp
• See PARALLEL parameter

Export Example…

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

…Export Example

Import Example

Data Pump Features…

• Client can disconnect, reconnect at will
• To monitor
  • See STATUS and CONTINUE commands
• To control
  • STOP_JOB, KILL_JOB, change parallel degree (PARALLEL)
• To restart jobs!
  • See START_JOB
  • Even after instance crash!
• To add dump files
  • See ADD_FILE

…Data Pump Features…

• Filtering during export and import
  • Data, metadata or both
    • See CONTENT parameter
  • Filter by object type, name
    • Wildcard support
    • See INCLUDE and EXCLUDE
  • Filter data
    • Supports WHERE, ORDER BY, more
      • Even on import
    • See QUERY

…Data Pump Features…

• Import remapping capabilities
  • Change name of datafiles
    • See REMAP_DATAFILE
  • Change owner
    • See REMAP_SCHEMA
  • Change tablespace
    • See REMAP_TABLESPACE
• Reuse existing datafiles
  • See REUSE_DATAFILES

…Data Pump Features…

• Flashback support
  • Export at previous point in time
  • Get a consistent export
  • See FLASHBACK_SCN and FLASHBACK_TIME parameters

…Data Pump Features

• Estimate space before exporting
  • See ESTIMATE
    • BLOCKS or STATISTICS
  • ESTIMATE_ONLY
    • Use “Y” to skip the actual export
• Table compression support

Network Export / Import

• “Network export”
  • Server “A” receives network export request
  • Retrieves data from server “B”
  • Writes data to dump file on server “A”
• “Network import”
  • Server “A” receives network import request
  • Retrieves data from server “B” (exports)
  • Writes data to Server “A”
  • No intermediate “dump” files involved!
  • Can request source data at flashback time or SCN

Network Import Example

DBMS_DATAPUMP

Data Pump Dictionary

• Monitor job progress
  • V$SESSION_LONGOPS
  • DBA_DATAPUMP_JOBS
  • DBA_DATAPUMP_SESSIONS
• Valid objects for INCLUDE parameter
  • DATABASE_EXPORT_OBJECTS
  • SCHEMA_EXPORT_OBJECTS
  • TABLE_EXPORT_OBJECTS.

Datapump Resources

• 10g Concepts, Chapter 11
• 10g Utilities manual

Data Pump Summary

• Datapump should prove to be a better export / import utility
• Several features that should impact us:
  • Parallel support
  • Server-based, background processing
  • Restartability
  • Network import

External Tables

External Tables…

• New access driver ORACLE_DATAPUMP
  • Used by datapump import and export
• Can read and write to external files
  • One-time load only
  • No subsequent updates allowed
• Use as another way to invoke datapump export / import
  • Without OS commands

…External Tables…

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

…External Tables

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

External Table Resources

• 10g Utilities manual
• Administrator’s Guide
  • Chapter 14
• Metalink notes
  • Note 269354.1

External Table Summary

• External Tables / ORACLE_DATAPUMP let’s us unload data
  • 9i ORACLE_LOADER let us load data
• Consider it another face on datapump export / import operations

Lesson Summary

• You should have a good understanding of:
  • Data Pump
  • ORACLE_DATAPUMP, the new access driver for external tables
• Please send your comments!!!
  • knowledge@skillbuilders.com