12 | January | 2017 | Catt Solutions

The Oracle EXPDP utility moves data outside of the database. The utility can be executed in a command line fashion, by a parameter file, or interactive-command mode.

Command-line – Enables you to specify most of the export parameters
Parameter File – Allows you to specify command line parameters in a PARFILE
Interactive-Command – Displays an export prompt and you enter various commands.

The EXPDP utility can create up to 4 external file types. This file types include the actual data captured by EXPDP utility, logs of current progress, SQL statements and actual complete data files depending on the parameters passed to the utility. The four possible output files of an EXPDP operation are:

Dump File – Contains data and metadata being moved.
Log File – Records log of operation.
SQL files – Records output of SQLFILE operation.
Data files – Used with transportable import and displays files using DATA_FILES

The EXPDP utility can be run in various modes to extract data at different levels of the database. This includes exporting the entire database, a single user contents, a single and/or multiple tables, and various types of tablespaces. The various types of EXPDP modes are:

Full – Entire database is unloaded. Need DATAPUMP_EXP_FULL_DATABASE role. FULL parameter
SCHEMA – Default export mode. If you have DATAPUMP_EXP_FULL_DATABASE role you give list of schemas if not only your own. SCHEMAS parameter.
TABLE – Only specific tables, partitions and dependent objects are unloaded. TABLES parameter.
TABLESPACES – All contents of a tablespaces are unloaded. If you have full privilege you get everything if not only your own objects. TABLESPACES parameter.
TRANSPORTABLE TABLESPACE – Only metadata of tables and associated objects are unloaded. Data is moved in another operation. TRANSPORT_TABLESPACES parameter.

Finally EXPDP utility can except the following parameters to define and control its operations. The acceptable parameters are:

ABORT_STEP – Stop the job after it is initialized or at the indicated object. Valid values are -1 or N where N is zero or greater. N corresponds to the object’s process order number in the master table.
ACCESS_METHOD – Instructs Export to use a particular method to unload data. Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.
ATTACH – Attach to an existing job. For example, ATTACH=job_name.
CLUSTER – Utilize cluster resources and distribute workers across the Oracle RAC [YES].
COMPRESSION – Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.
COMPRESSION_ALGORITHM – Specify the compression algorithm that should be used. Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.
CONTENT – Specifies data to unload. Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
DATA_OPTIONS – Data layer option flags. Valid keyword values are: XML_CLOBS.
DIRECTORY – Directory object to be used for dump and log files.
DUMPFILE – Specify list of destination dump file names [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION – Encrypt part or all of a dump file. Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION_ALGORITHM – Specify how encryption should be done. Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_MODE – Method of generating encryption key. Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_PASSWORD – Password key for creating encrypted data within a dump file.
ENCRYPTION_PWD_PROMPT – Specifies whether to prompt for the encryption password [NO]. Terminal echo will be suppressed while standard input is read.
ESTIMATE – Calculate job estimates. Valid keyword values are: [BLOCKS] and STATISTICS.
ESTIMATE_ONLY – Calculate job estimates without performing the export [NO].
EXCLUDE – Exclude specific object types. For example, EXCLUDE=SCHEMA:”=’HR'”.
FILESIZE – Specify the size of each dump file in units of bytes.
FLASHBACK_SCN – SCN used to reset session snapshot.
FLASHBACK_TIME – Time used to find the closest corresponding SCN value.
FULL – Export entire database [NO].
HELP – Display Help messages [NO].
INCLUDE – Include specific object types. For example, INCLUDE=TABLE_DATA.
JOB_NAME – Name of export job to create.
KEEP_MASTER – Retain the master table after an export job that completes successfully [NO].
LOGFILE – Specify log file name [export.log].
LOGTIME – Specifies that messages displayed during export operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.
METRICS – Report additional job information to the export log file [NO].
NETWORK_LINK – Name of remote database link to the source system.
NOLOGFILE – Do not write log file [NO].
PARALLEL – Change the number of active workers for current job.
PARFILE – Specify parameter file name.
QUERY – Predicate clause used to export a subset of a table. For example, QUERY=employees:”WHERE department_id > 10″.
REMAP_DATA – Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.
REUSE_DUMPFILES – Overwrite destination dump file if it exists [NO].
SAMPLE – Percentage of data to be exported.
SCHEMAS – List of schemas to export [login schema].
SERVICE_NAME – Name of an active Service and associated resource group to constrain Oracle RAC resources.
SOURCE_EDITION – Edition to be used for extracting metadata.
STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
TABLES – Identifies a list of tables to export. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.
TABLESPACES – Identifies a list of tablespaces to export.
TRANSPORTABLE – Specify whether transportable method can be used. Valid keyword values are: ALWAYS and [NEVER].
TRANSPORT_FULL_CHECK – Verify storage segments of all tables [NO].
TRANSPORT_TABLESPACES – List of tablespaces from which metadata will be unloaded.
VERSION – Version of objects to export. Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.
VIEWS_AS_TABLES – Identifies one or more views to be exported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

—————–

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

ADD_FILE – Add dumpfile to dumpfile set.
CONTINUE_CLIENT – Return to logging mode. Job will be restarted if idle.
EXIT_CLIENT – Quit client session and leave job running.
FILESIZE – Default filesize (bytes) for subsequent ADD_FILE commands.
HELP – Summarize interactive commands.
KILL_JOB – Detach and delete job.
PARALLEL – Change the number of active workers for current job.
REUSE_DUMPFILES – Overwrite destination dump file if it exists [NO].
START_JOB – Start or resume current job. Valid keyword values are: SKIP_CURRENT.
STATUS – Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
STOP_JOB – Orderly shutdown of job execution and exits the client. Valid keyword values are: IMMEDIATE.

The following is a simple example of the EXPDP utility in exporting a single table.

Logon to your server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Tue Jan 10 09:26:15 EST 2017 on pts/2

Enter database to use:

1 – ORCL

2 – CDB1

Option >

[oracle@linux2 ~]$

Logon to your database as the user scott default password tiger.

[oracle@linux2 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 08:56:36 2017

Last Successful login time: Fri Jan 06 2017 11:01:31 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL> create directory dmpdir as ‘/home/oracle’;

Directory created.

SQL>

Log out of SQLPLUS and change directory to oracle software owners home directory.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$ cd

[oracle@linux2 ~]$

Execute the command below to export the table scott.emp to file scott_emp.dmp.

expdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir tables=emp

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 ~]$ cd

[oracle@linux2 ~]$ expdp scott/tiger DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir tables=emp

Export: Release 12.1.0.2.0 – Production on Wed Jan 11 09:04:43 2017

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

Starting “SCOTT”.”SYS_EXPORT_TABLE_01″: scott/******** DUMPFILE=scott_emp.dmp DIRECTORY=dmpdir tables=emp

Estimate in progress using BLOCKS method…

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

. . exported “SCOTT”.”EMP” 8.820 KB 15 rows

Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/home/oracle/scott_emp.dmp

Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at Wed Jan 11 09:06:01 2017 elapsed 0 00:01:16

[oracle@linux2 ~]$

Now the table scott_emp.dmp contains all the data and metadata from scott.emp table.

[oracle@linux2 ~]$ ls -l scott*

-rw-r—–. 1 oracle dba 204800 Jan 11 09:06 scott_emp.dmp

[oracle@linux2 ~]$

This completes simple example of EXPDP utility.

Larry Catt

OCP

Catt Solutions

Daily Archives: January 12, 2017

Oracle 12c – Export Data Pump

Oracle tips and tricks.