| Oracle® Database Utilities 10g Release 1 (10.1) Part Number B10825-01 |
|
|
View PDF |
| Oracle® Database Utilities 10g Release 1 (10.1) Part Number B10825-01 |
|
|
View PDF |
T
his chapter describes how to use the original Export and Import utilities, invoked with the exp and imp com
mand, respectively. These are called the original Export and Import utilities to differentiate them from the new Oracle Data Pump Exp
ort and Import utilities available as of Oracle Database 10g. These new utilities are invoked with the expdp an
d impdp commands, respectively. In general, Oracle recommends that you use the new Data Pump Export and Import utilities
because they support all Oracle Database 10g features. Original Export and Import do not support all Oracle Database 10
g features.
However, you should still use the original Export and Import utilities in the following situations:
You want to import files that were created using the original Export utility (exp).
You want to export files that will be imported using the original Import utility (imp). An example of this
would be if you wanted to export data from Oracle Database 10g and then import it into an earlier database release.
This chapter discusses the following topics:
Using Export and Import to Move a Database Between Platforms
< a href="#i1021659">Warning, Error, and Completion Messages
The E xport and Import utilities provide a simple way for you to transfer data objects between Oracle databases, even if they reside on pla tforms with different hardware and software configurations.
When you run Export against an Oracle database, objects (such as t ables) are extracted, followed by their related objects (such as indexes, comments, and grants), if any. The extracted data is writte n to an export dump file. The Import utility reads the object definitions and table data from the dump file.
An export file is an Oracle binary-format dump file that is typically located on disk or tape. The dump files can be transferred using FTP or physical ly transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on systems not connected through a network. The files can also be used as backups in addition to normal backup pr ocedures.
Export dump files can only be read by the Oracle Import utility. The version of the Import utility cannot be earlier than the version of the Export utility used to create the dump file.
You can also display the contents of an export file with
out actually performing an import. To do this, use the Import SHOW parameter. See SHOW for more
information.
To load data from ASCII fixed-format or delimited files, use the SQL*Loader utility.
|
See Also:
|
Before you begin using Export and Import, be sure you take care of the following items (desc ribed in detail in the following sections):
Run the catexp.sql or catalog.sql
script
Ensure there is sufficient disk or tape storage to write the export file
Verify that you have the required access privileges
To use Export and Import, you must run the script catexp.sql or catalog.sql (which runs
catexp.sql) after the database has been created or migrated to Oracle Database 10g.
The catexp.sql or catalog.sql script needs to
be run only once on a database. The script performs the following tasks to prepare the database for export and import operations:
Creates the necessary export and import views in the data dictionary
Creates the EXP_FULL_DATABASE role
Assigns all necessary privileges to the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles
Assigns EXP_FULL_DATABASE and IMP_FULL_DATABASE to the DBA role
Records the version of catexp.
sql that has been installed
Before you run Export, ensure that there is sufficient disk or tape storage space to write the export file. If there is not enoug h space, Export terminates with a write-failure error.
You can use table sizes to estimate the maximum space needed. You can f
ind table sizes in the USER_SEGMENTS view of the Oracle data dictionary. The fo
llowing query displays disk usage for all tables: <
/p>
SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_TYPE='TABLE';
The result of the query do
es not include disk space used for data stored in LOB (large object) or VARRAY columns or in partitioned tables.
To use Export and Impo
rt, you must have the CREATE SESSION privilege on an Oracle database. This privilege belongs to the CONNECT
role established during database creation. To export tables owned by another user, you must have the EXP_FULL_DATABASE
role enabled. This role is granted to all database administrators (DBAs).
If you do n
ot have the system privileges contained in the EXP_FULL_DATABASE role, you cann
ot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you cr
eated a synonym for it.
The following schema names are reserved and will not be proce ssed by Export:
ORDSYS
MDSYS
CTXSYS
ORDPLUGINS
LBACSYS
You can perform an import operation even if you did not create the export file. However, keep in mind that if the exp
ort file was created by a user with the EXP_FULL_DATABASE role, then you must have the IMP_FULL_DATABASE ro
le to import it. Both of these roles are typically assigned to database administrators (DBAs).
You can invoke Export and Import, an d specify parameters by using any of the following methods:
Command-line entries
Parameter files
Interactive mode
Before you use on e of these methods, be sure to read the descriptions of the available parameters. See Export Parameters and < a href="#i1021478">Import Parameters.
SYSDBA is used internally and has specialized functions; its behavior is not the sam
e as for generalized users. Therefore, you should not typically need to invoke Export or Import as SYSDBA, except in the
following situations:
At the request of Oracle technical support
When impo rting a transportable tablespace set
To invoke Export or Import as SYSDBA, use the following syntax (su
bstitute exp for imp if you are using Export). Add any desired parameters or parameter filenames:
imp \'username/password AS SYSDBA\'
Optionally, you could also specify an instance name:
imp \'username/password@instance AS SYSDBA\'
If either the username or password is omitted, you will be prompted you for it.
This example shows the entire connect string enclosed in
quotation marks and backslashes. This is because the string, AS SYSDBA, contains a blank, a situation for which most op
erating systems require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some opera
ting systems also require that quotation marks on the command line be preceded by an escape character. In this example, backslashes a
re used as the escape character. If the backslashes were not present, the command-line parser that Export and Import use would not un
derstand the quotation marks and would remove them.
You can specify all valid parameters and their values from the command l ine using the following syntax:
exp username/password PARAMETER=value
or< /p>
exp username/password PARAMETER=(value1,value2,...,valuen)
The number of
parameters cannot exceed the maximum length of a command line on the system. Note that the examples could use imp to inv
oke Import rather than exp to invoke Export.
The information in this section
applies to both Export and Import, but the examples show use of the Export command, exp.
You can specify all vali d parameters and their values in a parameter file. Storing the parameters in a file allows them to be easily modified or reused, and is the recommended method for invoking Export. If you use different parameters for different databases, you can have multiple paramet er files.
Create the parameter file using any flat file text editor. The command-line option PARFILE=fi
lename tells Export to read the parameters from the specified file rather than from the command line. For example:
exp PARFILE=filename exp username/password PARFILE=filename
T
he first example does not specify the username/password on the command line
to illustrate that you can specify them in the parameter file, although, for security reasons, this is not recommended.
The sy ntax for parameter file specifications is one of the following:
PARAMETER=value PARAMETER=(value) PARAMETER=(value1, value2, ...)
The following example shows a partial parameter file listing:
< pre xml:space="preserve">FULL=y FILE=dba.dmp GRANTS=y INDEXES=y CONSISTENT=yYou can add comments to the parameter file by preceding them with the pound (#) sign. Export i gnores all characters to the right of the pound (#) sign.
You can specify a parameter file at the same time that you are entering parameters on the command line. In fact, you ca
n specify the same parameter in both places. The position of the PARFILE parameter and other parameters on the command l
ine determines which parameters take precedence. For example, assume the parameter file params.dat contains the paramete
r INDEXES=y and Export is invoked with the following line:
exp username/password PARFILE=params.dat INDEXES=n
In this case, because INDEXES=n occurs after PARFILE=params.dat<
/code>, INDEXES=n overrides the value of the INDEXES parameter in the parameter file.
|
See Also:
|
If you prefer to be prompted for the value of each parameter, you can use the following
syntax to start Export (or Import, if you specify imp) in interactive mode:
exp username/password
Commonly used parameters are displayed with a request for you t o enter a value. The command-line interactive method does not provide prompts for all functionality and is provided only for backward compatibility. If you want to use an interactive interface, Oracle recommends that you use the Oracle Enterprise Manager Export or I mport Wizard.
If you do not specify a username/password combination on the command
line, then you are prompted for this information.
Keep in mind the fol lowing points when you use the interactive method:
In user mode, Export prompts for all usernames to be included in the export before exporting any data. To indicate the end of the user list and begin the current Export session, press En ter.
In table mode, if you do not specify a schema prefix, Export defaults to the exporter's schema or t he schema containing the last table exported in the current session.
For example, if beth is a privileged user ex
porting in table mode, Export assumes that all tables are in the beth schema until another schema is specified. Only a p
rivileged user (someone with the EXP_FULL_DATABASE role) can export tables in another user's schema.
If you specify a null table list to the prompt "Table to be exported," the Export utility exits.
Table 20-1 lists the privileges required to import objects into you
r own schema. All of these privileges initially belong to the RESOURCE role.
Table 20-1 Privileges Required to Import Objects into Your Own Schema
| Object | Required Privilege (Privilege Type, If Applicable) |
|---|---|
| Clusters | CREATE CLUSTER (System) or UNLIMITED TABLESPACE (System). The
user must also be assigned a tablespace quota. |
| Database links | CREATE DATABASE LINK (System
) and CREATE SESSION (System) on remote database |
| Triggers on tables | CREATE TRIGGER (System) |
| Triggers on schemas | CREATE ANY TRIGGER (System) |
| Indexes | CREATE |
| Integrity constraints | ALTER TABLE (Object) |
| Libraries | CREATE ANY LIBRARY (System) |
| Packages | CREATE PROCEDURE (System) |
| Private synonyms | CREATE<
/code> |
| Sequen ces | CREATE SEQUENCE (System) |
| Snapshots | CREATE<
/code> |
| Store d functions | CREATE PROCEDURE (System) |
| Stored procedures | CREATE PROCEDURE (System) |
| Table data | INSERT TABLE (Object) | Table definitions (including comments and audit options ) | CREATE TABLE (System) or UNLIMITED TA
BLESPACE (System). The user must also be assigned a tablespace quota. |
| Views | CREATE VIEW (Syste
m) and SELECT (Object) on the base table, or SELECT ANY TABLE (System) |
| Object types | CREATE TYPE (System) |
| Foreign function libraries | CREATE LIBRARY (Sy
stem) |
| Dimensions | CREATE DIMENSION (System) |
| Operators | CREATE OPERATOR
(System) |
| Indextypes | CREATE INDEXTYPE (System) |
To import the privileges that a user has granted to others
, the user initiating the import must either own the objects or have object privileges with the WITH GRANT
OPTION. Table 20-2 shows the required conditions for the authorizations to be valid on the targe
t system.
< em>Table 20-2 Privileges Required to Import Grants
| Grant | Conditions |
|---|---|
| Object privileges | The object m
ust exist in the user's schema, or
the user must have the object privileges with the the user must have the |
| System privileges | User must have the WITH ADMIN OPTION. |
To import objects into another user's schema, you
must have the IMP_FULL_DATABASE role enabled.
To import system objects from a full database export file, the IMP_FULL_DATABASE role must be enable
d. The parameter FULL specifies that t
he following system objects are included in the import when the export file is a full export:
Profiles
Public database links
Public synonyms
Roles
Rollback segment definitions
Resource costs
Forei gn function libraries
Context objects
System procedural objects
System audit options
System privileges
Tablespace definit ions
Tablespace quotas
User definitions
Direct ory aliases
System event triggers
The following restrictions apply when you process data with the Export and Import utilities:
Java cla sses, resources, and procedures that are created using Enterprise JavaBeans (EJB) are not placed in the export file.
Constraints that have been altered using the RELY keyword lose the RELY attribute when they ar
e exported.
When a type definition has evolved and then data referencing that evolved type is exported, the type definition on the import system must have evolved in the same manner.
The table compression att ribute of tables and partitions is preserved during export and import. However, the import process does not use the direct path API, hence the data will not be stored in the compressed format when imported. Use the new Data Pump Export and Import utilities to enable compression during import.
Table objects are imported as they are read from the export file. The export file contain s objects in the following order:
Type definitions
Table definitions
Table data
Table indexes
Integrity constraints, views, procedures, and triggers
Bitmap, function-based, and domain indexes
< /li>The order of import is as follows: new tables are created, data is imported and indexes are built, triggers are imported , integrity constraints are enabled on the new tables, and any bitmap, function-based, and/or domain indexes are built. This sequence prevents data from being rejected due to the order in which tables are imported. This sequence also prevents redundant triggers from firing twice on the same data (once when it is originally inserted and again during the import).
For example, if the em
p table has a referential integrity constraint on the dept table and the emp table is imported first
, all emp rows that reference departments that have not yet been imported into dept would be rejected if th
e constraints were enabled.
When data is imported into existing tables, however, the order of import can still produce referen
tial integrity failures. In the situation just given, if the emp table already existed and referential integrity constra
ints 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, scott's row will fail, even though it would be valid at the end of the import.
|
Note: For the reasons menti oned previously, it is a good idea to disable referential constraints when importing into an existing table. You can then reenable th e constraints after the import is completed. |
This section describes factors to take into account when you import data into existing tables.
When you choose to create tables manually before importing data into them from an exp ort file, you should use either the same table definition previously used or a compatible format. For example, although you can incre ase the width of columns and change their order, you cannot do the following:
Add NOT NULL
columns
Change the datatype of a column to an incompatible datatype (LONG to NUMBER,<
/code> for example)
Change the definition of object types used in a table
C
hange DEFAULT column values
|
Note: When tables are manually created before data is imported, theCREATE TABLE statement in the export dump fi
le will fail because the table already exists. To avoid this failure and continue loading data into the table, set the Import paramet
er IGNORE=y. Otherwise, no data will be loaded into the table because of the table creation error. |
In the normal import order, referential constraints are imported only after all tables are imported. This sequence prevents errors that could occur if a referential integrity cons traint exists for data that has not yet been imported.< /a>
These errors can still occur when data is loaded into existing tables. For example, if table emp has a refere
ntial integrity constraint on the mgr column that verifies that the manager number exists in emp, a legitim
ate 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 di sable constraints manually to avoid this.
Referential constraints between tables can also cause problems. For example, if the
emp table appears before the dept table in the export file, but a referential check exists from the e
mp table into the dept table, some of the rows from the emp table may not be imported due to a refer
ential constraint violation.
To prevent errors like these, you should disable referential integrity constraints when importing data into existing tables.
When the constraints are reenabled af ter importing, the entire table is checked, which may take a long time for a large table. If the time required for that check is too long, it may be beneficial to order the import manually.
To do so, perform several imports from an export file instead of one. First, import tables that are the targets of refer ential checks. Then, import the tables that reference them. This option works if tables do not reference each other in a circular fas hion, and if a table does not reference itself.
Triggers that are defined to trigger on DDL events for a specific schema or on DDL-related events for the database, are system triggers. These triggers can have detrimental effects on certain import operations. For example, they can prevent successful re-creation of database objects, such as tables. This causes err ors to be returned that give no indication that a trigger caused the problem.
Databas e administrators and anyone creating system triggers should verify that such triggers do not prevent users from performing database o perations for which they are authorized. To test a system trigger, take the following steps:
Define the trigger.
Create some databa se objects.
Export the objects in table or user mode.
Delete the objects.
Import t he objects.
Verify that the objects have been successfully re-created.
|
Note: A full export does not export triggers owned by schemaSYS. You must man
ually re-create SYS triggers either before or after the full import. Oracle recommends that you re-create them after the
import in case they define actions that would impede progress of the import. |
The Export an d Import utilities support four modes of operation:
Full: Exports and imports a full database. Only user
s with the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles can use this mode. Use the FULL param
eter to specify this mode.
Tablespace: Enables a privileged user to move a set of tablespaces from one O
racle database to another. Use the TRANSPORT_TABLESPACE parameter to specify this mode.
Use
r: Enables you to export and import all objects that belong to you (such as tables, grants, indexes, and procedures). A privileged us
er importing in user mode can import all objects in the schemas of a specified set of users. Use the OWNER parameter to
specify this mode in Export, and use the FROMUSER parameter to specify this mode in Import.
Table: Enables you to export and import specific tables and partitions. A privileged user can qualify the tables by specifying the s
chema that contains them. Use the TABLES parameter to specify this mode.
See Table 20-3 for a list of objects that are exported and imported in each mode.
Caution:
When you use table mode to import tables that have columns of type
ANYDATA, you may receive the following error:
ORA-22370: Incorrect usage of metho d. Nonexistent type.
This indicates that the ANYDATA column depends on other types that are not present in the da
tabase. You must manually create dependent types in the target database before you use table mode to import tables that use the ANYDATA type.
A user with the IMP_FULL_
DATABASE role must specify one of these modes. Otherwise, an error results. If a user without the IMP_FULL_DATABASE role fails to specify one of these modes, a user-level Import is performed.
You can use conventional path Export or direct path Export to export in any mode except tablespace mode.The differences between conventional path Export and direct path Export are described in Conventional Path Export Versus Direct Path Export.
Table 20-3 Objects Exported and Imported in Each Mode
| Object | Table Mode | User Mode | Full Database Mode | Tablespace Mode |
|---|---|---|---|---|
| Analyze cluster | No | Yes | Yes | No |
| Analyze tables/statistics | Yes | Yes | Yes | Yes |
| Application contexts | No | No | Yes | No |
| Auditing information | Yes | Yes | Ye s | No |
| B-tree, bitmap, domain function-based indexes | YesFoot 1 | Yes | Yes | Yes |
| Cluster defi nitions | No | Yes | Yes | Yes |
| Yes | Yes | Yes | Yes | |
| Database links | No | Yes | Yes | No |
| Default roles | No | No | Yes | N o |
| Dimensions | No | Yes | Y es | No |
| Directory aliases | No | No | Yes | No |
| External tables (without data) | Yes | Yes | Yes | No |
| Foreign function libraries | No | Yes | Yes | No |
| Indexes owned by users other than table ow ner | Yes (Privileged users only) | Yes | Yes | Yes |
| Index types | No | Yes | Yes | No |
| Jav a resources and classes | No | Yes< /td> | Yes | No |
| Job queues | No< /td> | Yes | Yes | No |
| Nested tab le data | Yes | Yes | Yes | Yes |
| Object grants | Yes (Only for tables and indexes) | Yes | Yes | Yes |
| Object type definitions used by table | Yes | Yes | Yes | Y es |
| Object types | No | Yes | Yes | No |
| Operators | No | Yes | Yes | No |
| Password history | No | No | Yes | No |
| Postinstance actions and objects | No | No | Yes | No |
| Postschema procedural actions and objects | No | Yes | Yes | No |
| Posttable actions | Yes | Yes | Yes | Yes |
| Posttable procedural actions and objects | Yes | Yes | Yes | Yes |
| Preschema procedural objects and actions | No | Yes | Yes | No |
| P retable actions | Yes | Yes | Yes | |
| Pretable procedural actions | Yes | Yes | Yes | Yes |
| Private synonyms | No | Yes | Yes | No |
| Procedural objects | No | Yes | Yes | No |
| Profil es | No | No | Yes | No |
| Public synonyms | No | No | Yes | No |
| Referential integrity constr aints | Yes | Yes | Yes | No |
| Refresh groups | No | Yes | Yes | No |
| Resource costs | No | No | Yes | No |
| Role grants | No | No | Yes | No |
| Roles | No | No | Yes | |
| Rollback segment definitions | No | No | Yes | No |
| Security policies for table | Yes | Yes | Yes | Yes |
| Sequence numbers | No | Yes | Yes | No | Snapshot logs | No | Yes | Yes | No |
| Snapshots and materialized views | No | Yes | Yes | No |
| System privilege grants | No | No | Y es | No |
| Table constraints (primary, unique, check) | Yes | Yes | Yes | Yes |
| Table data | Yes | Yes | Yes | Yes |
| Table definitions | Yes | Yes | Yes | Yes |
| Tablespace definitions | No | No | Yes | No |
| Tablespace quotas | No | No | Yes | No | < /tr>
| Triggers | Yes | YesFoot 2 | YesFoot 3&nb sp; | Yes |
| Triggers owned by other users | Yes (Privileged users on ly) | No | No | No |
| User def initions | No | No | Yes | No |
| User proxies | No | No | Yes | No |
| User views | No | Yes | Yes | No |
| User-stored procedures, packages, and functions | No | Yes | Yes | No |
You can export tables, partitions, and subpartitions in the following ways:
Table-level Export: exports all data from the specified tables
Pa rtition-level Export: exports only data from the specified source partitions or subpartitions
In all modes, partitioned data is exported in a format such that partitions or subpartitions can be imported selectively.
In table-level Export, you can export an entire table (partitioned or nonpartitioned) along with its indexes and other table-dep endent objects. If the table is partitioned, all of its partitions and subpartitions are also exported. This applies to both direct p ath Export and conventional path Export. You can perform a table-level export in any Export mode.
In partition-level Export, you can export one or more specified partitions or subpartitions of a table. Yo u can only perform a partition-level export in table mode.
For information about how to specify table-level and partition-leve l Exports, see TABLES.
You can import tables, partitions, and su bpartitions in the following ways:
Table-level Import: Imports all data from the specified tables in an export file.
Partition-level Import: Imports only data from the specified source partitions or subpartit ions.
You must set the parameter IGNORE=y when loading data into an existing table. See IGNORE for more information.
For each specified table, table-level Import imports all rows of the table. With table-level Import:
All tab
les exported using any Export mode (except TRANSPORT_TABLESPACES) can be imported.
Users ca n import the entire (partitioned or nonpartitioned) table, partitions, or subpartitions from a table-level export file into a (partit ioned or nonpartitioned) target table with the same name.
If the table does not exist, and if the exported table was partitioned, table-level Import creates a partitioned table. If the table creation is successful, table-level Import reads all sourc e data from the export file into the target table. After Import, the target table contains the partition definitions of all partitions and subpartitions associated with the source table in the export file. This operation ensures that the physical and logica l attributes (including partition bounds) of the source partitions are maintained on import.
Partition-level Import can only be specified in table mode. It lets you selectively load d ata from specified partitions or subpartitions in an export file. Keep the following guidelines in mind when using partition-level Im port.
Import always stores the rows according to the partitioning scheme of the target table.
Partition-level Import inserts only the row data from the specified source partitions or subpartitions.
< li type="disc">If the target table is partitioned, partition-level Import rejects any rows that fall above the highest partition of the target table.
Partition-level Import cannot import a nonpartitioned exported table. However, a pa rtitioned table can be imported from a nonpartitioned exported table using table-level Import.
Partition -level Import is legal only if the source table (that is, the table called tablename at export time) was partitioned and exists in th e export file.
If the partition or subpartition name is not a valid partition in the export file, Import generates a warning.
The partition or subpartition name in the parameter refers to only the partition o r subpartition in the export file, which may not contain all of the data of the table on the export source system.
If ROWS=y (default), and the table does not exist in the import target system, the table is created and all r
ows from the source partition or subpartition are inserted into the partition or subpartition of the target table.
If ROWS=y (default) and IGNORE=y, but the table already existed before import, all rows for the
specified partition or subpartition in the table are inserted into the table. The rows are stored according to the existing partition
ing scheme of the target table.
If ROWS=n, Import does not insert data into the target tabl
e and continues to process other objects associated with the specified table and partition or subpartition in the file.
If the target table is nonpartitioned, the partitions and subpartitions are imported into the entire table. Import re
quires IGNORE=y to import one or more partitions or subpartitions from the export file into a nonpartitioned table on th
e import target system.
If you specify a partition name for a composite partition, all subpartitions within the composite partition are used as the source.
In the fol lowing example, the partition specified by the partition name is a composite partition. All of its subpartitions will be imported:
imp SYSTEM/password FILE=expdat.dmp FROMUSER=scott TABLES=b:py
The following example
causes row data of partitions qc and qd of table scott.e to be imported into the table
scott.e:
imp scott/tiger FILE=expdat.dmp TABLES=(e:qc, e:qd) IGNORE=y
If table e does not exist in the import target database, it is created and data is inserted into the same partitions. If table e
existed on the target system before import, the row data is inserted into the partitions whose range allows insertion. The ro
w data can end up in partitions of names other than qc and qd.
|
Note: With partition-level Import to an existing table, you must< /em> set up the target partitions or subpartitions properly and useIGNORE=y. |
This section contains descriptions of the Export command-line parameters.
Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this pa rameter.
Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum nu mber of rows in an array fetched by Export. Use the following formula to calculate the buffer size:
buffer_size = rows_in_array * maximum_row_size
If you specify zero, the Export util ity fetches only one row at a time.
Tables with columns of type LOBs, LONG, BFILE, REF,
ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.
This section shows an example of how to calculate buffe r size.
The following table is created:
CREATE TABLE sample (name varchar(30), weight number);
The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weig
ht column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.
Therefore , the maximum row size is 56 (30+2+22+2).
To perform array operations for 100 rows, a buffer size of 5600 should be specified.
Specifies how Export and Import manage the initial extent for table data.
The d
efault, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent si
zes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space req
uired to hold the data.
If you specify COMPRESS=n, Export uses the current storage parameters, including the valu
es of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent
size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.
|
Note: Although the actual consolidation is performed u pon import, you can specify theCOMPRESS parameter only when you export, not when you import. The Export utility, not th
e Import utility, generates the data definitions, including the storage parameter definitions. Therefore, if you specify COMPRE
SS=y when you export, you can import the data in consolidated form only. |
Specifies whether or not Export uses the SET TRANSACTION READ ONLY statemen
t to ensure that the data seen by Export is consistent to a single point in time and does not change during the execution of the CONSISTENT=y when you anticipate that other applications will be updating the t
arget data after an export has started.
If you use CONSISTENT=n, each table is usu
ally exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported
as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.
Therefore, if neste d tables and partitioned tables are being updated by other applications, the data that is exported could be inconsistent. To minimize this possibility, export those tables at a time when updates are not being done.
Table 20-4 shows a s
equence of events by two users: user1 exports partitions in a table and user2 updates data in that table.
Table 20-4 Sequence of Events During Updates by Two Users
| TIme Sequence | User1 | User2 |
|---|---|---|
| 1 | Begins export of TAB:P1 | No activity |
| 2 | No activity | Updates TAB:P2 Updates TAB:P1 Commits transaction |
| 3 | Ends export of TAB:P1 | No activity |
| 4 | < td align="left" headers="r5c1-t19 r1c2-t19">Exports TAB:P2No activity |
If the export uses CONSISTENT=y, none of the updates by u
ser2 are written to the export file.
If the export uses CONSISTENT=n, the updates to TAB:P1 are not writte
n to the export file. However, the updates to TAB:P2 are written to the export file, because the update transaction is committed befo
re the export of TAB:P2 begins. As a result, the user2 transaction is only partially recorded in the export file, making
it inconsistent.
If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be
large. In addition, the export of each table will be slower, because the rollback segment must be scanned for uncommitted transaction
s.
Keep in mind the following points about using CONSISTENT=y:
CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA,<
/code> or both.
Export of certain metadata may require the use of the SYS schema within rec
ursive SQL. In such situations, the use of CONSISTENT=y will be ignored. Oracle recommends that you avoid making metadat
a changes during an export process in which CONSISTENT=y is selected.
To minimize the time
and space required for such exports, you should export tables that need to remain consistent separately from those that do not. For e
xample, export the emp and dept tables together in a consistent export, and then export the remainder of th
e database in a second pass.
A "snapshot too old" error occurs when rollback space is used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity t o be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be pres erved.
If a committed transaction has been overwritten and the information is needed for a read-consistent view of the databas e, a "snapshot too old" error results.
To avoid this error, you should minimize the time taken by a read-consistent export. (D o this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rol lback segment as large as possible.
|
Note: Roll back segments will be deprecated in a future Oracle database release. Oracle recommends that you use automatic undo management instea d. |
Default: n
Specifies the use of direct path Export.
Specifying DIRECT=y causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer (evaluating buffer). This m
ethod can be much faster than a conventional path Export.
For information about direct path Exports, including security and pe rformance considerations, see Invoking a Direct Path Export.
Default: 0 (zero)
Specifies tha
t Export should display a progress meter in the form of a period for n number of rows exported. For example, if
you specify FEEDBACK=10, Export displays a period each time 10 rows are exported. The FEEDBACK value appli
es to all tables being exported; it cannot be set individually for each table.
Specifies the names of the export
dump files. The default extension is .dmp, but you can specify any extension. Because Export supports multiple export f
iles, you can specify multiple filenames to be used. For example:
exp scott/tiger FILE = dat1.dmp, dat2 .dmp, dat3.dmp FILESIZE=2048
When Export reaches the value you have specified for the maximum FILESIZE, Expo
rt stops writing to the current file, opens another export file with the next name specified by the FILE parameter, and
continues until complete or the maximum value of FILESIZE is again reached. If you do not specify sufficient export file
names to complete the export, Export will prompt you to provide additional filenames.
Default: Da ta is written to one file until the maximum size, as specified in Table 20-5, is reached.
Export suppo
rts writing to multiple export files, and Import can read from multiple export files. If you specify a value (byte limit) for the
When the amount of dat
a Export must write exceeds the maximum value you specified for FILESIZE, it will get the name of the next export file f
rom the FILE parameter (see FILE for more information) or, if it has used all the names specifie
d in the FILE parameter, it will prompt you to provide a new export filename. If you do not specify a value for FI
LESIZE (note that a value of 0 is equivalent to not specifying FILESIZE), then Export will write to only one file
, regardless of the number of files specified in the FILE parameter.
|
Note: If the space requirements of your export file exceed the available dis k space, Export will terminate, and you will have to repeat the Export after making sufficient disk space available. |
The FILESIZE parameter has a maximum value equal to the maxi
mum value that can be stored in 64 bits.
Table 20-5 shows tha t the maximum size for dump files depends on the operating system you are using and on the release of the Oracle database that you ar e using.
| Operating System | Release of Oracle Database | |
|---|---|---|
| Any | Prior to 8.1.5 | 2 gigabytes |
| 32-bit | 8.1.5 | 2 gigabytes |
| 64-bit | 8.1.5 and later | Unlimited |
| Any | < td align="left" headers="r5c1-t23 r1c3-t23">2 gigabytes||
| 32-bit with 64-bit files | 8.1.6 and later | Unlimited |
The maximum value that can be st
ored in a file is dependent on your operating system. You should verify this maximum value in your Oracle operating system-specific d
ocumentation before specifying FILESIZE. You should also ensure that the file size you specify for Export is supported o
n the system on which Import will run.
The FILESIZE value can also be specified as a number followed by KB (numbe
r of kilobytes). For example, FILESIZE=2KB is the same as FILESIZE=2048. Similarly, MB specifies megabytes
(1024 * 1024) and GB specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to obtain the fin
al file size (FILESIZE=2048B is the same as FILESIZE=2048).
Default: none
Specifies the system cha nge number (SCN) that Export will use to enable flashback. The export operation is performed with data consistent as of this specifie d SCN.
The fo
llowing is an example of specifying an SCN. When the export is performed, the data will be consistent as of SCN 3482971.
> exp system/password FILE=exp.dmp FLASHBACK_SCN=3482971
Default: none
Enables you to specify a timestamp. Export finds the SCN that most closely matches the specified timestamp. This SCN is used to enable flashback. T he export operation is performed with data consistent as of this SCN.
You can specify the time in any format that the DB
MS_FLASHBACK.ENABLE_AT_TIME procedure accepts. This means that you can specify it in either of the following ways
:
> exp system/password FILE=exp.dmp FLASHBACK_TIME="TIMESTAMP '2002-05-01 11:00:00'"
> exp syst
em/password FILE=exp.dmp FLASHBACK_TIME="TO_TIMESTAMP('12-02-2001 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"
Also, the old form at, as shown in the following example, will continue to be accepted to ensure backward compatibility:
& gt; exp system/password FILE=exp.dmp FLASHBACK_TIME="'2002-05-01 11:00:00'"
|
See Also:
|
|
Note: Because the value of theQUERY parameter contains b
lanks, most operating systems require that the entire strings WHERE job=\'SALESMAN\' and sal\<1600 be pl
aced in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded b
y an escape character. See your Oracle operating system-specific documentation for information about special and reserved characters
on your system. |
When executing this query, Export builds
a SQL SELECT statement similar to the following:
SELECT * FROM emp WHERE job='SALESMAN' an d sal <1600;
The values specified for the QUERY parameter are applied to all tables (or table partition
s) listed in the TABLE parameter. For example, the following statement will unload rows in both emp and
exp scott/tiger TABLES=emp,bonus QUERY=\"WHERE job=\'SALESMAN\' a nd sal\<1600\"
Again, the SQL statements that Export executes are similar to the following:
SELECT * FROM emp WHERE job='SALESMAN' and sal <1600; SELECT * FROM bonus WHERE job='SALESMAN' and sal <1600;
If a table is missing the columns specified in the QUERY clause, an error message will be produced, and no rows will be
exported for the offending table.
The QUERY parameter cannot be specified for full, user, or tablespace-mode exports.
The QUERY parameter must be applicable to all specified tables.
The QUERY
parameter cannot be specified in a direct path Export (DIRECT=y)
The QUERY parameter cannot be specified for tables with inner nested tables.
You cannot determine from the co
ntents of the export file whether the data is the result of a QUERY export.
Def ault: operating system-dependent
Specifies the length, in bytes, of the file record. The RECORDLENGTH parameter i
s 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 def aults to your platform-dependent value for buffer size.
You can set RECORDLENGTH to any value equal to or greater
than your system's buffer size. (The highest value is 64 KB.) Changing the RECORDLENGTH parameter affects only the size
of data that accumulates before writing to the disk. It does not affect the operating system file block size.
|
Note: You can use this parameter to specify the size of the Export I/O buffer. |
Default: n
The
RESUMABLE parameter is used to enable and disable resumable space allocation.
Because this parameter is disabled by default, you must set RESUMABLE=y in order to use its associated parameters, RESUMABLE_NAME and RESUMABLE_TIMEOUT.
|
See Also:
|
Default: 'User USERN
AME (USERID), Session SESSIONID, Instance INSTANCEID'
The value for this parameter identifies the statement that is res
umable. This value is a user-defined text string that is inserted in either the USER_RESUMABLE or DBA_RESUMABLE view to help you identify a specific resumable statement that has been suspended.
This parameter is ignored unless the y to enable resumable space allocation.
Default: 7200 second
s (2 hours)
The value of the parameter specifies the time period during which an error must be fixed. If the error is not fixe d within the timeout period, execution of the statement is terminated.
This parameter is ignored unless the RESUMABLE parameter is set to y to enable resumable space allocation.
Specifies the type of database optimizer statistics to generate when the exported data is
imported. Options are ESTIMATE, COMPUTE, and NONE. See the Import parameter STATISTICS and Importing Statistics.
In some cases, Export will place the precalculated statist
ics in the export file, as well as the ANALYZE statements to regenerate the statistics.
However, the precalculate d optimizer statistics will not be used at export time if a table has columns with system-generated names.
The precalculated o ptimizer statistics are flagged as questionable at export time if:
There are row errors while exporting< /p>
The client character set or NCHAR character set does not match the server character set or
NCHAR character set
A QUERY clause is specified
O nly certain partitions or subpartitions are exported
Previous
