Data Pump Export. This chapter describes the Oracle Data Pump Export utility (expdp). The following topics are discussed: What Is Data Pump Export? Data Pump Export (hereinafter referred to as Export for ease of reading) is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility. The dump file set can be imported on the same system or it can be moved to another system and loaded 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 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, the database administrator (DBA) must create directory objects that define the server locations to which files are written.
Code Conventions for the Java TM Programming Language. Revised April 20, 1999. Or visit the Oracle Community space to leave feedback, offer suggestions, or ask community-related questions. Do not post product-related questions in the feedback space; instead, navigate to or search for an appropriate.
See "Default Locations for Dump, Log, and SQL Files" for more information about directory objects. Data Pump Export enables you to specify that a job should move a subset of the data and metadata, as determined by the export mode. This is done using data filters and metadata filters, which are specified through Export parameters. See "Filtering During Export Operations". To see some examples of the various ways in which you can use Data Pump Export, refer to "Examples of Using Data Pump Export". Invoking Data Pump Export. The Data Pump Export utility is invoked using the expdp command.
The characteristics of the export operation are determined by the Export parameters you specify. These parameters can be specified either on the command line or in a parameter file. Note. Do not invoke 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. The following sections contain more information about invoking Export: Data Pump Export Interfaces. You can interact with Data Pump Export by using a command line, a parameter file, or an interactive- command mode. Command- Line Interface: Enables you to specify most of the Export parameters directly on the command line.
For a complete description of the parameters available in the command- line interface, see "Parameters Available in Export's Command- Line Mode". 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. The use of parameter files is recommended if you are using parameters whose values require quotation marks. See "Use of Quotation Marks On the Data Pump Command Line". 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.
Overview. When you use CREATE INDEX to create an index or ALTER INDEX to manage an index, you can optionally specify indexing preferences, stoplists, and section groups in the parameter string. Specifying a preference. 176 Responses to “Step by Step installing Oracle 10g RAC on VMware” Wissem Says: April 5th, 2011 at 1:35 pm. Thanks Brother, Really Useful Cheers, Wissem. Muhammad Adnan Says: April 5th, 2011 at 2:57 pm. really Excellent. Followup October 30, 2007 - 1:12 pm UTC. the dmp file must be accessible on a file system local to the database server itself. is it? if it is on your PC, you need to make your filesystem 'visible' to the server itself. impdb.
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. For a complete description of the commands available in interactive- command mode, see "Commands Available in Export's Interactive- Command Mode".
Oracle Par File Comments And Graphics
Data Pump Export Modes. Export provides different modes for unloading different portions of the database. The mode is specified on the command line, using the appropriate parameter. The available modes are described in the following sections: Note. Several system schemas cannot be exported because they are not user schemas; they contain Oracle- managed data and metadata. Examples of system schemas that are not exported include SYS, ORDSYS, and MDSYS.
Full Export Mode. A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the DATAPUMP_EXP_FULL_DATABASE role.
See Also: "FULL" for a description of the Export FULL parameter. Schema Mode. A schema export is specified using the SCHEMAS parameter. This is the default export mode. If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of schemas, optionally including the schema definitions themselves and also system privilege grants to those schemas.
Archiving Oracle Data in easily restorable non-Oracle structures Oracle Tips by John Adolph Palinski. Here is the procedure to Determine the process which is locking an Oracle account with incorrect password, which search from the dba_audit_session view for records with a returncode equal to 1017 which indicate a failed logon.
If you do not have the DATAPUMP_EXP_FULL_DATABASE role, then you can export only your own schema. The SYS schema cannot be used as a source schema for export jobs. Cross- schema references are not exported unless the referenced schema is also specified in the list of schemas to be exported.
For example, a trigger defined on a table within one of the specified schemas, but that resides in a schema not explicitly specified, is not exported. This is also true for external type definitions upon which tables in the specified schemas depend. In such a case, it is expected that the type definitions already exist in the target instance at import time. See Also: "SCHEMAS" for a description of the Export SCHEMAS parameter. Table Mode. A table mode export is specified using the TABLES parameter. In table mode, only a specified set of tables, partitions, and their dependent objects are unloaded. If you specify the TRANSPORTABLE=ALWAYS parameter with the TABLES parameter, then only object metadata is unloaded.
To move the actual data, you copy the data files to the target database. This results in quicker export times. If you are moving data files between releases or platforms, then the data files may need to be processed by Oracle Recovery Manager (RMAN). You must have the DATAPUMP_EXP_FULL_DATABASE role to specify tables that are not in your own schema. Note that type definitions for columns are not exported in table mode. It is expected that the type definitions already exist in the target instance at import time. Also, as in schema exports, cross- schema references are not exported.
See Also: "TABLES" for a description of the Export TABLES parameter"TRANSPORTABLE" for a description of the Export TRANSPORTABLE parameter. Tablespace Mode. A tablespace export is specified using the TABLESPACES parameter. In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded.
If a table is unloaded, then its dependent objects are also unloaded. Both object metadata and data are unloaded. In tablespace mode, if any part of a table resides in the specified set, then that table and all of its dependent objects are exported. Privileged users get all tables. Unprivileged users get only the tables in their own schemas. See Also: "TABLESPACES" for a description of the Export TABLESPACES parameter. Transportable Tablespace Mode.
A transportable tablespace export is specified using the TRANSPORT_TABLESPACES parameter. In transportable tablespace mode, only the metadata for the tables (and their dependent objects) within a specified set of tablespaces is exported. The tablespace data files are copied in a separate operation.
Then, a transportable tablespace import is performed to import the dump file containing the metadata and to specify the data files to use. Transportable tablespace mode requires that the specified tables be completely self- contained. That is, all storage segments of all tables (and their indexes) defined within the tablespace set must also be contained within the set.
If there are self- containment violations, then Export identifies all of the problems without actually performing the export. Transportable tablespace exports cannot be restarted once stopped. Also, they cannot have a degree of parallelism greater than 1. Encrypted columns are not supported in transportable tablespace mode.
Note. You cannot export transportable tablespaces and then import them into a database at a lower release level. The target database must be at the same or higher release level as the source database. Considerations for Time Zone File Versions in Transportable Tablespace Mode. Jobs performed in transportable tablespace mode have the following requirements concerning time zone file versions: If the source is Oracle Database 1. TIMESTAMP WITH TIMEZONE (TSTZ) columns, then the time zone file version on the target database must exactly match the time zone file version on the source database. If the source is earlier than Oracle Database 1. TSTZ columns. If these requirements are not met, then the import job aborts before anything is imported.
This is because if the import job were allowed to import the objects, there might be inconsistent results when tables with TSTZ columns were read. To identify the time zone file version of a database, you can execute the following SQL statement. SQL> SELECT VERSION FROM V$TIMEZONE_FILE. Network Considerations. You can specify a connect identifier in the connect string when you invoke the Data Pump Export utility.
This identifier can specify a database instance that is different from the current instance identified by the current Oracle System ID (SID). The connect identifier can be an Oracle*Net connect descriptor or a net service name (usually defined in the tnsnames. Use of a connect identifier requires that you have Oracle Net Listener running (to start the default listener, enter lsnrctlstart). The following is an example of this type of connection, in which inst. DIRECTORY=dpump_dir. DUMPFILE=hr. dmp TABLES=employees.
Export then prompts you for a password. Password: password. The local Export client connects to the database instance defined by the connect identifier inst.
Specifying a connect identifier when you invoke the Export utility is different from performing an export operation using the NETWORK_LINK parameter. When you start an export operation and specify a connect identifier, the local Export client connects to the database instance identified by the connect identifier, retrieves data from that database instance, and writes it to a dump file set on that database instance.