Import Parameters
The parameters are as follows:
ANALYZE
Default: Y
Specifies whether or not the Import utility executes SQL ANALYZE statements found in the export file.
BUFFER
Default: operating system-dependent
The buffer-size is the size, in bytes, of the buffer through which data rows are transferred.
The BUFFER (buffer size) parameter determines the number of rows in the array inserted by Import. The following formula gives an approximation of the buffer size that inserts a given array of rows:
buffer_size = rows_in_array * maximum_row_size
For tables containing LONG, LOB, BFILE, REF, ROWID or type columns, rows are inserted individually. The size of the buffer must be large enough to contain the entire row, except for LOB columns. If the buffer cannot hold the longest row in a table, Import attempts to allocate a larger buffer.
Additional Information: See your Oracle operating system-specific documentation to determine the default value for this parameter.
CHARSET
Default: none
Note: This parameter applies to Oracle Version 6 export files only.
Oracle Version 6 export files do not contain the NLS character set identifier. However, a Version 6 export file indicates whether the user session character set was ASCII or EBCDIC.
Use this parameter to indicate the actual character set used at the time of export. The Import utility will verify whether the specified character set is ASCII or EBCDIC based on the character set in the export file.
If you do not specify a value for the CHARSET parameter, Import will verify that the user session character set is ASCII, if the export file is ASCII, or EBCDIC, if the export file is EBCDIC.
Use of this parameter is not recommended. It is provided only for compatibility with previous versions. Eventually, it will no longer be supported.
If you are using an Oracle7 or Oracle8 Export file, the character set is specified within the export file, and conversion to the current database's character set is automatic.
Specification of this parameter serves only as a check to ensure that the export file's character set matches the expected value. If not, an error results.
COMMIT
Default: N
Specifies whether or not Import should commit after each array insert. By default, Import commits after loading each table, and Import performs a rollback when an error occurs, before continuing with the next object.
If a table has nested table columns or attributes, the contents of the nested tables are imported as separate tables. Therefore, the contents of the nested tables are always committed in a transaction distinct from the transaction used to commit the outer table.
If COMMIT=N and a table is partitioned, each partition in the Export file is imported in a separate transaction.
Specifying COMMIT=Y prevents rollback segments from growing inordinately large and improves the performance of large imports. Specifying COMMIT=Y is advisable if the table has a uniqueness constraint. If the import is restarted, any rows that have already been imported are rejected with a non-fatal error. Note that, if a table does not have a uniqueness constraint, and you specify COMMIT=Y, Import could produce duplicate rows when you re-import the data.
For tables containing LONG, LOB, BFILE, REF, ROWID or type columns, array inserts are not done. If COMMIT=Y, Import commits these tables after each row.
DESTROY
Default: N
Specifies whether or not the existing data files making up the database should be reused. That is, the DESTROY parameter specifies that Import should include the reuse option in the datafile clause of the CREATE TABLESPACE command.
The export file contains the datafile names used in each tablespace. If you attempt to create a second database on the same machine (for testing or other purposes), the Import utility overwrites the original database's data files when it creates the tablespace. This is undesirable. With this parameter set to N (the default), an error occurs if the data files already exist when the tablespace is created.
To eliminate this error when you import into a secondary database, pre-create the tablespace and specify its data files. (Specifying IGNORE=Y suppresses the object creation error that the tablespace already exists.)
To bypass the error when you import into the original database, specify IGNORE=Y to add to the existing data files without replacing them. To reuse the original database's data files after eliminating their contents, specify DESTROY=Y.
Note: If you have pre-created your tablespace, you must specify DESTROY=N or your pre-created tablespace will be lost.
Warning: If datafiles are stored on a raw device, DESTROY=N does not prevent files from being overwritten.
FEEDBACK
Default: 0 (zero)
Specifies that Import should display a progress meter in the form of a dot for n number of rows imported. For example, if you specify FEEDBACK=10, Import displays a dot each time 10 rows have been imported. The FEEDBACK value applies to all tables being imported; it cannot be set on a per-table basis.
FILE
Default: expdat.dmp
The name of the export file to import. You do not have to be the Oracle user who exported the file. However, you do need to have current access to the file. The default extension is .dmp, but you can specify any extension.
FROMUSER
Default: none
A list of schemas containing objects to import. The default for users without the IMP_FULL_DATABASE role is a user mode import. That is, all objects for the current user are imported. (If the TABLES parameter is also specified, a table mode import is performed.)
When importing in user mode, all other objects in the export file are ignored. The effect is the same as if the export file had been created in user mode (or table mode).
For example, the following command treats the export file as though it were simply a user mode export of SCOTT's objects:
imp system/manager FROMUSER=scott
If user SCOTT does not exist in the current database, his objects are imported into the importer's schema - in this case, the system's. Otherwise, the objects are imported in SCOTT's schema. If a list of schemas is given, each schema can be specified only once. Duplicate schema names are ignored. The following example shows an import from two schemas:
imp system/manager FROMUSER=scott,blake
Note: Specifying FROMUSER=SYSTEM does not import system objects. It imports only those objects that belong to user SYSTEM.
When FROMUSER is specified and TOUSER is missing, the objects of FROMUSER are imported back to FROMUSER. However, if the schema specified in FROMUSER does not exist in the current database, the objects are imported into the importer's schema.
To import system objects (for example, user definitions and tablespaces), you must import from a full export file specifying FULL=Y.
FULL
Default: N
Specifies whether or not to import the entire export file.
GRANTS
Default: Y
Specifies whether or not to import grants.
By default, the Import utility imports any grants that were exported. If the export was a user-mode Export, the export file contains only first-level grants (those granted by the owner). If the export was a full database mode Export, the export file contains all grants, including lower-level grants (those granted by users given a privilege with the WITH GRANT OPTION). If you specify GRANTS=N, the Import utility does not import grants.
HELP
Default: N
Displays a description of the Import parameters.
IGNORE
Default: N
Specifies how object creation errors should be handled. If you specify IGNORE=Y, Import overlooks object creation errors when it attempts to create database objects. If you specify IGNORE=Y, Import continues without reporting the error. If you accept the default IGNORE=N, Import logs and/or displays the object creation error before continuing.
For tables, IGNORE=Y causes rows to be imported into existing tables. No message is given. IGNORE=N causes an error to be reported, and the table is skipped if it already exists.
Note that only object creation errors are ignored; other errors, such as operating system, database, and SQL errors, are not ignored and may cause processing to stop.
In situations where multiple refreshes from a single export file are done with IGNORE=Y, certain objects can be created multiple times (although they will have unique system-defined names). You can prevent this for certain objects (for example, constraints) by doing an export in table mode with the CONSTRAINTS=N parameter. Note that, if you do a full export with the CONSTRAINTS parameter set to N, no constraints for any tables are exported.
If you want to import data into tables that already exist- perhaps because you want to use new storage parameters, or because you have already created the table in a cluster - specify IGNORE=Y. The Import utility imports the rows of data into the existing table.
Warning: When you import into existing tables, if no column in the table is uniquely indexed, rows could be duplicated if they were already present in the table. (This warning applies to non-incremental imports only. Incremental imports replace the table from the last complete export and then rebuild it to its last backup state from a series of cumulative and incremental exports.)
INCTYPE
Default: undefined
Specifies the type of incremental import.
The options are:
SYSTEM
Imports the most recent version of system objects. You should specify the most recent incremental export file when you use this option. A SYSTEM import imports foreign function libraries and object type definitions, but does not import user data or objects.
RESTORE
Imports all user database objects and data that are contained in the export file.
INDEXES
Default: Y
Specifies whether or not to import indexes. System-generated indexes such as LOB indexes, OID indexes, or unique constraint indexes are re-created by Import regardless of the setting of this parameter.
If indexes for the target table already exist, Import performs index maintenance when data is inserted into the table.
You can postpone all user-generated index creation until after Import completes by specifying INDEXES = N.
INDEXFILE
Default: none
Specifies a file to receive index-creation commands.
When this parameter is specified, index-creation commands for the requested mode are extracted and written to the specified file, rather than used to create indexes in the database. Tables and other database objects are not imported.
The file can then be edited (for example, to change storage parameters) and used as a SQL script to create the indexes. To make it easier to identify the indexes defined in the file, the export file's CREATE TABLE statements and CREATE CLUSTER statements are included as comments.
Note: Since Release 7.1, the commented CREATE TABLE statement in the indexfile does not include primary/unique key clauses.
Perform the following steps to use this feature:
Import using the INDEXFILE parameter to create a file of index-creation commands.
Edit the file, making certain to add a valid password to the CONNECT string.
Rerun Import, specifying INDEXES=N.
This step imports the database objects while preventing Import from using the index definitions stored in the export file.
Execute the file of index-creation commands as a SQL script to create the index.
The INDEXFILE parameter can be used only with the FULL=Y, FROMUSER, TOUSER, or TABLES parameters.
LOG
Default: none
Specifies a file to receive informational and error messages. If you specify a log file, the Import utility writes all information to the log in addition to the terminal display.
PARFILE
Default: undefined
Specifies a filename for a file that contains a list of Import parameters. For more information on using a parameter file, see "The Import Parameter File".
POINT_IN_TIME_RECOVER
Default: N
Indicates whether or not Import recovers one or more tablespaces in an Oracle database to a prior point in time, without affecting the rest of the database
RECORDLENGTH
Default: operating system-dependent
Specifies the length in bytes of the file record. The RECORDLENGTH parameter is required when you import to another operating system that uses a different default value.
Additional Information: See the Oracle operating system-specific documentation to determine the default value for this parameter.
ROWS
Default: Y
Specifies whether or not to import the rows of table data.
SHOW
Default: N
When you specify SHOW, the contents of the export file are listed to the display and not imported. The SQL statements contained in the export are displayed in the order in which Import will execute them.
The SHOW parameter can be used only with the FULL=Y, FROMUSER, TOUSER, or TABLES parameters.
SKIP_UNUSABLE_INDEXES
Default: N
Specifies whether or not Import skips building indexes that were set to the Index Unusable state (set by either system or user). Refer to "ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE" in the Oracle Server SQL Reference manual for details. Other indexes (not previously set Index Unusable) continue to be updated as rows are inserted.
This parameter allows you to postpone index maintenance on selected index partitions until after row data has been inserted. You then have the responsibility to rebuild the affected index partitions after the Import. You can use the INDEXFILE parameter in conjunction with INDEXES = N to provide the SQL scripts for re-creating the index. Without this parameter, row insertions that attempt to update unusable indexes fail.
TABLES
Default: none
Specifies a list of table names to import. Use an asterisk (*) to indicate all tables. When specified, this parameter initiates a table mode import, which restricts the import to tables and their associated objects, as listed in Table 1-1 on page 1-5. The number of tables that can be specified at the same time is dependent on command line limits.
Any table-level Import or partition-level Import attempts to create a partitioned table with the same partition names as the exported partitioned table, including names of the form SYS_Pnnn. If a table with the same name already exists, Import processing depends on the setting of the IGNORE parameter.
Unless SKIP_UNUSABLE_INDEXES=Y, inserting the exported data into the target table fails if Import cannot update a non-partitioned index or index partition that is marked Indexes Unusable or otherwise not suitable.
Although you can qualify table names with schema names (as in SCOTT.EMP) when exporting, you cannot do so when importing. In the following example, the TABLES parameter is specified incorrectly:
imp system/manager TABLES=(jones.accts, scott.emp,scott.dept)
The valid specification to import these tables is:
imp system/manager FROMUSER=jones TABLES=(accts)
imp system/manager FROMUSER=scott TABLES=(emp,dept)
If TOUSER is specified, SCOTT's objects are stored in the schema specified by TOUSER. If user SCOTT does not exist in the current database, his tables are imported into the importer's schema - system in the previous example. Otherwise, the tables and associated objects are installed in SCOTT's schema.
Additional Information: Some operating systems, such as UNIX, require that you use escape characters before special characters, such as a parenthesis, so that the character is not treated as a special character. On UNIX, use a backslash (\) as the escape character, as shown in the following example:
TABLES=\(EMP,DEPT\)
Table Name Restrictions
Table names specified on the command line or in the parameter file cannot include a pound (#) sign, unless the table name is enclosed in quotation marks.
For example, if the parameter file contains the following line, Import interprets everything on the line after EMP# as a comment. As a result, DEPT and MYDATA are not imported.
TABLES=(EMP#, DEPT, MYDATA)
However, if the parameter file contains the following line, the Import utility imports all three tables:
TABLES=("EMP#", DEPT, MYDATA)
Attention: When you specify the table name in quotation marks, it is case sensitive. The name must exactly match the table name stored in the database. By default, database names are stored as uppercase.
Additional Information: Some operating systems require single quotes instead of double quotes. See your Oracle operating system-specific documentation.
TOUSER
Default: none
Specifies a list of usernames whose schemas will be imported. The IMP_FULL_DATABASE role is required to use this parameter.
To import to a different schema than the one that originally contained the object, specify TOUSER. For example:
imp system/manager FROMUSER=scott TOUSER=joe TABLES=emp
If multiple schemas are specified, the schema names are paired. The following example imports SCOTT's objects into JOE's schema, and FRED's objects into TED's schema:
imp system/manager FROMUSER=scott,fred TOUSER=joe,ted
USERID
Default: undefined
Specifies the username/password of the user performing the import.
Optionally, you can specify the @connect_string clause for Net8