For the exact syntax of this clause, see the user's guide for your Oracle Net protocol. The following sections describe the globalization support behavior of Export and Import with respect to character set conversion of user data and data definition language DDL.
The Export utility always exports user data, including Unicode data, in the character sets of the Export server. Character sets are specified at database creation. If the character sets of the source database are different than the character sets of the import database, a single conversion is performed to automatically convert the data to the character sets of the Import server.
If the export character set has a different sorting order than the import character set, then tables that are partitioned on character columns may yield unpredictable results. For example, consider the following table definition, which is produced on a database having an ASCII character set:.
To obtain the desired results, the owner of partlist must repartition the table following the import. If the export file's character set is different than the import user session character set, then Import converts the character set to its user session character set.
Import can only perform this conversion for single-byte character sets. This means that for multibyte character sets, the import file's character set must be identical to the export file's character set. A final character set conversion may be performed if the target database's character set is different from the character set used by the import user session.
To minimize data loss due to character set conversions, ensure that the export database, the export user session, the import user session, and the import database all use the same character set. Some 8-bit characters can be lost that is, converted to 7-bit equivalents when you import an 8-bit character set export file. Most often, this is apparent when accented characters lose the accent mark. During character set 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. The three interrelated objects in a snapshot system are the master table, optional snapshot log, and the snapshot itself.
The tables master table, snapshot log table definition, and snapshot tables can be exported independently of one another. Snapshot logs can be exported only if you export the associated master table.
You can export snapshots using full database or user-mode export; you cannot use table-mode export. The snapshot log in a dump file is imported if the master table already exists for the database to which you are importing and it has a snapshot log. As a result, each ROWID snapshot's first attempt to do a fast refresh fails, generating an error indicating that a complete refresh is required.
After you have done a complete refresh, subsequent fast refreshes will work properly. In contrast, when a primary key snapshot log is exported, the values of the primary keys do retain their meaning upon import. Therefore, primary key snapshots can do a fast refresh after the import. A snapshot that has been restored from an export file has reverted to a previous state.
On import, the time of the last refresh is imported as part of the snapshot table definition. The function that calculates the next refresh time is also imported. 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.
When you restore a snapshot from an export file, you may encounter a problem under certain circumstances. Assume that a snapshot is refreshed at time A, exported at time B, and refreshed again at time C. Then, because of corruption or other problems, the snapshot needs to be restored by dropping the snapshot and importing it again.
The newly imported version has the last refresh time recorded as time A. However, log entries needed for a fast refresh may no longer exist. If the log entries do exist because they are needed for another snapshot that has yet to be refreshed , they are used, and the fast refresh completes successfully.
Otherwise, the fast refresh fails, generating an error that says a complete refresh is required. Snapshots and related items are exported with the schema name explicitly given in the DDL statements.
This does not apply to snapshot logs, which cannot be imported into a different schema. The transportable tablespace feature enables you to move a set of tablespaces from one Oracle database to another.
To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use Export and Import to move the database information metadata stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat binary files, such as the operating system copying facility, binary-mode FTP, or publishing on CD-ROMs.
Export and Import provide the following parameters to enable movement of transportable tablespace metadata. Oracle Database Administrator's Guide for details about managing transportable tablespaces. Oracle Database Concepts for an introduction to transportable tablespaces.
Read-only tablespaces can be exported. If you want read-only functionality, you must manually make the tablespace read-only after the import. You can drop a tablespace by redefining the objects to use different tablespaces before the import.
In many cases, you can drop a tablespace by doing a full database export, then creating a zero-block tablespace with the same name before logging off as the tablespace you want to drop. All objects from that tablespace will be imported into their owner's default tablespace with the exception of partitioned tables, type tables, and tables that contain LOB or VARRAY columns or index-only tables with overflow segments.
Import cannot determine which tablespace caused the error. Objects are not imported into the default tablespace if the tablespace does not exist, or you do not have the necessary quotas for your default tablespace. If a user's quota allows it, the user's tables are imported into the same tablespace from which they were exported. However, if the tablespace no longer exists or the user does not have the necessary quota, the system uses the default tablespace for that user as long as the table is unpartitioned, contains no LOB or VARRAY columns, is not a type table, and is not an index-only table with an overflow segment.
This scenario can be used to move a 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:. Set joe 's quota on tablespace A to zero. Also revoke all roles that might have such privileges or quotas. When you revoke a role, it does not have a cascade effect. Therefore, users who were granted other roles by joe will be unaffected. Give joe a quota on tablespace B and make it the default tablespace for joe.
Import joe 's tables. By default, Import puts joe 's tables into tablespace B. You can export and import tables with fine-grained access control policies enabled. When doing so, consider the following:. If a user without the correct privileges attempts to export a table with fine-grained access policies enabled, only those rows that the user has privileges to read will be exported.
If a user without the correct privileges attempts to import from an export file that contains tables with fine-grained access control policies, a warning message will be issued. Therefore, it is advisable for security reasons that the exporter and importer of such tables be the DBA. If fine-grained access control is enabled on a SELECT statement, then conventional path Export may not export the entire table, because fine-grained access may rewrite the query.
You can use instance affinity to associate jobs with instances in databases you plan to export and import. Be aware that there may be some compatibility issues if you are using a combination of releases.
Oracle Database Reference. Oracle Database Upgrade Guide. A database with many noncontiguous, small blocks of free space is said to be fragmented.
A fragmented database should be reorganized to make space available in contiguous, larger blocks. You can reduce fragmentation by performing a full database export and import as follows:. Delete the database. See your Oracle operating system-specific documentation for information about how to delete a database. 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, unless the table:.
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. Tables are exported with their current storage parameters. If you alter the storage parameters of existing tables prior to export, the tables are exported using those altered storage parameters. Note that LOB data might not reside in the same tablespace as the containing table.
If LOB data resides in a tablespace that does not exist at the time of import, or the user does not have the necessary quota in that tablespace, the table will not be imported. Because there can be multiple tablespace clauses, including one for the table, Import cannot determine which tablespace clause caused the error. Before using the Import utility to import data, you may want to create large tables with different storage parameters. By default at export time, storage parameters are adjusted to consolidate all data into its initial extent.
The material presented in this section is specific to the original Export utility. The following topics are discussed:. Data is read from disk into a buffer cache, and rows are transferred to the evaluating buffer.
The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file. Direct path Export is much faster than conventional path Export because data is read from disk into the buffer cache and rows are transferred directly to the Export client.
The evaluating buffer that is, the SQL command-processing layer is bypassed. The data is already in the format that Export expects, thus avoiding unnecessary data conversion. The data is transferred to the Export client, which then writes the data into the export file. The rest of this section discusses the following topics:. ORA snapshot too old; rollback segment number string with name " string " too small.
The following users are exempt from Virtual Private Database and Oracle Label Security enforcement regardless of the export mode, application, or utility used to extract data from the database:. This is a powerful privilege and should be carefully managed. Your exact performance gain depends upon the following factors:. An export file that is created using direct path Export will take the same amount of time to import as an export file created using conventional path Export.
To invoke a direct path Export, you must use either the command-line method or a parameter file. You cannot invoke a direct path Export using the interactive method. To extract metadata from a source database, Export uses queries that contain ordering clauses sort operations. For these queries to succeed, the user performing the export must be able to allocate sort segments.
For these sort segments to be allocated in a read-only database, the user's temporary tablespace should be set to point at a temporary, locally managed tablespace. The following sections describe points you should consider when you export particular database objects.
If transactions continue to access sequence numbers during an export, sequence numbers might be skipped. The best way to ensure that sequence numbers are not skipped is to ensure that the sequences are not accessed during the export. Sequence numbers can be skipped only when cached sequence numbers are in use. When a cache of sequence numbers has been allocated, they are available for use in the current database. The exported value is the next sequence number after the cached values.
Sequence numbers that are cached, but unused, are lost when the sequence is imported. On export, LONG datatypes are fetched in sections. However, enough memory must be available to hold all of the contents of each row, including the LONG data.
LONG columns can be up to 2 gigabytes in length. All data in a LOB column does not need to be held in memory at the same time. LOB data is loaded and unloaded in sections. The contents of foreign function libraries are not included in the export file. Instead, only the library specification name, location is included in full database mode and user-mode export. You must move the library's executable files and update the library specification if the database is moved to a new location.
If the data you are exporting contains offline locally managed tablespaces, Export will not be able to export the complete tablespace definition and will display an error message. You can still import the data; however, you must create the offline locally managed tablespaces before importing to prevent DDL commands that may reference the missing tablespaces from failing. Directory alias definitions are included only in a full database mode export.
To move a database to a new location, the database administrator must update the directory aliases to point to the new location. Directory aliases are not included in user-mode or table-mode export. Therefore, you must ensure that the directory alias has been created on the target system before the directory alias is used.
The export file does not hold the contents of external files referenced by BFILE columns or attributes. Instead, only the names and directory aliases for files are copied on Export and restored on Import. If you move the database to a location where the old directories cannot be used to access the included files, the database administrator DBA must move the directories containing the specified files to a new location where they can be accessed.
The contents of external tables are not included in the export file. Instead, only the table specification name, location is included in full database mode and user-mode export.
You must manually move the external data and update the table specification if the database is moved to a new location. In all Export modes, the Export utility includes information about object type definitions used by the tables being exported.
The information, including object name, object identifier, and object geometry, is needed to verify that the object type on the target system is consistent with the object instances contained in the export file. This ensures that the object types needed by a table are created with the same object identifier at import time. Note, however, that in table mode, user mode, and tablespace mode, the export file does not include a full object type definition needed by a table if the user running Export does not have execute access to the object type.
In this case, only enough information is written to verify that the type exists, with the same object identifier and the same geometry, on the Import target system. The user must ensure that the proper type definitions exist on the target system, either by working with the DBA to create them, or by importing them from full database mode or user-mode exports performed by the DBA.
It is important to perform a full database mode export regularly to preserve all object type definitions. Alternatively, if object type definitions from different schemas are used, the DBA should perform a user mode export of the appropriate set of users. For example, if table1 belonging to user scott contains a column on blake 's type type1, the DBA should perform a user mode export of both blake and scott to preserve the type definitions needed by the table.
Inner nested table data is exported whenever the outer containing table is exported. Although inner nested tables can be named, they cannot be exported individually. Queues are implemented on tables. The export and import of queues constitutes the export and import of the underlying queue tables and related dictionary tables. You can export and import queues only at queue table granularity. When you export a queue table, both the table definition information and queue data are exported.
Because the queue table data is exported as well as the table definition, the user is responsible for maintaining application-level data integrity when queue table data is imported. You should be cautious when exporting compiled objects that reference a name used as a synonym and as another object.
Exporting and importing these objects will force a recompilation that could result in changes to the object definitions. If the database in the preceding example were exported, the reference to emp in the trigger would refer to blake 's view rather than to scott 's table. The material in this section is specific to the original Import utility. If a row is rejected due to an integrity constraint violation or invalid data, Import displays a warning message but continues processing the rest of the table.
Some errors, such as "tablespace full," apply to all subsequent rows in the table. These errors cause Import to stop processing the current table and skip to the next table. A row error is generated if a row violates one of the integrity constraints in force on your system, including:. Row errors can also occur when the column definition for a table in a database is different from the column definition in the export file. The error is caused by data that is too long to fit into a new table's columns, by invalid datatypes, or by any other INSERT error.
Errors can occur for many reasons when you import database objects, as described in this section. When these errors occur, import of the current database object is discontinued. Import then attempts to continue with the next database object in the export file. If a database object to be imported already exists in the database, an object creation error occurs.
The current database object is not replaced. For tables, this behavior means that rows contained in the export file are not imported. The database object is not replaced. If the object is a table, rows are imported into it. Note that only object creation errors are ignored; all other errors such as operating system, database, and SQL errors are reported and processing may stop. If sequence numbers need to be reset to the value in an export file as part of an import, you should drop sequences.
If a sequence is not dropped before the import, it is not set to the value captured in the export file, because Import does not drop and re-create a sequence that already exists. Resource limitations can cause objects to be skipped. When you are importing tables, for example, resource errors can occur as a result of internal problems, or when a resource such as memory has been exhausted.
If a resource error occurs while you are importing a row, Import stops processing the current table and skips to the next table. If not, a rollback of the current table occurs before Import continues. Import provides you with the capability of delaying index creation and maintenance services until after completion of the import and insertion of exported data. Performing index creation, re-creation, or maintenance after Import completes is generally faster than updating the indexes for each row inserted by Import.
Index creation can be time consuming, and therefore can be done more efficiently after the import of all other objects has completed. The index-creation statements that would otherwise be issued by Import are instead stored in the specified file. This approach saves on index updates during the import of existing tables.
Delayed index maintenance may cause a violation of an existing unique integrity constraint supported by the index. For example, assume that partitioned table t with partitions p1 and p2 exists on the import target system. Assume that partition p1 contains a much larger amount of data in the existing table t , compared with the amount of data to be inserted by the export file expdat.
Assume that the reverse is true for p2. If statistics are requested at export time and analyzer statistics are available for a table, Export will include the ANALYZE statement used to recalculate the statistics for the table into the dump file. In most circumstances, Export will also write the precalculated optimizer statistics for tables, indexes, and columns to the dump file.
By default, Import will always use the precalculated statistics that are found in the export dump file. The Export utility flags certain precalculated statistics as questionable. The importer might want to import only unquestionable statistics, not precalculated statistics, in the following situations:. Character set translations between the dump file and the import client and the import database could potentially change collating sequences that are implicit in the precalculated statistics.
For example, the statistics gathered from a fragmented database may not be relevant when the data is imported in a compressed form. This section discusses some ways to possibly improve the performance of an import operation. The information is categorized as follows:. The following suggestions about system-level options may help to improve performance of an import operation:. Create and use one large rollback segment and take all other rollback segments offline. Generally a rollback segment that is one half the size of the largest table being imported should be big enough.
It can also help if the rollback segment is created with the minimum number of two extents, of equal size. This will reduce the overhead of creating and managing archive logs. Create several large redo files and take any small redo log files offline.
This will result in fewer log switches being made. If possible, have the rollback segment, table data, and redo log files all on separate disks. If possible, do not run any other jobs at the same time that may compete with the import operation for system resources.
This change is permanent, so be sure to carefully consider all effects it will have before making it. The following suggestions about settings in your initialization parameter file may help to improve performance of an import operation.
This number is in operating system blocks on most UNIX systems. This reduces checkpoints to a minimum at log switching time. The amount you increase it depends on other activity taking place on the system and on the amount of free memory available.
If the system begins swapping and paging, the value is probably set too high. The following suggestions about usage of import options may help to improve performance.
Be sure to also read the individual descriptions of all the available options in Import Parameters. This causes Import to commit after each object table , not after each buffer. This is why one large rollback segment is needed. Because rollback segments will be deprecated in future releases, Oracle recommends that you use automatic undo management instead. A larger size reduces the number of times that the export file has to be accessed for data. Several megabytes is usually enough. Be sure to check your system for excessive paging and swapping activity, which can indicate that the buffer size is too large.
Eliminating indexes significantly reduces total import time. This is because LOB data requires special consideration during an import because the LOB locator has a primary key that cannot be explicitly dropped or ignored during an import.
There are no specific parameters that will improve performance during an import of large amounts of LONG data, although some of the more general tuning suggestions made in this section may help overall performance. The following sections describe restrictions and points you should consider when you import particular database objects.
The Oracle database assigns object identifiers to uniquely identify object types, object tables, and rows in object tables. These object identifiers are preserved by Import. To do this, Import compares the types's unique identifier TOID with the identifier stored in the export file. If those match, Import then compares the type's unique hashcode with that stored in the export file. Import will not import table rows if the TOIDs or hashcodes do not match.
Import uses the following criteria to decide how to handle object types, object tables, and rows in object tables:. Rows are imported into the object table. Be sure you do not remove any Oracle home files. Uninstalling the Complete Instant Client. Previous Next JavaScript must be enabled to correctly display this content. The following topics are discussed:. What is the Tools Instant Client? Configure the Instant Client.
Connect to a remote instance with the utility you want to run. Instant Client packages should never be installed on an Oracle home. Unzip the two packages into the new directory.
The wrc tool is listed here for completeness sake, but it is not covered by the information in this appendix. Reset environment variables and remove tnsnames. Remove tnsnames. Original classic export executable. None of the options in the poll matched what I'm trying to achieve. View results. Free Questionnaires. Hashes [? These are some of the error messages that can appear related to imp. We are sorry for the inconvenience.
End Program - imp. This program is not responding. What will you do with IMP. To help other users, please let us know what you will do with IMP. Comments Please share with the other users what you think about this file. If this generated file is placed in the Documents and Settings directory, it causes problems for Microsoft Internet Explorer and Windows Explorer.
When the computer is rebooted, Windows looks in the Documents and Settings directory before the system32 directory to find a copy of shdocvw. It will use the copy it finds in Documents and Settings and attempt to load the managed wrappers. Internet Explorer and Windows Explorer will not function properly because they rely on the rendering engine in the version of shdocvw.
If this problem occurs, delete the copy of shdocvw. NET assembly for use in application development can also cause problems. In this case, your application will load both the system version of shdocvw.
When the user clicks Open , the Web page will be opened in Internet Explorer. This occurs only with computers that are running Internet Explorer version 6 or earlier.
To prevent this problem, use the managed WebBrowser control or use Visual Studio to generate the managed shdocvw. The following command generates MediaPlayer. Skip to main content.
0コメント