• Define Data Pump and how to use it for import and export of data.
• Describe how to work with SQL*Loader and create directory objects.
Focused Explanation
Exporting Data using Data Pump Export
The Data Pump is a mechanism for transferring data or metadata from one database to another
database. The Data Pump uses direct path loading and unloading technologies. These technologies use direct-path APIs for providing the data to be loaded and unloaded on the Oracle server. The Data Pump runs on the server-side, unlike the old version of import and export programs, which operate on the client-side of a database. You can use the Data Pump export to call a DBMS_DATAPUMP package by running the expdp program or by running a PL/SQL program. You can also run Data Pump exports using a graphical user interface provided by Enterprise Manager Database Control.
Performing Data Pump Export Jobs Using the expdp Program
To perform the Data Pump export jobs using the expdp program, you need to create directory objects to store files, such as dump, log, and SQL files.
Creating Directory Objects
Directory objects represent the location of a directory on the database server. These objects can be used with various database features, such as bigfile tablespaces, external tables, utl_file, and Data Pump.
The following statements show how to create data directories using the CREATE DIRECTORY statement in UNIX:
CREATE DIRECTORY dump_file AS ‘/oracle/data_pump/dumps’;
CREATE DIRECTORY log_dir AS ‘/oracle/data_pump/logs’;
When creating a data directory in Windows, you use the following statement:
CREATE DIRECTORY dpump_dir AS ‘c:\datapumps’;
Using Data Pump Export Modes
The following are various modes in which you can perform data pump export jobs using the expdp program:
• Full Export mode – Used to export a complete database.
• Schema Export mode – Used to export the database objects that belong to a specified schema.
• Table Export mode – Used to export only specified tables, partitions of tables, and database objects that are dependent on these tables.
• Tablespace Export mode – Used to export the tables contained in a specified set of tablespaces.
• Transportable Tablespace Export mode – Used to export only the metadata for tables contained in a specified set of tablespaces and database objects that are dependent on these tables.
Using the Full Export Mode
You must be granted the EXP_FULL_DATABASE role to perform a full database export. For example, the following command is used for performing a full database export to files in the chap3a and chap3b directories:
expdp system / secret full=Y dumpfile=chap3a:fulla%U.dmp, chap3b:fullb%U.dmp filesize=2G parallel=2 logfile=chap3:full.log
These parameters instruct the Data Pump program to connect to the database using the SYSTEM user name and SECRET password. The full=Y parameter enables the Data Pump to perform a database mode export. The Data Pump saves a series of dump files alternating between the chap3a and chap3b directories. The %U parameter facilitates the Data Pump to insert a sequential number into each filename.
Each file contains 2 GB of data. The log file full.log is saved in the chap3 directory.
Using the Schema Export Mode
A schema mode export is performed by omitting the full=Y parameter and including a
schemas=schema_list parameter in the expdp program parameter list. The schema list is a comma-delimited list of schemas. You can export your own schema by omitting both the full and the schemas parameters. To export another user’s schema, you must have been granted the EXP_FULL_DATABASE role. The command to export the hr schema is:
expdp hr/hr dumpfile=chap3:hr.dmp logfile=chap3:hr.out
The dumpfile and logfile parameters work in the same way in all export modes. A colon and the file name follow the database directory object. Depending on operating system, the names of the files used can be sensitive. For example, on the Windows operating system, the names of files are not case-sensitive. However on the UNIX platform, the names are case-case-sensitive.
Using the Table Export Mode
A table mode export is performed by including a tables=table_list parameter and omitting the full and schemas parameters. The table_list is a comma-delimited list of tables to be exported. These tables can be specified with the owner using dot notation, such as HR.EMPLOYEES. For example, the command to export the metadata for hr owned jobs and job_history tables is:
expdp hr/hr dumpfile=char3:job_tabs.dmp nologfile=y content=metadata_only tables=jobs,job_history
The nologfile=y parameter enables the Data Pump not to write the log file to disk. The
content=metadata_only parameter enables the Data Pump to export only the metadata and not the data in the table.
Using the Tablespace Export Mode
To perform a tablespace mode export, the database user must have been granted the
EXP_FULL_DATABASE role. You can use the tablespaces=tablespace_list parameter to specify a comma-delimited list of the tablespaces to be exported. For example, the command to export all the objects from the tablespaces USERS and USERS1 is:
expdp system /password dumpfile=chap3:users_ts.dmp logfile=chap3:users_ts.out tablespaces=USERS,USERS1
The help=y parameter enables you to see the full list of parameters used with the expdp program. You can also place all the parameters in a file and then execute the expdp program to read these parameters using the parfile=directory:filename parameter. The directory portion of the parameter refers to a database directory object and the filename portion of the parameter refers to the name of the file that contains the parameters to use for the export.
Using Transportable Tablespace Export Mode
You must have been granted the EXP_FULL_DATABASE role to perform a transportable tablespace mode export. This mode allows you to export metadata information from one database to another. For example, the command to export metadata information using the transportable tablespace mode is:
expdp system / password directory=dpump_dir1 dumpfile=ttbs.dmp transport_tablespaces=ttbs transport_full_check=y logfile=ttbs.log The transport_full_check=y parameter verifies storage segments of all tables.
Data Pump Export Parameters
Data Pump export parameters are the parameters required to perform data export jobs. Table 4-1 describes the Data Pump export parameters.
Parameter Description
full=y Specifies a database export mode.
schemas=schema_list Specifies a schema mode export where schema_list is a list of schemas to be exported.
tables=table_list Specifies a table mode export where table_list is a list of tables to be exported.
tablespaces=tablespace_list Specifies a tablespace mode export where tablespace_list is a list of tablespaces to be exported.
content=content_option Specifies whether data, metadata, or both are exported.
network_link=db_link Specifies that a remote database used is accessed through the database link db_link and is used as the export source.
Parameter Description dumpfile=dir:file Specifies the dump file location and name.
filesize=size_limit Specifies the maximum size of the dump file.
logfile=dir:file Specifies the log file location and name.
directory=dir Specifies the file location to be used for both the dump file and the log file.
nologfile=y Specifies that no log file should be written.
job_name=identifier Specifies a name for the import job.
parallel=degree Specifies the maximum number of active threads or processes operating for the import.
parfile=dir:file Specifies the file location and name of the parameter file that the Data Pump export uses.
Table 4-1: The Data Pump Export Parameters Performing Data Pump Export Jobs Using the DBMS_DATAPUMP Package
You can use the DBMS_DATAPUMP PL/SQL package to export data using the Data Pump. The
DBMS_DATAPUMP PL/SQL package provides greater function and control and also schedules the Data Pump export jobs for a database scheduler. The series of actions for a PL/SQL-initiated Data Pump session are as follows:
• Obtain a handle to a Data Pump session.
• Define the dump and log file.
• Define a filter condition.
• Launch the Data Pump session.
• Disconnect from the session.
Importing Data Using Data Pump Import
Export extracts data and metadata from the database, and import loads this extracted data into the same or a different database using the Data Pump. Loading the database enables you to copy tables, from one schema to another or map a tablespace from one database to another. You can use the Data Pump import to call a DBMS_DATAPUMP package by running the impdp program or by running a PL/SQL program.
Performing Data Pump Import Jobs Using the impdp Program
The Data Pump import program, impdp, operates in various modes, namely full, schema, table, and tablespace. In the full mode, the entire content of the export file is loaded. In the schema mode, all content from a list of schemas in the specified file, are loaded. In the table mode, only the specified tables and dependent objects are loaded. In the tablespace mode, all objects in the export file set are loaded.
The Data Pump import mechanism uses the same parameters used by the Data Pump export.
Table 4-2 describes the mapping of export modes to import modes.
Source Export Modes Import Mode
Database
Table 4-2: Mapping of Data Pump Export Modes to Import Modes To perform Data Pump import jobs using the impdp program, complete the following steps:
1. Connect as the SYSDBA user to the destination database.
2. Create a directory with the same name as created during exporting of the data using the expdp program.
3. Grant READ and WRITE permissions to the user, John, who wants to import the database.
4. Connect to destination database as the user, John.
5. Copy the expdp.dmp file to the destination database at the same location.
6. Type the following statement at the command prompt to invoke the Data Pump impdp program:
C:/> impdp directory=directory_name
When you type the above statement at the command prompt, you receive a prompt to type the user name and the password.
7. Type the user name and password at the command prompt. Data Pump starts importing data.
Monitoring Data Pump Jobs
You can monitor the progress of Data Pump jobs by either using the STATUS command in the interactive-command mode interface or querying the DATA_DATAPUMP_JOBS, USER_DATAPUMP_JOBS,
DBA_DATAPUMP_SESSIONS, and V$SESSION_LONGOPS views.
You can use the DBA_DATAPUMP_JOBS and USER_DATAPUMP_JOBS views to display information about all the active Data Pump jobs, regardless of their status. These views also display information about all the master tables that are not associated with the active Data Pump job. After gathering the information about a master table, you can use that master table to restart a Data Pump job.
Working with SQL*Loader
SQL*Loader is a program that reads data files in different formats, parses the data, and loads the data into database tables. The various file types used by SQL*Loader are:
• log: Is a mandatory file that records the activities performed by SQL*Loader. If not specified, SQL*Loader creates a new log file in the current directory with the name of the control file and with a .log filename extension. Data loading is aborted if SQL*Loader is not able to create a log file.
• control: Is a mandatory file that contains information about how the load should be performed.
SQL*Loader reads this file to locate other data files, determine how to parse and load the data files, and determine the table into which to load the parsed data.
• data: Is a file that contains the data that SQL*Loader reads and loads into the database.
• bad: Is a file created by SQL*Loader that contains records that either cause errors during insert operations or are improperly formatted, and cannot be loaded. These records are not validated either by SQL*Loader or by the database.
• discard: Is a file created by SQL*Loader that contains the data records that are not loaded because the records are not specified in the selection criteria.
You can invoke the SQL*Loader program by using the sqlldr command followed by one or more line parameters. You can identify these parameters either by their position on the command-line or with a keyword=value pair.
Table 4-3 lists the SQL*Loader command-line parameters.
Parameter Description
userid Specifies the database connect string.
control Specifies the name of the control file.
log Specifies the name of the log file.
bad Specifies the name of bad file.
data Specifies the name of data file.
discard Specifies the name of the discard file.
Parameter Description discardmax Specifies the maximum number of discards allowed before
data loading is terminated.
skip Specifies the number of records to skip before loading the records.
load Specifies the number of records to load.
error Specifies the number of errors allowed before data loading is terminated.
rows Specifies the number of rows in a conventional path bind array or between direct path data.
bindsize Specifies the size of conventional path bind array in bytes.
direct Specifies the load method to be used.
parfile Specifies the path and filename of a separate file containing additional command-line parameters.
Table 4-3: SQL*Loader Command-Line Parameters