Import Parameters - Oracle 8i

The following diagrams show the syntax for the parameters that you can specify in the parameter file or on the command line.









Default: Y

Specifies whether or not the Import utility executes SQL ANALYZE statements found in the export file or loads optimizer statistics for tables, indexes, and columns that were precomputed on the Export system.

Default: operating system-dependent

The buffer-size is the size, in bytes, of the buffer through which data rows are transferred.

The parameter BUFFER (buffer size) 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, LOGICAL ROWID, DATE, or type columns, rows are inserted individually. The size of the buffer must be large enough to contain the entire row, except for LOB and LONG 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.

Note: This parameter applies to Oracle Version 5 and 6 export files only. Use of this parameter is not recommended. It is provided only for compatibility with previous versions. Eventually, it will no longer be supported.

Default: N
Specifies whether Import should commit after each array insert. By default, Import commits only 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 and subpartition 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 nonfatal error.

If a table does not have a uniqueness constraint, Import could produce duplicate rows when you reimport the data.

For tables containing LONG, LOB, BFILE, REF, ROWID, UROWID, DATE, or type columns, array inserts are not done. If COMMIT=Y, Import commits these tables after each row.

Default: Y
Specifies whether or not table constraints are to be imported. Note that the default is to import constraints. If you do not want constraints to be imported, you must set the parameter value to N.

Default: none
When TRANSPORT_TABLESPACE is specified as Y, use this parameter to list the datafiles to be transported into the database.

Default: N
Specifies whether or not the existing data files making up the database should be reused. That is, specifying DESTROY=Y causes Import to include the REUSE option in the datafile clause of the CREATE TABLESPACE command, which causes Import to reuse the original database’s data files after deleting their contents.

Note that the export file contains the data file names used in each tablespace. If you specify DESTROY=Y and attempt to create a second database on the same machine (for testing or other purposes), the Import utility will overwrite the first database’s data files when it creates the tablespace. In this situation you should use the default, DESTROY=N, so that an error occurs if the data files already exist when thetablespace is created. Also, when you need to import into the original database, you will need to specify IGNORE=Y to add to the existing data files without replacing them.

Caution: If data files are stored on a raw device, DESTROY=N does not prevent files from being overwritten.

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.

Default: expdat.dmp
Specifies the names of the export files to import. The default extension is .dmp.

Because Export supports multiple export files, you may need to specify multiple filenames to be imported. You need not be the user who exported the export files; however, you must have read access to the files. If you were not the exporter of the export files, you must also have the IMP_FULL_DATABASE role granted to you.

Export supports writing to multiple export files, and Import can read from multiple export files. If, on export, you specify a value (byte limit) for the Export FILESIZE parameter, Export will write only the number of bytes you specify to each dump file. On import, you must use the Import parameter FILESIZE to tell Import the maximum dump file size you specified on export.

Note: The maximum value that can be stored in a file is operating system-dependent. You should verify this maximum value in your operating system-specific documentation before specifying FILESIZE.

The FILESIZE value can be specified as a number followed by K (number of kilobytes). For example, FILESIZE=2K is the same as FILESIZE=2048. Similarly, M specifies megabytes (1024 * 1024) while G specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048b is the same as FILESIZE=2048).

Default: none
A comma-separated list of schemas to import. This parameter is relevant only to users with the IMP_FULL_DATABASE role. The parameter enables you to import a subset of schemas from an export file containing multiple schemas (for example, a full export dump file or a multischema, user mode export dump file).

You will typically use FROMUSER in conjunction with the Import parameter TOUSER, which you use to specify a list of usernames whose schemas will be targets for import. However, if you omit specifyingTOUSER, Import will:

  • Import objects into the FROMUSER’s schema if the export file is a full dump or a multischema, user mode export dump file
  • Create objects in the importer’s schema (regardless of the presence of or absence of the FROMUSER schema on import) if the export file is a single-schema, user mode export dump file created by an unprivileged user

Note: Specifying FROMUSER=SYSTEM causes only schema objects belonging to user SYSTEM to be imported; it does not cause system objects to be imported.

Default: N
Specifies whether to import the entire export file.

Default: Y
Specifies whether to import object grants.

By default, the Import utility imports any object grants that were exported. If the export was a user-mode Export, the export file contains only first-level object grants (those granted by the owner).

If the export was a full database mode Export, the export file contains all object 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 object grants. (Note that system grants are imported even if GRANTS=N.)

Note: Export does not export grants on data dictionary views for security reasons that affect Import. If such grants were exported, access privileges would be changed and the importer would not be aware of this.

Default: N
Displays a description of the Import parameters.

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 and continues without reporting the errors. Even if IGNORE=Y, Import will not replace an existing object; instead, it will skip the object.

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. If a table already exists, IGNORE=N causes an error to be reported, and the table is skipped with no rows inserted. Also, objects dependent on tables, such as indexes, grants, and constraints, will not be created if a table already exists and IGNORE=N.

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 import with the value of the parameterCONSTRAINTS set to N. If you do a full import with the CONSTRAINTS parameter set to N, no constraints for any tables are imported.

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.

Caution: 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 applies to nonincremental 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.)

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
system objects such as foreign function libraries and
object type definitions, but does not import user data or
RESTORE : Imports all user database objects and data contained in
the export file, excluding system objects.

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.

You can postpone all user-generated index creation until after Import completes, by specifying INDEXES=N.

If indexes for the target table already exist at the time of the import, Import performs index maintenance when data is inserted into the table.

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. No database objects are imported.

If the Import parameter CONSTRAINTS is set to Y, Import also writes table constraints to the index file.

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. Perform the following steps to use this feature:

  1. Import using the INDEXFILE parameter to create a file of index-creation commands.
  2. Edit the file, making certain to add a valid password to the CONNECT strings.
  3. Rerun Import, specifying INDEXES=N.
    (This step imports the database objects while preventing Import from using the index definitions stored in the export file.)
  4. 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.

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.

Default: undefined
Specifies a filename for a file that contains a list of Import parameters.

Default: N

Setting this parameter to Y will cause database optimizer statistics to generate when the exported data is imported.

Default: operating system-dependent

Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter is necessary when you must transfer the export file to another operating system that uses a different default value.

If you do not define this parameter, it defaults to your platform-dependent value for BUFSIZ. For more information about the BUFSIZ default value, see your operating system-specific documentation.

You can set RECORDLENGTH to any value equal to or greater than your system’s BUFSIZ. (The highest value is 64KB.) Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to the database. It does not affect the operating system file block size.

Note: You can use this parameter to specify the size of the Import I/O buffer.

Additional Information: See your Oracle operating system-specific documentation to determine the proper value or to create a file with a different record size.

Default: Y
Specifies whether or not to import the rows of table data.

Default: N
When SHOW=Y, 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.

Default: N

Specifies whether or not Import skips building indexes that were set to the Index Unusable state (by either system or user). 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 will fail.

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. The number of tables that can be specified at the same time is dependent on command-line limits.

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)

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:


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.


However, if the parameter file contains the following line, the Import utility imports all three tables:


Note: 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 quotation marks instead of double quotation marks. See your Oracle operating system-specific documentation.

Default: none

When TRANSPORT_TABLESPACE is specified as Y, use this parameter to provide a list of tablespaces to be transported into the database.

Default: none

When you import a table that references a type, but a type of that name already exists in the database, Import attempts to verify that the preexisting type is, in fact, the type used by the table (rather than a different type that just happens to have the same name).

To do this, Import compares the type's unique identifier (TOID) with the identifier stored in the export file. Import will not import the table rows if the TOIDs do not match.

In some situations, you may not want this validation to occur on specified types (for example, if the types were created by a cartridge installation). You can use the TOID_NOVALIDATE parameter to specify types to exclude from TOID comparison.

The syntax is as follows:

toid_novalidate=([schemaname.]typename [, ...])
For example:

imp scott/tiger table=foo toid_novalidate=bar
imp scott/tiger table=foo toid_novalidate=(fred.type0,sally.type2,type3)

If you do not specify a schemaname for the type, it defaults to the schema of the importing user. For example, in the first preceding example, the type "bar" defaults to "".

The output of a typical import with excluded types would contain entries similar to the following:

. importing IMP3's objects into IMP3
. . skipping TOID validation on type IMP2.TOIDTYP0
. . importing table "TOIDTAB3"

Note: When you inhibit validation of the type identifier, it is your responsibility to ensure that the attribute list of the imported type matches the attribute list of the existing type. If these attribute lists do not match, results are unpredictable.

Default: none

Specifies a list of usernames whose schemas will be targets for import. 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

Note: If the FROMUSER list is longer than the TOUSER list, the remaining schemas will be imported into either the FROMUSER schema, or into the importer’s schema, based on normal defaulting rules. You can use the following syntax to ensure that any extra objects go into the TOUSER schema:

imp system/manager FROMUSER=scott,adams TOUSER=ted,ted

Note that user Ted is listed twice.

Default: N
When specified as Y, instructs Import to import transportable tablespace metadata from an export file.

Default: none
When TRANSPORT_TABLESPACE is specified as Y, use this parameter to list the users who own the data in the transportable tablespace set.

USERID (username/password)
Default: undefined
Specifies the username/password (and optional connect string) of the user performing the import.

USERID can also be:

username/password AS SYSDBA or username/password@instance AS SYSDBA

Note that your operating system may require you to treat AS SYSDBA as a special string, requiringyou to enclose the entire string in quotation marks.

Optionally, you can specify the @connect_string clause for Net8.

Specifies the maximum number of bytes in an export file on each volume of tape. The VOLSIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits. See your Operating system-specific documentation for more information.

The VOLSIZE value can be specified as number followed by K (number of kilobytes). For example, VOLSIZE=2K is the same as VOLSIZE=2048. Similarly, M specifies megabytes (1024 * 1024) while G specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to get the final file size (VOLSIZE=2048b is the same as VOLSIZE=2048).

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 8i Topics