Data Pump Import. This chapter describes the Oracle Data Pump Import utility (impdp). The following topics are discussed: What Is Data Pump Import? Data Pump Import (hereinafter referred to as Import for ease of reading) is a utility for loading an export dump file set into a target system. 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.
![Oracle Data Pump Import Parameter File Oracle Data Pump Import Parameter File](http://1.bp.blogspot.com/-lhDBwjfUNZs/Tollnei14XI/AAAAAAAAAJg/gVYxudc3nA4/s1600/1.png)
![Oracle Data Pump Import Parameter File Oracle Data Pump Import Parameter File](http://hi.csdn.net/attachment/201202/17/0_1329456848XPVp.gif)
During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. Import can also be used to load a target database directly from a source database with no intervening dump files. This is known as a network import. Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.
![Oracle Data Pump Import Parameter File Oracle Data Pump Import Parameter File](http://microsoftsamplequestions.myblog.it/wp-content/uploads/sites/322801/2014/09/410.png)
![Oracle Data Pump Import Parameter File Oracle Data Pump Import Parameter File](http://kamranagayev.com/wp-content/uploads/2010/04/porus1.jpg)
See "Filtering During Import Operations". To see some examples of the various ways in which you can use Import, refer to "Examples of Using Data Pump Import". Invoking Data Pump Import. The Data Pump Import utility is invoked using the impdp command. The characteristics of the import operation are determined by the import parameters you specify. These parameters can be specified either on the command line or in a parameter file. Note. Do not invoke Import 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. Note. Be aware that if you are performing a Data Pump Import into a table or tablespace created with the NOLOGGING clause enabled, then a redo log file may still be generated. The redo that is generated in such a case is generally for maintenance of the master table or related to underlying recursive space transactions, data dictionary changes, and index maintenance for indices on the table that require logging. The following sections contain more information about invoking Import: Data Pump Import Interfaces. You can interact with Data Pump Import by using a command line, a parameter file, or an interactive- command mode.
Command- Line Interface: Enables you to specify the Import parameters directly on the command line. For a complete description of the parameters available in the command- line interface, see "Parameters Available in Import'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 Import 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 import 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 Import's Interactive- Command Mode".
Data Pump Import Modes. The import mode determines what is imported. The specified mode applies to the source of the operation, either a dump file set or another database if the NETWORK_LINK parameter is specified. When the source of the import operation is a dump file set, specifying a mode is optional. If no mode is specified, then Import attempts to load the entire dump file set in the mode in which the export operation was run. The mode is specified on the command line, using the appropriate parameter.
The available modes are described in the following sections: Note. When you import a dump file that was created by a full- mode export, the import operation attempts to copy the password for the SYS account from the source database. This sometimes fails (for example, if the password is in a shared password file). If it does fail, then after the import completes, you must set the password for the SYS account at the target database to a password of your choice. Full Import Mode.
A full import is specified using the FULL parameter. In full import mode, the entire content of the source (dump file set or another database) is loaded into the target database.
This is the default for file- based imports. You must have the DATAPUMP_IMP_FULL_DATABASE role if the source is another database. Cross- schema references are not imported for non- privileged users. For example, a trigger defined on a table within the importing user's schema, but residing in another user's schema, is not imported.
The DATAPUMP_IMP_FULL_DATABASE role is required on the target database and the DATAPUMP_EXP_FULL_DATABASE role is required on the source database if the NETWORK_LINK parameter is used for a full import. Schema Mode. A schema import is specified using the SCHEMAS parameter. In a schema import, only objects owned by the specified schemas are loaded. The source can be a full, table, tablespace, or schema- mode export dump file set or another database. If you have the DATAPUMP_IMP_FULL_DATABASE role, then a list of schemas can be specified and the schemas themselves (including system privilege grants) are created in the database in addition to the objects contained within those schemas.
Cross- schema references are not imported for non- privileged users unless the other schema is remapped to the current schema. For example, a trigger defined on a table within the importing user's schema, but residing in another user's schema, is not imported. Table Mode. A table- mode import is specified using the TABLES parameter.
- . usually defined in a tnsnames.ora file), to import the data on that. Data Pump Import only works with Oracle Database. Comparable Data Pump Import Parameter.
- Data pump impdp parameter tips. Oracle tips by Burleson June 27, 2015. DATA_ONLY. Filter the import of dump file contents to the specified criteria.
- Impdp command tips Oracle tips by Burleson May 21. (data pump import). PARFILE Specify parameter file. QUERY.
- Oracle Data Pump Overview. Oracle Data Pump is a new and. Data Pump Import. for the dump file set. Network Mode. Data Pump Export and Import both.
- Data Pump Import can always read dump file sets. When you run Data Pump Import and specify the SQLFILE parameter. Oracle Data Pump Quick Start Oracle.
A parameter file is a text file listing the parameters for Oracle 12c’s Data Pump Export or Import and setting the chosen values. Data Pump Export and Import. I need to do import on my dbase and was given the DUMP file and import command how. I read from Oracle docs all about Data Pump Import. parameter is set to DATA.
In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, tablespace, or table- mode export dump file set or another database. You must have the DATAPUMP_IMP_FULL_DATABASE role to specify tables that are not in your own schema. You can use the transportable option during a table- mode import by specifying the TRANPORTABLE=ALWAYS parameter with the TABLES parameter.
Note that this requires use of the NETWORK_LINK parameter, as well. Tablespace Mode. A tablespace- mode import is specified using the TABLESPACES parameter. In tablespace mode, all objects contained within the specified set of tablespaces are loaded, along with the dependent objects. The source can be a full, schema, tablespace, or table- mode export dump file set or another database.
![Oracle Data Pump Import Parameter File Oracle Data Pump Import Parameter File](http://docs.oracle.com/cd/B14117_01/server.101/b10825/impsourcefileopts.gif)
For unprivileged users, objects not remapped to the current schema will not be processed. Transportable Tablespace Mode. A transportable tablespace import is specified using the TRANSPORT_TABLESPACES parameter.
In transportable tablespace mode, the metadata from another database is loaded using a database link (specified with the NETWORK_LINK parameter). There are no dump files involved.
The actual data files, specified by the TRANSPORT_DATAFILES parameter, must be made available from the source system for use in the target database, typically by copying them over to the target system. Encrypted columns are not supported in transportable tablespace mode.
This mode requires the DATAPUMP_IMP_FULL_DATABASE role. 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 Import utility.
The connect 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.
Import then prompts you for a password. Password: password. The local Import client connects to the database instance identified by the connect identifier inst. Specifying a connect identifier when you invoke the Import utility is different from performing an import operation using the NETWORK_LINK parameter. When you start an import operation and specify a connect identifier, the local Import client connects to the database instance identified by the connect identifier and imports the data from the dump file named on the command line to that database instance. Whereas, when you perform an import using the NETWORK_LINK parameter, the import is performed using a database link, and there is no dump file involved.