Download Oracle Data Pump

Author: m | 2025-04-24

In this tutorial, you use the Oracle Data Pump Export utility to unload data then use the Oracle Data Pump Import utility to load data. What is Oracle Data Pump? Oracle Data Pump is a new

Data Transformations with Oracle Data Pump

Set. COMPRESSION_ALGORITHMThe Oracle Data Pump Export command-line utility COMPRESSION_ALGORITHM parameter specifies the compression algorithm that you want to use when compressing dump file data. CONTENTThe Oracle Data Pump Export command-line utility CONTENT parameter enables you to filter what Export unloads: data only, metadata only, or both. DATA_OPTIONSThe Oracle Data Pump Export command-line utility DATA_OPTIONS parameter designates how you want certain types of data handled during export operations. DIRECTORYThe Oracle Data Pump Export command-line utility DIRECTORY parameter specifies the default location to which Export can write the dump file set and the log file. DUMPFILEThe Oracle Data Pump Export command-line utility DUMPFILE parameter specifies the names, and optionally, the directory objects of dump files for an export job. ENABLE_SECURE_ROLESThe Oracle Data Pump Export command-line utility ENABLE_SECURE_ROLES parameter prevents inadvertent use of protected roles during exports. ENCRYPTIONThe Oracle Data Pump Export command-line utility ENCRYPTION parameter specifies whether to encrypt data before writing it to the dump file set. ENCRYPTION_ALGORITHMThe Oracle Data Pump Export command-line utility ENCRYPTION_ALGORITHM parameter specifies which cryptographic algorithm should be used to perform the encryption. ENCRYPTION_MODEThe Oracle Data Pump Export command-line utility ENCRYPTION_MODE parameter specifies the type of security to use when encryption and decryption are performed. ENCRYPTION_PASSWORDThe Oracle Data Pump Export command-line utility ENCRYPTION_PASSWORD parameter specifies a password for encrypting encrypted column data, metadata, or table data in the export dump file. This parameter prevents unauthorized access to an encrypted dump file set. ENCRYPTION_PWD_PROMPTThe Oracle Data Pump Export command-line utility ENCRYPTION_PWD_PROMPT specifies whether Oracle Data Pump prompts you for the encryption password. ESTIMATEThe Oracle Data Pump Export command-line utility ESTIMATE parameter specifies the method that Export uses to estimate how much disk space each table in the export job will consume (in bytes). ESTIMATE_ONLYThe Oracle Data Pump Export command-line utility ESTIMATE_ONLY parameter instructs Export to estimate the space that a job consumes, without actually performing the export operation. EXCLUDEThe Data Pump Export command-line utility EXCLUDE parameter enables you to filter the metadata that is exported by specifying objects and object types that you want to exclude from the export operation. FILESIZEThe Oracle Data Pump Export command-line utility FILESIZE Based on table size PARFILEThe Oracle Data Pump Export command-line utility PARFILE parameter specifies the name of an export parameter file. QUERYThe Oracle Data Pump Export command-line utility QUERY parameter enables you to specify a query clause that is used to filter the data that gets exported. REMAP_DATAThe Oracle Data Pump Export command-line utility REMAP_DATA parameter enables you to specify a remap function that takes as a source the original value of the designated column and returns a remapped value that replaces the original value in the dump file. REUSE_DUMPFILESThe Oracle Data Pump Export command-line utility REUSE_DUMPFILES parameter specifies whether to overwrite a preexisting dump file. SAMPLEThe Oracle Data Pump Export command-line utility SAMPLE parameter specifies a percentage of the data rows that you want to be sampled and unloaded from the source database. SCHEMASThe Oracle Data Pump Export command-line utility SCHEMAS parameter specifies that you want to perform a schema-mode export. SERVICE_NAMEThe Oracle Data Pump Export command-line utility SERVICE_NAME parameter specifies a service name that you want to use in conjunction with the CLUSTER parameter. SOURCE_EDITIONThe Oracle Data Pump Export command-line utility SOURCE_EDITION parameter specifies the database edition from which objects are exported. STATUSThe Oracle Data Pump Export command-line utility STATUS parameter specifies the frequency at which the job status display is updated. TABLESThe Oracle Data Pump Export command-line utility TABLES parameter specifies that you want to perform a table-mode export. TABLESPACESThe Oracle Data Pump Export command-line utility TABLESPACES parameter specifies a list of tablespace names that you want to be exported in tablespace mode. TRANSPORT_FULL_CHECKThe Oracle Data Pump Export command-line utility TRANSPORT_FULL_CHECK parameter specifies whether to check for dependencies between objects TRANSPORT_TABLESPACESThe Oracle Data Pump Export command-line utility TRANSPORT_TABLESPACES parameter specifies that you want to perform an export in transportable-tablespace mode. TRANSPORTABLEThe Oracle Data Pump Export command-line utility TRANSPORTABLE parameter specifies whether the transportable option should be used during a table mode or full mode export. TTS_CLOSURE_CHECKThe Oracle Data Pump Export command-line mode TTS_CLOSURE_CHECK parameter is used to indicate the degree of closure checking to be performed as part of a Data Pump transportable tablespace operation. VERSIONThe Data

Data Pump for Oracle 3.7

Parameter specifies the maximum size of each dump file. FLASHBACK_SCNThe Oracle Data Pump Export command-line utility FLASHBACK_SCN parameter specifies the system change number (SCN) that Export uses to enable the Flashback Query utility. FLASHBACK_TIMEThe Oracle Data Pump Export command-line utility FLASHBACK_TIME parameter finds the SCN that most closely matches the specified time. FULLThe Export command-line FULL parameter specifies that you want to perform a full database mode export HELPThe Data Pump Export command-line utility HELP parameter displays online help for the Export utility. INCLUDEThe Data Pump Export command-line utility INCLUDE parameter enables you to filter the metadata that is exported by specifying objects and object types for the current export mode. The specified objects and all their dependent objects are exported. Grants on these objects are also exported. JOB_NAMEThe Oracle Data Pump Export command-line utility JOB_NAME parameter identifies the export job in subsequent actions. KEEP_MASTERThe Oracle Data Pump Export command-line utility KEEP_MASTER parameter indicates whether the Data Pump control job table should be deleted or retained at the end of an Oracle Data Pump job that completes successfully. LOGFILEThe Data Pump Export command-line utility LOGFILE parameter specifies the name, and optionally, a directory, for the log file of the export job. LOGTIMEThe Oracle Data Pump Export command-line utility LOGTIME parameter specifies that messages displayed during export operations are timestamped. METRICSThe Oracle Data Pump Export command-line utility METRICS parameter indicates whether you want additional information about the job reported to the Data Pump log file. NETWORK_LINKThe Data Pump Export command-line utility NETWORK_LINK parameter enables an export from a (source) database identified by a valid database link. The data from the source database instance is written to a dump file set on the connected database instance. NOLOGFILEThe Data Pump Export command-line utility NOLOGFILE parameter specifies whether to suppress creation of a log file. PARALLELThe Oracle Data Pump Export command-line utility PARALLEL parameter specifies the maximum number of processes of active execution operating on behalf of the export job. PARALLEL_THRESHOLDThe Oracle Data Pump Export command-line utility PARALLEL_THRESHOLD parameter specifies the size of the divisor that Data Pump uses to calculate potential parallel DML. In this tutorial, you use the Oracle Data Pump Export utility to unload data then use the Oracle Data Pump Import utility to load data. What is Oracle Data Pump? Oracle Data Pump is a new Oracle recommends using the latest Oracle Data Pump version for importing data from Data Pump files into your database. Download the latest version of Oracle Instant Client, which includes Oracle Data Pump, for your platform from Oracle Instant Client Downloads .

Overview of Oracle Data Pump

The Oracle Data Pump Export utility is used to unload data and metadata into a set of operating system files, which are called a dump file set.2.1 What Is Oracle Data Pump Export? Oracle Data Pump Export is a utility for unloading data and metadata into a set of operating system files that are called a dump file set. You can import a dump file set only by using the Oracle Data Pump Import utility. You can import the dump file set on the same system, or import it to another system, and load the dump file set there. The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Oracle Data Pump Import utility uses these files to locate each database object in the dump file set. Because the dump files are written by the server, rather than by the client, you must create directory objects that define the server locations to which files are written. Oracle Data Pump Export enables you to specify that you want a job to move a subset of the data and metadata, as determined by the export mode. This subset selection is done by using data filters and metadata filters, which are specified through Oracle Data Pump Export parameters. Note: Several system schemas cannot be exported, because they are not user schemas; they contain Oracle-managed data and metadata. Examples of schemas that are not exported include SYS, ORDSYS, and MDSYS. Secondary objects are also not exported, because the CREATE INDEX run at import time will recreate them. Related Topics Understanding Dump_ Log_ and SQL File Default Locations Filtering During Export Operations Export Utility (exp or expdp) does not Export DR${name}$% or DR#{name}$% Secondary Tables of Text Indexes (Doc ID 139388.1) Examples of Using Oracle Data Pump Export 2.2 Starting Oracle Data Pump Export Start the Oracle Data Pump Export utility by using the expdp command. The characteristics of the Oracle Data Pump export operation are determined by The Export parameters that you specify. You can specify these parameters either on the command line, or in a parameter file. Caution: Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users. Oracle Data Pump Export InterfacesYou can interact with Oracle Data Pump Export by using a command line, a parameter file, or an interactive-command mode. Oracle Data Pump Export ModesExport provides different modes for unloading different portions of Oracle Database data. Network Considerations for Oracle Data Pump ExportLearn how Oracle Data Pump Export utility expdp identifies instances with connect identifiers in the connection string using Oracle*Net or a net service name, and how they are different from export operations using the NETWORK_LINK parameter. 2.2.1 Oracle Data Pump Export Interfaces You can interact with Oracle Data Pump Export by using a command line, a parameter file, or an interactive-command mode. Choose among the three options: Command-Line Interface: Enables you to specify most of the Export parameters directly on the command line. Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter, because parameter files cannot be nested. If you are using parameters whose values require quotation marks, then Oracle recommends that you use parameter files. Interactive-Command Interface: Stops logging to the terminal and displays the Export prompt, from which you can enter various commands, some of which are specific to interactive-command mode. This mode is enabled by pressing Ctrl+C during an export operation started with the command-line interface, or the parameter file interface. Interactive-command mode is also enabled when you attach to an executing or stopped job. 2.2.2 Oracle Data Pump Export Modes Export provides different modes for unloading different portions of Oracle Database data. Specify export modes on the command line, using the appropriate parameter. Note: You cannot export several Oracle-managed system schemas for Oracle Database, because they are not user schemas; they contain Oracle-managed data and metadata. Examples of system schemas that are not exported include

Data pump in oracle - Medium

One-time in-memory jobs, nothing is written to disk. For repeating in-memory jobs, job metadata is written to disk but there is no run information. In-memory jobs have better performance and scalability. Oracle Data Pump Parallel Import of Metadata The PARALLEL parameter for Oracle Data Pump, which previously applied only to data, has been extended to include metadata import operations. The performance of Oracle Data Pump import jobs has improved by enabling the use of multiple processes working in parallel to import metadata. Oracle Data Pump jobs now take less time because parallel import of metadata is added to this release. Oracle Data Pump Parallel Export of Metadata The PARALLEL parameter for Oracle Data Pump, which previously applied only to data, is extended to include metadata export operations. The performance of Oracle Data Pump export jobs is improved by enabling the use of multiple processes working in parallel to export metadata. Oracle Data Pump jobs now require shorter down time during migration and shorter elapsed time for export operations. New Options for Substitution Variables in Oracle Data Pump File Names Choices for substitution (wildcard) variables are now available for Oracle Data Pump dump file names. The new choices include date or time values, a larger range for numeric values, and system generated unique file names. Substitution variables improve file management for Oracle Data Pump dump files and enables you to take advantage of higher degrees of parallel processing without manually specifying individual file names. Renaming Data Files During Import New syntax is added to let users specify new file names, or file name transforms, for the data files in a transportable tablespace job. Renaming data files during import lowers management overhead by eliminating the need to execute ALTER TABLESPACE statements and rename or move files manually after the import is complete. TRUST_EXISTING_TABLE_PARTITIONS Flag for DATA_OPTIONS Parameter of Import A new TRUST_EXISTING_TABLE_PARTITIONS flag is added to the DATA_OPTIONS parameter for import. This option tells the Data Pump to load partition data in parallel into existing tables. This is done as part of a migration when the metadata is static and can be moved before the databases are taken offline to migrate the data. Moving the metadata separately minimizes downtime. If the DBA uses this mechanism and if other attributes of the database are the same (for example, character set), then the data from the export database goes to the same partitions in the import database. This new TRUST_EXISTING_TABLE_PARTITIONS flag enables data from multiple partitions to be loaded in parallel into a preexisting table which reduces the import time. GROUP_PARTITION_TABLE_DATA Flag for DATA_OPTIONS Parameter of Export A new GROUP_PARTITION_TABLE_DATA flag is added to the DATA_OPTIONS parameter for export. This option tells the Data

Data Pump for Oracle - SQLManager

Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter. If multiple filters are specified for an object type, then an implicit AND operation is applied to them. That is, objects pertaining to the job must pass all of the filters applied to their object types. The same metadata filter name can be specified multiple times within a job. To see a list of valid object types, query the following views: DATABASE_EXPORT_OBJECTS for full mode, SCHEMA_EXPORT_OBJECTS for schema mode, and TABLE_EXPORT_OBJECTS for table and tablespace mode. The values listed in the OBJECT_PATH column are the valid object types. For example, you could perform the following query: SQL> SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS 2 WHERE OBJECT_PATH LIKE '%GRANT' AND OBJECT_PATH NOT LIKE '%/%';The output of this query looks similar to the following:OBJECT_PATH--------------------------------------------------------------------------------COMMENTS--------------------------------------------------------------------------------GRANTObject grants on the selected tables OBJECT_GRANTObject grants on the selected tables PROCDEPOBJ_GRANTGrants on instance procedural objects PROCOBJ_GRANTSchema procedural object grants in the selected schemas ROLE_GRANTRole grants to users associated with the selected schemas SYSTEM_GRANTSystem privileges granted to users associated with the selected schemas 2.4 Parameters Available in Data Pump Export Command-Line Mode Use Oracle Data Pump parameters for Export (expdp) to manage your data exports. About Data Pump Export ParametersLearn how to use Oracle Data Pump Export parameters in command-line mode, including case sensitivity, quotation marks, escape characters, and information about how to use examples. ABORT_STEPThe Oracle Data Pump Export command-line utility ABORT_STEP parameter stops the job after it is initialized. ACCESS_METHODThe Oracle Data Pump Export command-line utility ACCESS_METHOD parameter instructs Export to use a particular method to unload data. ATTACHThe Oracle Data Pump Export command-line utility ATTACH parameter attaches a worker or client session to an existing export job, and automatically places you in the interactive-command interface. CLUSTERThe Oracle Data Pump Export command-line utility CLUSTER parameter determines whether Data Pump can use Oracle RAC, resources, and start workers on other Oracle RAC instances. COMPRESSIONThe Oracle Data Pump Export command-line utility COMPRESSION parameter specifies which data to compress before writing to the dump file. In this tutorial, you use the Oracle Data Pump Export utility to unload data then use the Oracle Data Pump Import utility to load data. What is Oracle Data Pump? Oracle Data Pump is a new

View Data Pump Jobs and Import Data with Data Pump - Oracle

The exported view must be in the same schema as the view. Tables that you want to serve as the source of the metadata for the exported view must be non-partitioned relational tables with heap organization. Tables that you want to serve as the source of the metadata for the exported view cannot be nested tables. Tables created using the VIEWS_AS_TABLES parameter do not contain any hidden or invisible columns that were part of the specified view. Views that you want exported as tables must exist, and must be relational views with only scalar, non-LOB columns. If you specify an invalid or non-existent view, then the view is skipped, and an error message is returned. The VIEWS_AS_TABLES parameter does not support tables that have columns with a data type of LONG. Example The following example exports the contents of view scott.view1 to a dump file named scott1.dmp. > expdp scott/password views_as_tables=view1 directory=data_pump_dir dumpfile=scott1.dmpThe dump file contains a table named view1 with rows obtained from the view. 2.5 Commands Available in Data Pump Export Interactive-Command Mode Check which command options are available to you when using Data Pump Export in interactive mode. About Oracle Data Pump Export Interactive Command ModeLearn about commands you can use with Oracle Data Pump Export in interactive command mode while your current job is running. ADD_FILEThe Oracle Data Pump Export interactive command mode ADD_FILE parameter adds additional files or substitution variables to the export dump file set. CONTINUE_CLIENTThe Oracle Data Pump Export interactive command mode CONTINUE_CLIENT parameter changes the Export mode from interactive-command mode to logging mode. EXIT_CLIENTThe Oracle Data Pump Export interactive command mode EXIT_CLIENT parameter stops the export client session, exits Export, and discontinues logging to the terminal, but leaves the current job running. FILESIZEThe Oracle Data Pump Export interactive command mode FILESIZE parameter redefines the maximum size of subsequent dump files. HELPThe Oracle Data Pump Export interactive command mode HELP parameter provides information about Data Pump Export commands available in interactive-command mode. KILL_JOBThe Oracle Data Pump Export interactive command mode KILL_JOB parameter detaches all currently attached worker client sessions, and then terminates the