Oracle7 Server Utilities User's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Import only reads export files created by Export. For information on how to export a database, see Chapter 1, "Export." To load data from other operating system files, see the discussion of SQL*Loader in Part II of this manual.
This chapter discusses the following topics:
Figure 2 - 1. Importing an Export File
For example, if the EMP table has a referential integrity constraint on the DEPT table and the EMP table is imported first, then all EMP rows that reference departments that have not yet been imported into DEPT would be rejected provided that the constraints are enabled.
When data is imported into existing tables, however, the order of import can still produce referential integrity failures. In the situation given above, if the EMP table already existed and referential integrity constraints were in force, many rows could be rejected.
A similar situation occurs when a referential integrity constraint on a table references itself. For example, if SCOTT's manager in the EMP table is DRAKE, and DRAKE's row has not yet been loaded, then SCOTT's row will fail--even though it would be valid at the end of the import.
Suggestion: For the reasons mentioned above, it is a good idea to disable referential constraints when importing into an existing table. You can then re-enable the constraints after the import is completed.
If the tablespace no longer exists, or the user does not have sufficient quota in the tablespace, the system uses the default tablespace for that user. If the user does not have sufficient quota in the default tablespace, the user's tables are not imported. (See "Reorganizing Tablespaces" to see how you can use this to your advantage.)
You can do an import even if you did not create the export file. However, if the export file is a full database export (created by someone using the EXP_FULL_DATABASE role), then it will only be possible to import that file if you have the IMP_FULL_DATABASE role as well.
Additional Information: For information on the LOG specification, see page 2 - 23. Also see your operating system-specific Oracle7 Server documentation for information on redirecting output.
When an import completes without errors, the message "Import terminated successfully without warnings" is issued. If one or more non-fatal errors occurred, but Import was able to continue to completion, then the message "Import terminated successfully with warnings" occurs. If a fatal error occurs, then Import ends immediately with the message "Import terminated unsuccessfully".
Additional Information: Specific messages are documented in the Oracle7 Server Messages manual and in your operating system-specific Oracle7 Server documentation.
Messages that are specific to Trusted Oracle7 Server are documented in the Trusted Oracle7 Server Administrator's Guide.
See Chapter 5, "Maintaining Data Integrity", in the Oracle7 Server Application Developer's Guide and Chapter 7, "Data Integrity", in the Oracle7 Server Concepts manual for more information on integrity constraints.
IMP-00020 column (size num) in export file too large for column
buffer (size num)
then you can gradually increase the insert buffer size (for example, by 10,000 bytes at a time) up to 66,000 or greater.
If IGNORE=N (the default), the error is reported, and Import continues with the next object. The current object is not replaced. For tables, this behavior means that rows contained in the export file are not imported.
If IGNORE=Y, object creation errors are not reported. Although the object is still not replaced, if the object is a table, rows are imported into it. Note that only object creation errors will be ignored, all other errors (operating system, database, SQL, etc.) will be reported and processing may stop.
Warning: Specifying IGNORE=Y can cause duplicate rows to be entered into a table unless one or more columns of the table are specified with the UNIQUE integrity constraint. This could occur, for example, if Import were run twice.
If a resource error occurs while importing a row, Import stops processing the current table and skips to the next table. If you have specified COMMIT=Y, Import will commit the partial import of the current table. If not, a rollback of the current table will occur before Import continues. (See the description of COMMIT for information about the COMMIT parameter.)
Object | Privileges | Privilege Type | |
clusters | CREATE CLUSTER | system | |
And: | tablespace quota, or | ||
UNLIMITED TABLESPACE | system | ||
database links | CREATE DATABASE LINK | system | |
CREATE SESSION on remote db | system | ||
database triggers | CREATE TRIGGER | system | |
indexes | CREATE INDEX | system | |
And: | tablespace quota, or | ||
UNLIMITED TABLESPACE | system | ||
integrity constraints | ALTER TABLE | object | |
packages | CREATE PROCEDURE | system | |
private synonyms | CREATE SYNONYM | system | |
sequences | CREATE SEQUENCE | system | |
snapshots | CREATE SNAPSHOT | system | |
stored functions | CREATE PROCEDURE | system | |
stored procedures | CREATE PROCEDURE | system | |
table data | INSERT TABLE | object | |
table definitions | CREATE TABLE | system | |
And: | tablespace quota, or | ||
UNLIMITED TABLESPACE | system | ||
views | CREATE VIEW | system | |
And: | SELECT on the base table, or | object | |
SELECT ANY TABLE | system | ||
Notes:
Grant | Conditions |
object privileges | Object must exist in the user's schema, or user must have the object privileges with the WITH GRANT OPTION. |
system privileges | User must have system privileges as well as the WITH ADMIN OPTION. |
Recompilation of a local procedure, function, or package does not occur until it is invoked or until the COMPILE ALL command is used to recompile all marked procedures, functions, and packages. For more information, see the Oracle7 Server Application Developer's Guide for more information about the COMPILE ALL command.
If a local procedure that has not been recompiled is invoked by a remote package or procedure it is recompiled then. However, the timestamp of the local procedure then changes, causing an error in the remote procedure that called it.
Remote procedures are not recompiled at import time; while this functionality prevents unnecessary recompilation, it does mean that you will have to ensure that remote procedures that have been imported are recompiled.
To avoid the refresh error, do a complete refresh after importing a snapshot log. After doing a complete refresh, subsequent fast refreshes will work properly.
Each refresh leaves a signature. A fast refresh uses the log entries that date from the time of that signature to bring the snapshot up to date. When the fast refresh is complete, the signature is deleted and a new signature is created. Any log entries that are not needed to refresh other snapshots are also deleted (all log entries with times before the earliest remaining signature).
Subsequent fast refreshes update from time B, not from time A. So changes logged in the master table between time A and B are not reflected in the snapshot.
This situation can only occur if IGNORE=Y is specified for the import. (Otherwise, the snapshot rows are not imported.) As a result, the situation is undetectable -- the IGNORE setting prevents an object creating error from being issued.
Suggestion: To avoid such problems, export backup copies of snapshots immediately after doing a fast refresh. If out of date backups must be imported, do a complete refresh at the earliest opportunity.
Warning: The first snapshot to do a fast refresh recognizes the signature and gets the expected results. When a fast refresh is performed for the second snapshot the signature will be missing, causing an error because a complete refresh is required. After the complete refresh is performed, the second snapshot will have its own signature and subsequent fast refreshes will work properly.
During the conversion, any characters in the export file that have no equivalent in the target character set are replaced with a default character. (The default character is defined by the target character set.) To guarantee 100% conversion, the target character set should be a superset or equivalent of the source character set.
For multi-byte character sets, conversion is only performed if the length of the character string cannot change as a result of the conversion.
For more information, refer to the National Language Support section of the Oracle7 Server Reference.
Table
This mode allows you to import specified tables in your schema, rather than all your tables. A privileged user can qualify the tables by specifying the schema that contains them. The default is to import all tables in the schema of the user doing the import.
This mode allows you to import all objects that belong to you (such as tables, data, grants, and indexes). A privileged user importing in user mode can import all objects in the schemas of a specified set of users.
Only users with the IMP_FULL_DATABASE role can import in this mode. All objects in the export file are imported.
To select table, user, or full database mode, specify TABLES=tablelist, FROMUSER=userlist, or FULL=Y, respectively. A user with the IMP_FULL_DATABASE role must specify one of these options, or else an error results. If a user without the IMP_FULL_DATABASE role fails to specify one of these options, then a user-level import is performed.
Table 2 - 3 shows objects that are imported in each mode and the order in which they are imported.
CATEXP.SQL only needs to be run once. Once run, it does not need to be run again before future imports. CATEXP.SQL performs the following operations to prepare the database for Import:
If you omit username/password, Import will prompt for it.
Figure 2 - 2. Import Help Screen
For example:
IMP PARFILE=filename
IMP username/password PARFILE=filename
The syntax for parameter file specifications is:
KEYWORD=value
or
KEYWORD=(value)
or
KEYWORD=(value1, value2, ...)
The following is an example of a partial parameter file listing:
FULL=Y
FILE=DBA.DMP
GRANTS=Y
INDEXES=Y
Additional Information: The maximum size of the parameter file may be limited and operating system file naming conventions will apply. See your Oracle operating system-specific documentation for more information.
Similarly, in a parameter file, if a table has a # sign in the name, the rest of the line is interpreted as a comment unless the table name is enclosed in quotation marks.
Additional Information: Some operating systems require single vs. double quotes. See your Oracle operating system-specific documentation.
For example, if a parameter file contains the line
TABLES=(EMP#, DEPT, MYDATA)
then nothing after EMP# is seen as input by Import. As a result, DEPT and MYDATA are not imported.
The following modification fixes the problem:
TABLES=("EMP#", DEPT, MYDATA)
Attention: When the name is specified in quotation marks, it is case-sensitive. The name must then exactly match the table name stored in the database. By default, database names are stored as uppercase.
USERID | RECORDLENGTH |
BUFFER | INCTYPE |
FILE | COMMIT |
SHOW | HELP |
IGNORE | LOG |
GRANTS | DESTROY |
INDEXES | INDEXFILE |
ROWS | CHARSET |
FULL | FEEDBACK |
FROMUSER | MLS* |
TOUSER | MLS_LISTLABELS* |
TABLES | MLS_MAPFILE* |
*Trusted Oracle7 Server parameter. | |
Default: operating system dependent
The BUFFER (buffer size) parameter determines the number of rows in the array inserted by Import. The following formula gives a rough approximation of the buffer size that inserts a given array of rows:
buffer_size = rows_in_array * maximum_row_size
The size in bytes of the buffer through which data rows are transferred.
Additional Information: See your operating Oracle system-specific documentation to determine the default value for this parameter.
Release 6 export files only
Specifies the NLS character set used in the export file.
Use of this parameter is not recommended. It is only provided for compatibility with previous versions. Eventually, it will no longer be supported.
If you are using Oracle7 Server's Export, 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.
Valid values for CHARSET are contained in the V$NLS_PARAMETERS view. Consult the Oracle7 Server Reference for details.
COMMIT
Specifies whether Import should commit after each array insert. By default, Import commits after loading each object and Import will perform a rollback when an error occurs before continuing with the next object.
Specifying COMMIT=Y prevents rollback segments from growing inordinately large and improves the performance of large imports. Enabling this parameter is advisable if the table has a uniqueness constraint. If the import is restarted, any rows that have already been imported will be rejected with a non-fatal error. Note that, if there is no uniqueness constraint, enabling this parameter could produce duplicate rows when re-importing the data.
DESTROY
Specifies whether the existing data files making up the database should be reused. That is, the DESTROY option 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. Attempting to create a second database on the same machine (for testing or other purposes) has the undesirable effect of overwriting the original database's data files when creating the tablespace. 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 importing 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 importing 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 that, if you have pre-created your tablespace, you should specify DESTROY=N or your pre-created tablespace will be lost.
FEEDBACK
Default: 0 (zero)
If set to other than zero, specifies that Import should display a progress meter in the form of a dot for x number of rows imported. For example, were you to specify FEEDBACK=10, import would display a dot each time 10 rows had been imported. The FEEDBACK value applies to all tables being imported, it cannot be set on a per table basis.
FILE
The name of the Export file to import.
FROMUSER
A list of schemas containing objects to import. The default is a user mode import. That is, all objects for the current user are imported. (If the TABLES parameter is also specified, then 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). See page 1 - 6 for the list of objects that are imported in user mode and 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, then his objects are imported into the importer's schema -- in this case, the system manager's. Otherwise, the objects are installed in SCOTT's schema. If a list of schemas is given, each schema can be specified only once. Duplicate schema names are ignored.
Note: Specifying FROMUSER=SYSTEM does not import system objects. It imports only those objects that belong to user SYSTEM.
To import system objects (for example, user definitions and tablespaces), you must import from a full export file specifying FULL=Y.
FULL
Specifies whether to import the entire export file or not. This parameter can be specified only by a user with the IMP_FULL_DATABASE role enabled.
GRANTS
Specifies whether to import grants or not.
HELP
Displays a description of import parameters.
IGNORE
Specifies how object creation errors should be handled. Specifying IGNORE=Y causes Import to overlook object creation errors when attempting to create database objects. For objects other than tables, 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 will be ignored, operating system, database, SQL, etc. errors will not be 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). This can be prevented for certain objects, for example, constraints, by doing an export in table mode with the CONSTRAINTS=NO option. Note that, if a full export is done with the CONSTRAINTS option set to NO, no constraints for any tables will be exported. See page 1 - 12 for information about which objects you can prevent from being exported.
Warning: When importing into existing tables, if no column in the table is uniquely indexed, then 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
Specifies the type of incremental import. Valid values are SYSTEM and RESTORE. See the section called "Importing Incremental, Cumulative and Complete Export Files" later in this chapter for more information.
INDEXES
Specifies whether to import indexes or not.
INDEXFILE
Specifies a file to receive index-creation commands.
When this parameter is specified, index-creation commands 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. This is the most efficient way to create indexes for new tables--even if the index file is not edited. 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: As of release 7.1, the commented CREATE TABLE statement in the indexfile no longer includes primary/unique key clauses.
Perform the following steps to make use of this feature:
Specifies a file to receive informational and error messages. If specified, all information written to the terminal display is also written to the log.
MLS
Used when importing data into a secure database. Specifies that the export file contains Multi-Level Security (MLS) labels. For more information on this parameter, see the Trusted Oracle7 Server Administrator's Guide.
MLS_LISTLABELS
When specified, lists the MLS labels in the export file. For more information on this parameter, see the Trusted Oracle7 Server Administrator's Guide.
MLS_MAPFILE
When specified, maps the MLS labels in the export file to the labels used in the target database for the import. For more information, see the Trusted Oracle7 Server Administrator's Guide .
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 your Oracle operating system-specific documentation to determine the default value for this parameter.
ROWS
Specifies whether to import the rows of table data or not.
SHOW
When specified, the contents of the export file will be listed to the display and not imported. SHOW is not typically used with other parameters.
TABLES
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 . The number of tables that can be specified at the same time is dependent on command line limits and buffer settings. 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 should be:
IMP system/manager FROMUSER=(scott,jones) TABLES=(accts,emp,dept)
If user SCOTT does not exist in the current database, then his tables are imported into the importer's schema -- in the above example, the system/manager's. Otherwise, the tables and associated objects are installed in SCOTT's schema.
TOUSER
Specifies a list of usernames to whose schemas data 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, then the schema names are paired. For example,
IMP system/manager FROMUSER=(scott,fred) TOUSER=(joe, ted)
imports SCOTT's objects into JOE's schema, and FRED's objects into TED's schema.
USERID
The username/password of the user performing the import.
You may not see all the prompts in a given Import session because some prompts depend on your responses to other prompts. Some prompts show a default answer; if the default is acceptable, press [RETURN]. To end your input, enter a period (.) followed by [RETURN].
If you have not specified a username/password on the command line, Import first prompts you for this information. Then the following prompts are displayed:
Specify the name of the export file to be imported. You do not have to be the Oracle user who exported the file. However, you do need to have current access to the file.
Specify the buffer size for your tables. You may need to increase the buffer size for tables that have LONG columns. The buffer must be at least as large as the longest row.
When specified, allows you to see what is in the export file (for example, a particular table exported to the export file). Equivalent to the command-line parameter SHOW.
If you specify Y, Import will not import the rows but will prompt you whether to display the entire export file or only portions of it. Import can also display the SQL statements contained in the export file in the order they would be executed on import.
Import will generate an error if an object to be imported already exists. This prompt allows you to specify how you want the errors to be handled.
If you accept the default (N) Import will issue an error message each time it encounters a table that already exists and will not import that table's data.
Specifying Y has two effects on the import process. If Import encounters non-table objects, it skips that object and does not issue an error message. If Import encounters a pre-existing table, it imports the rows into the table.
For example, you may want to import data into a pre-existing table -- perhaps because you want to use unique storage parameters, or because you have pre-created the table in a cluster. You can do so by specifying Y at this prompt. The rows of data will be imported into the pre-existing table.
By default, any grants that were exported will be imported. If the export was done in user mode, only first-level grants (those granted by the owner) are in the export file. If the export was done in full database mode, all grants are in the export file, including lower-level grants (those granted by users given a privilege with the WITH GRANT option). Specifying N will prevent any grants from being imported.
By default, Import will import the data rows. However, specifying N, causes Import to execute such SQL statements as CREATE TABLE or CREATE VIEW, but does not insert any data rows. Indexes and grants are created.
By default, Import will import the entire export file. By specifying N at this prompt, you can choose specific tables for import. If you choose to import specific tables, you will need to specify the tables at the next prompt.
If there is not a previous table, it defaults to the importer's schema. In the following example, schema defaults to SCOTT for table DEPT.
Command Line Method
imp system/manager file=dba.dmp fromuser=scott tables="(emp,dept)"
Interactive Method
imp system/manager
...
Import file: expdat.dmp >
Enter insert buffer size (minimum is 4096) 30720>
Export file created by EXPORT:V07.01.03
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): yes >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): yes > no
Username: scott
Enter table names. Null list means all tables for user
Enter table name or . if done: dept
Enter table name or . if done: emp
Enter table name or . if done: .
Import Messages
Export file created by EXPORT:V07.01.03
Warning: the objects were exported by SCOTT, not by you
. importing SCOTT's objects into SYSTEM
. . importing table "DEPT" 7 rows imported
. . importing table "EMP" 22 rows imported
Import terminated successfully without warnings.
Command Line Method
imp adams/ez4me file=scott.dmp tables="(emp,dept)"
Interactive Method
imp adams/ez4me
...
Import file: expdat.dmp > scott.dmp
Enter insert buffer size (minimum is 4096) 30720>
Export file created by EXPORT:V07.01.03
Warning: the objects were exported by SCOTT, not by you
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): yes >
Import grants (yes/no): yes > n
Import table data (yes/no): yes >
Import entire export file (yes/no): yes > n
Username: scott
Enter table names. Null list means all tables for user
Enter table name or . if done: emp
Enter table name or . if done: dept
Enter table name or . if done: .
Import Messages
Export file created by EXPORT:V07.01.03
Warning: the objects were exported by SCOTT, not by you
. importing SCOTT's objects into ADAMS
. . importing table "EMP" 22 rows imported
. . importing table "DEPT" 7 rows imported
Import terminated successfully without warnings.
Command Line Method
imp system/manager file=scott.dmp fromuser=scott touser=rosemary
tables=(*) [use an asterisk to specify all tables]
Import Messages
Export created by Oracle version EXPORT:V7.2
.. importing table "BONUS" 32 rows imported
.. importing table "SALGRADE" 10 rows imported
Since imports from incremental export files are dependent on the method used to export the data, you should also read the section called "Incremental, Cumulative and Complete Exports".
It is important to note that, since importing an incremental export file imports new versions of existing objects, existing objects are dropped before new ones are imported. This behavior differs from a normal import. During a normal import, objects are not dropped and an error is generated if the object already exists.
Note: Imports from incremental export files can only be applied to an entire database. There is no user-mode or table-mode for such an import. Any user with the BECOME_USER system privilege can do an import from an incremental export file. This privilege is contained in the IMP_FULL_DATABASE role.
SYSTEM
Imports the most recent version of system objects (except those owned by SYS) using the most recent incremental export file. A SYSTEM import does not import user data or objects.
RESTORE
Imports all user database objects and data that have changed since the last export using export files in chronological order.
Note: To restore a set of objects, you must first import the most recent incremental export file to import the system objects (i.e. specify INCTYPE=SYSTEM for the export). Then you must import the export files in chronological order, based on their export time (i.e. specify INCTYPE=RESTORE for the import).
IMP system/manager INCTYPE=SYSTEM FULL=Y FILE=I3
IMP system/manager INCTYPE=RESTORE FULL=Y FILE=X1
IMP system/manager INCTYPE=RESTORE FULL=Y FILE=C1
IMP system/manager INCTYPE=RESTORE FULL=Y FILE=C2
IMP system/manager INCTYPE=RESTORE FULL=Y FILE=I1
IMP system/manager INCTYPE=RESTORE FULL=Y FILE=I2
IMP system/manager INCTYPE=RESTORE FULL=Y FILE=I3
Notes:
These errors can still occur when data is loaded into existing tables, however. For example, if table EMP has a referential integrity constraint on the MGR column that verifies the manager number exists in EMP, then a perfectly legitimate employee row might fail the referential integrity constraint if the manager's row has not yet been imported. When such an error occurs, Import generates an error message, bypasses the failed row, and continues importing other rows in the table. You can disable constraints manually to avoid this.
Similarly, a referential check from table AEMP into table BDEPT using DEPTNO would cause rows to fail, because the tables are loaded in alphabetic order, and rows from AEMP would be loaded before the corresponding rows in BDEPT.
To prevent errors like these, it is a good idea to disable referential integrity constraints when importing data into existing tables.
To do so, do several imports from an export file instead of one. First, import tables that are the targets of referential checks, before importing the tables that reference them. Provided tables do not reference each other in circular fashion, and provided a table does not reference itself, this option works.
STATISTICS=COMPUTE
STATISTICS=ESTIMATE
When one of these options is specified during the export, all database objects that have had ANALYZE applied to them are exported with the commands necessary to generate the appropriate statistics (estimated or computed) on import.
Note: Generation of statistics is limited to those objects that already had them before export. So statistics are not suddenly generated on every object in the database.
If your installation generally uses either estimated or computed statistics, then it is a good idea to include one of these commands whenever you use Export. The cost during export is negligible -- statistics are not recorded in the export file, only a command to generate them. See Chapter 13 of the Oracle7 Server Concepts manual for more information about the optimizer.
By issuing this command during export, you ensure that the appropriate statistics are gathered when the data is imported. If your export file was created without this command, or if you have changed your method of collecting statistics, use Import's INDEXFILE option to generate a list of imported objects. Then edit that list to produce a series of ANALYZE commands on them. (For more information, see the INDEXFILE parameter ).
For example, running Export locally, you can write data from a remote Oracle database into a local export file. Running Import locally, you can read data into a remote Oracle database.
To use Export or Import with SQL*Net, include the @ connect_string clause when entering the EXP or IMP command. For the exact syntax of this clause, see the user's guide for your SQL*Net protocol. For more information on SQL*Net, see Understanding SQL*Net. If you are using Oracle Names see the Oracle Names Administrator's Guide.
If the user is unable to access the default tablespace, then the tables cannot be imported. This scenario can be used to move user's tables from one tablespace to another.
For example, you need to move JOE's tables from tablespace A to tablespace B after a full database export. Follow these steps:
IGNORE=Y
at the command line or in the parameter file or, if using Import's interactive mode, respond "Yes" to the prompt:
Ignore create errors due to object existence (Y/N) : Y >
Note: The storage parameter OPTIMAL is not exported for rollback segments and therefore cannot be imported at any time.
If the tablespace already exists in the target database and is already read-only, then you must make it read/write before the import.
To avoid this unwanted conversion, you can set the NLS_LANG operating system environment variable to be that of the export file character set.
When importing an Oracle Version 6 export file with a character set different from that of the native operating system, you must set the CHARSET import parameter to specify the character set of the export file.
If you pre-create tables from prepared scripts, then the CHAR columns are created as Oracle fixed-width columns instead of the desired VARCHAR2 (variable-width) columns. If you modify the scripts to create VARCHAR2 columns, however, the the columns will be created.
ORA-1401: inserted value too large for column
Oracle Version 6 did not check the columns in a CREATE TABLE statement to be sure they were long enough to hold their DEFAULT values so these tables could be imported into a Version 6 database. The Oracle7 Server does make this check, however. As a result, tables that could be imported into a Version 6 database may not import into Oracle7.
If the DEFAULT is a value returned by a function, then the column must be large enough to hold the maximum value that can be returned by that function. Otherwise, the CREATE TABLE statement recorded in the export file produces an error on import.
Note: The maximum value of the USER function increased in the Oracle7 Server, so columns with a default of USER may not be long enough. To determine the maximum size that the USER function will return, execute the following SQL command:
DESCRIBE user_sys_privs
The length shown for the USERNAME column is the maximum length returned by the USER function.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |