For example, if the regions table in the hr schema had a synonym of regn, an error would be returned if you specified regn as part of the REMPA_DATA specification. The available modes are described in the following sections: A full import is specified using the FULL parameter. See "FULL". See "FULL". The target schema must have sufficient quota in the target tablespace. See "NETWORK_LINK". The EMP table is exported without the EMPNO column being encrypted, but before importing the table you enable encryption on the EMPNO column. This execution set consists of a combination of worker processes and parallel I/O server processes. The Data Pump Import utility is invoked using the impdp command. It consists of a SQL operator and the values against which the object names of the specified type are to be compared. Alternatively, you can specify NOLOGFILE=YES. If no value is entered or if the default value of 0 is used, then no additional information is displayed beyond information about the completion of each object type, table, or partition. See "FULL". It logs any rows that cause non-deferred constraint violations, but does not stop the load for the data object experiencing the violation. Boolean values (y/n) are required for the SEGMENT_ATTRIBUTES, STORAGE, and OID transforms. See "SCHEMAS".
During the import operation, any columns in the employee_s_encrypt table that were encrypted during the export operation are decrypted before being imported. REMAP_SCHEMA loads all the objects from the source schema into the target schema. That is, the SQL file must be written to a disk file, not into the Oracle ASM storage. The connect identifier can specify a database instance that is different from the current instance identified by the current Oracle System ID (SID). It must be separated from the object type with a colon and enclosed in double quotation marks, because single quotation marks are required to delimit the name strings. Allows you to rename tables during an import operation. To see a list of valid paths for use with the INCLUDE parameter, you can 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. Perform a transportable tablespace export of tablespace tbs_1. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter. (A database link is a connection between two physical database servers that allows a client to access them as one logical database. This example will display a brief description of all Import parameters and commands. The ACCESS_METHOD parameter is provided so that you can try an alternative method if the default method does not work for some reason. You cannot use the none option or the merge option if the export was performed with the transportable method, along with a partition or subpartition filter.
If no mode were specified, then the default mode, SCHEMAS, would be used. It uses the merge option to merge all the partitions in sh.sales into one non-partitioned table in scott schema. The data files must already exist on the target database system. If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to either ALL or DATA_ONLY. A schema import is specified using the SCHEMAS parameter. The possible options are as follows: SEGMENT_ATTRIBUTES - If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. For example, suppose you have a table, EMP, and one of its columns is named EMPNO. Specifies the system change number (SCN) that Import will use to enable the Flashback utility.
If the NETWORK_LINK parameter is specified along with the QUERY parameter, then any objects specified in the query_clause that are on the remote (source) node must be explicitly qualified with the NETWORK_LINK value. Specifies the version of database objects to be imported (that is, only database objects and attributes that are compatible with the specified release will be imported). The datatypes of the source argument and the returned value should both match the datatype of the designated column in the table. A job that is terminated using KILL_JOB cannot be restarted. The job version must be set to 11.2 or higher. The following is an example of using the STATUS parameter. Note that full object path names are determined by the export mode, not by the import mode. For example, in the following command line, even though NOLOGFILE is a valid parameter, it would be interpreted as another dump file name for the DUMPFILE parameter: This would result in two dump files being created, test.dmp and nologfile.dmp. Because no import mode is specified, the default of schema mode will be used. Unless specifically noted, these parameters can also be specified in a parameter file. Specifies whether to import any Streams metadata that may be present in the export dump file. The remap function then returns a remapped value that will replace the original value in the target database. The following is an example of using the ATTACH parameter. Oracle Database Globalization Support Guide, Oracle Database Net Services Administrator's Guide, dependent objects of an identified object are processed along with the identified object, Oracle Database Advanced Security Administrator's Guide, Oracle Data Guard Concepts and Administration, Oracle Database Advanced Application Developer's Guide, Oracle Streams Replication Administrator's Guide, "Parameters Available in Import's Command-Line Mode", "Commands Available in Import's Interactive-Command Mode", "Default Locations for Dump, Log, and SQL Files", "Using Directory Objects When Oracle Automatic Storage Management Is Enabled", "Using PARALLEL During a Network Mode Import", "Moving Data Between Different Database Releases", Description of the illustration ''impinit.gif'', Description of the illustration ''impstart.gif'', Description of the illustration ''impmodes.gif'', Description of the illustration ''impopts.gif'', Description of the illustration ''impfilter.gif'', Description of the illustration ''impracopt.gif'', Description of the illustration ''impremap.gif'', Description of the illustration ''impfileopts.gif'', Description of the illustration ''impnetopts.gif'', Description of the illustration ''impdynopts.gif'', Description of the illustration ''impdiagnostics.gif''. Specifies that you want to perform a full database import. This example assumes that a job named import_job exists in the hr schema. This parameter is valid for transportable mode (or table mode when TRANSPORTABLE=ALWAYS was specified on the export) only when the NETWORK_LINK parameter is specified. The following sections contain more information about invoking Import: You can interact with Data Pump Import by using a command line, a parameter file, or an interactive-command mode. Use TABLESPACES to specify a list of tablespace names whose tables and dependent objects are to be imported from the source (full, schema, tablespace, or table-mode export dump file set or another database). There are no dump files involved. FULL is the default mode when you are performing a file-based import. Specifies the database edition into which objects should be imported. The value of the QUERY parameter is included in the WHERE clause of the SELECT portion of the INSERT statement. If you specify TRANSPORTABLE=ALWAYS, then all partitions specified on the TABLES parameter must be in the same table. The description of each syntax element, in the order in which they appear in the syntax, is as follows: schema -- the schema containing the table to be remapped.
The files are written in a proprietary, binary format. METADATA_ONLY loads only database object definitions; no table row data is loaded. If supplied, it designates the object type to which the transform will be applied. The failing statement is skipped and the job is restarted from the next work item. The default is y. Neither SQLFILE jobs nor imports done in transportable-tablespace mode are restartable. The name_clause is optional. This is known as a network import. The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. 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. It allows fine-grained selection of specific objects within an object type. If you do supply a value here, then it must be a directory object that already exists and that you have access to. The log file, schemas.log, is written to dpump_dir1. For example, tables containing new datatypes that are not supported in the specified release will not be imported. Table 3-1 Valid Object Types For the Data Pump Import TRANSFORM Parameter. This command imports the dump file set that is created when you run the example for the Export PARALLEL parameter. This is not a valid option if the CONTENT parameter is set to DATA_ONLY. In such cases, the limit is 4 KB. You can also use it to override the automatic naming of table partitions that were exported. That is, the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE. The SKIP_CURRENT option allows you to restart a job that previously failed to restart because execution of some DDL statement failed. To avoid this, specify either NOLOGFILE=YES or NOLOGFILE=NO. More than one table-specific query can be specified, but only one query can be specified per table. All data from the expfull.dmp dump file will be loaded except for functions, procedures, packages, and indexes whose names start with emp. Assume the following is in a parameter file, exclude.par, being used by a DBA or some other user with the DATAPUMP_IMP_FULL_DATABASE role. "Metadata Filters" for an example of using filtering. APPEND loads rows from the source and leaves existing rows unchanged. Oracle Database SQL Language Reference for more information about the CREATE DATABASE LINK statement, Oracle Database Administrator's Guide for more information about locally managed tablespaces. EXCLUDE=REF_CONSTRAINT will exclude referential integrity (foreign key) constraints.
This is not a valid option if the CONTENT parameter is set to DATA_ONLY. Specifies that you want to perform a tablespace-mode import. A nonpartitioned table, scott.non_part_table, has 1 table_data object: A partitioned table, scott.part_table (having partition p1 and partition p2), has 2 table_data objects: A subpartitioned table, scott.sub_part_table (having partition p1 and p2, and subpartitions p1s1, p1s2, p2s1, and p2s2) has 4 table_data objects: During a network mode import, each table_data object is assigned its own worker process, up to the value specified for the PARALLEL parameter. See "FULL". (See "SAMPLE". Attaches the client session to an existing import job and automatically places you in interactive-command mode. That is, objects participating in the job must pass all of the filters applied to their object types. In an Oracle Real Application Clusters (Oracle RAC) environment, if an import operation has PARALLEL=1, then all Data Pump processes reside on the instance where the job is started. The transform_name specifies the name of the transform. The name_clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT). Detaches all currently attached client sessions and then terminates the current 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 Import ESTIMATE parameter is valid only if the NETWORK_LINK parameter is also specified. The job name is implicitly qualified by the schema of the user performing the import operation.
The use of filtering can restrict what is imported using this import mode. Otherwise, the default is none. A value of departition promotes each partition or subpartition to a new individual table. Allows you to specify a query clause that filters the data that gets imported. You must supply a password before a database connection is made. See "FULL". See "FULL".
Suppose you have a parameter file, tablespaces.par, with the following content: Specifies whether the transportable option should be used during a table mode import (specified with the TABLES parameter) to import only metadata for specific tables, partitions, and subpartitions.
For example, the following Export commands create dump file sets with the necessary metadata to create a schema, because the user SYSTEM has the necessary privileges: If your dump file set does not contain the metadata necessary to create a schema, or if you do not have privileges, then the target schema must be created before the import operation is performed. The following is an example of using the TARGET_EDITION parameter: This example assumes the existence of an edition named exp_edition on the system to which objects are being imported. For REPLACE, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system. The files are not required to have the same names, locations, or order that they had at export time. Therefore, loading such data objects will take longer when the SKIP_CONSTRAINT_ERRORS option is used. The following is an example of using the STREAMS_CONFIGURATION parameter. Specifies whether to suppress the default behavior of creating a log file. Because a directory object (dpump_dir2) is specified for the exp1.dmp dump file, the import job will look there for the file. Note that you can use the PCTSPACE transform with the Data Pump Export SAMPLE parameter so that the size of storage allocations matches the sampled data subset. This is true both on the command line and within parameter files. Some operating systems treat quotation marks as special characters and will therefore not pass them to an application unless they are preceded by an escape character, such as the backslash (\). If you have the DATAPUMP_IMP_FULL_DATABASE role, then you can use this parameter to perform a schema-mode import by specifying a list of schemas to import. 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. The following is an example of using the REUSE_DATAFILES parameter. The following is an example of using the REMAP_TABLE parameter to rename the employees table to a new name of emps: Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace. This is the default. If different filters using the same name are applied to both a particular table and to the whole job, then the filter parameter supplied for the specific table takes precedence.
The FLASHBACK_TIME parameter pertains only to the flashback query capability of Oracle Database. The only exception is the PARFILE parameter because parameter files cannot be nested. Note that passwords are not included in the SQL file. Enables you to filter the metadata that is imported by specifying objects and object types for the current import mode. "Performing a Data-Only Table-Mode Import". This chapter describes the Oracle Data Pump Import utility (impdp). 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. If KU$ is not used for a table alias, then all rows are loaded: The maximum length allowed for a QUERY string is 4000 bytes including quotation marks, which means that the actual maximum length allowed is 3998 bytes. However, if the transportable set contains an index, but not the table, then the import operation is terminated. (If you want to try the example, then you must create this file.). You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter. You would also need to specify the TRANSPORT_DATAFILES parameter to let the import know where to find the actual data, which had been copied to the target in a separate operation using some other means. If the table into which you are importing does not already exist and Data Pump has to create it, then the import runs in parallel up to the parallelism specified on the PARALLEL parameter when the import is started. This parameter can be used to load a target system whose Oracle database is at an earlier compatibility release than that of the source system. This parameter enables you to make trade-offs between resource consumption and elapsed time. Remapping functions should not perform commits or rollbacks except in autonomous transactions. Default: the default database edition on the system. The examples assume that the hr user has been granted these roles. Example 3-1 Performing a Data-Only Table-Mode Import. In such cases, the limit is 4 KB. The following example will display the current job status and change the logging mode display interval to two minutes (120 seconds). Transportable tablespace jobs do not support the ACCESS_METHOD parameter for Data Pump Import.
Log files are not deleted. For example, if the regions table in the hr schema had a synonym of regn, then it would not be valid to use TABLES=regn. To use filters correctly and to get the results you expect, remember that dependent objects of an identified object are processed along with the identified object.
If SOURCE_EDITION=edition_name is specified, then the objects from that edition are imported.
