Export Utility Part2

Example Export Session in User Mode

User mode exports can be used to back up one or more database users. For example, a DBA may want to back up the tables of deleted users for a period of time. User mode is also appropriate for users who want to back up their own data or who want to move objects from one owner to another. In this example, user scott is exporting his own tables.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following information:

FILE=scott.dmp

OWNER=scott

GRANTS=y

ROWS=y

COMPRESS=y

Command-Line Method

> exp scott/tiger FILE=scott.dmp OWNER=scott GRANTS=y ROWS=y COMPRESS=y

Example Export Sessions in Table Mode

In table mode, you can export table data or the table definitions. (If no rows are exported, the CREATE TABLE statement is placed in the export file, with grants and indexes, if they are specified.)

A user with the EXP_FULL_DATABASE role can use table mode to export tables from any user's schema by specifying TABLES=schemaname.tablename.

If schemaname is not specified, Export defaults to the previous schemaname from which an object was exported. If there is not a previous object, Export defaults to the exporter's schema. In the following example, Export defaults to the SYSTEM schema for table a and to scott for table c:

> exp SYSTEM/password TABLES=(a, scott.b, c, mary.d)

A user with the EXP_FULL_DATABASE role can also export dependent objects that are owned by other users. A nonprivileged user can export only dependent objects for the specified tables that the user owns.

Exports in table mode do not include cluster definitions. As a result, the data is exported as unclustered tables. Thus, you can use table mode to uncluster tables.

Example 1: DBA Exporting Tables for Two Users

In this example, a DBA exports specified tables for two users.

Parameter File Method

> exp SYSTEM/password PARFILE=params.dat

The params.dat file contains the following information:

FILE=expdat.dmp

TABLES=(scott.emp,blake.dept)

GRANTS=y

INDEXES=y

Command-Line Method

> exp SYSTEM/password FILE=expdat.dmp TABLES=(scott.emp,blake.dept) GRANTS=y INDEXES=y

Example 2: User Exports Tables That He Owns

In this example, user blake exports selected tables that he owns.

Parameter File Method

> exp blake/paper PARFILE=params.dat

The params.dat file contains the following information:

FILE=blake.dmp

TABLES=(dept,manager)

ROWS=y

COMPRESS=y

Command-Line Method

> exp blake/paper FILE=blake.dmp TABLES=(dept, manager) ROWS=y COMPRESS=y

Example 3: Using Pattern Matching to Export Various Tables

In this example, pattern matching is used to export various tables for users scott and blake.

Parameter File Method

> exp SYSTEM/password PARFILE=params.dat

The params.dat file contains the following information:

FILE=misc.dmp

TABLES=(scott.%P%,blake.%,scott.%S%)

Command-Line Method

> exp SYSTEM/password FILE=misc.dmp TABLES=(scott.%P%,blake.%,scott.%S%)

Example Export Session Using Partition-Level Export

In partition-level export, you can specify the partitions and subpartitions of a table that you want to export.

Example 1: Exporting a Table Without Specifying a Partition

Assume emp is a table that is partitioned on employee name. There are two partitions, m and z. As this example shows, if you export the table without specifying a partition, all of the partitions are exported.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following:

TABLES=(emp)

ROWS=y

Command-Line Method

> exp scott/tiger TABLES=emp rows=y

Example 2: Exporting a Table with a Specified Partition

Assume emp is a table that is partitioned on employee name. There are two partitions, m and z. As this example shows, if you export the table and specify a partition, only the specified partition is exported.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following:

TABLES=(emp:m)

ROWS=y

Command-Line Method

> exp scott/tiger TABLES=emp:m rows=y

Example 3: Exporting a Composite Partition

Assume emp is a partitioned table with two partitions, m and z. Table emp is partitioned using the composite method. Partition m has subpartitions sp1 and sp2, and partition z has subpartitions sp3 and sp4. As the example shows, if you export the composite partition m, all its subpartitions (sp1 and sp2) will be exported. If you export the table and specify a subpartition (sp4), only the specified subpartition is exported.

Parameter File Method

> exp scott/tiger PARFILE=params.dat

The params.dat file contains the following:

TABLES=(emp:m,emp:sp4)

ROWS=y

Command-Line Method

> exp scott/tiger TABLES=(emp:m, emp:sp4) ROWS=y

Using the Interactive Method

You enter...	Export's Response
exp username/password@instance as sysdba	Starts an Export session
exp username/password@instance	Starts an Export session
exp username/password as sysdba	Starts an Export session
exp username/password	Starts an Export session
exp username@instance as sysdba	Prompts for password
exp username@instance	Prompts for password
exp username	Prompts for password
exp username as sysdba	Prompts for password
exp / as sysdba	No prompt for password, operating system authentication is used
exp /	No prompt for password, operating system authentication is used
exp /@instance as sysdba	No prompt for password, operating system authentication is used
exp /@instance	No prompt for password, operating system authentication is used

** Restrictions

1) 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 Enter.

2) In table mode, if you do not specify a schema prefix, Export defaults to the exporter's schema or the schema containing the last table exported in the current session.

3) If you specify a null table list to the prompt "Table to be exported," the Export utility exits.

Warning, Error, and Completion Messages

This section describes the different types of messages issued by Export and how to save them in a log file.

Log File

You can capture all Export messages in a log file, either by using the LOG parameter or, for those systems that permit it, by redirecting Export's output to a file.

Warning Messages

a) Export does not terminate after recoverable errors.

b) Export also issues a warning whenever it encounters an invalid object.

Nonrecoverable Error Messages

Some errors are nonrecoverable and terminate the Export session. E,g. error message: EXP-00024: Export views not installed, please notify your DBA

Completion Messages

Export terminated successfully without warnings

(one or more recoverable errors) Export terminated successfully with warnings

(a nonrecoverable error occurs) Export terminated unsuccessfull

Exit Codes for Inspection and Display

Export provides the results of an export operation immediately upon completion. Depending on the platform, Export may report the outcome in a process exit code as well as recording the results in the log file.

Result	Exit Code
Export terminated successfully without warnings	EX_SUCC
Export terminated successfully with warnings	EX_OKWARN
Export terminated unsuccessfully	EX_FAIL

For UNIX, the exit codes are as follows:

EX_SUCC 0

EX_OKWARN 0

EX_FAIL 1

Conventional Path Export Versus Direct Path Export

Conventional path Export uses the SQL SELECT statement to extract data from tables. 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 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.

Invoking a Direct Path Export

To use direct path Export, specify the DIRECT=y parameter on the command line or in the parameter file. The default is DIRECT=n, which extracts the table data using the conventional path.

Additionally, be aware that the Export parameter BUFFER applies only to conventional path Exports. For direct path Export, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.

In versions of Export prior to 8.1.5, you could not use direct path Export for tables containing objects and LOBs. If you tried to, their rows were not exported. This behavior has changed. Rows in tables that contain objects and LOBs will now be exported using conventional path, even if direct path was specified. Import will correctly handle these conventional path tables within direct path dump files.

Security Considerations for Direct Path Exports

Virtual Private Database (VPD) and Oracle Label Security are not enforced during direct path Exports.

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: 1) The database user SYS , 2) Database users granted the Oracle9i EXEMPT ACCESS POLICY privilege, either directly or through a database role

Performance Issues for Direct Path Exports

You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export. Your exact performance gain depends upon the following factors: 1) DB_BLOCK_SIZE 2) The types of columns in your table 3) Your I/O layout (The drive receiving the export file should be separate from the disk drive where the database files reside.)

The following values are generally recommended for RECORDLENGTH: 1) Multiples of the file system I/O block size 2) Multiples of DB_BLOCK_SIZE

Network Considerations

This section describes factors to take into account when you use Export and Import across a network.

Transporting Export Files Across a Network

Because the export file is in binary format, use a protocol that supports binary transfers to prevent corruption of the file when you transfer it across a network. For example, use FTP or a similar file transfer protocol to transmit the file in binary mode. Transmitting export files in character mode causes errors when the file is imported.

Exporting and Importing with Oracle Net

With Oracle Net, you can perform exports and imports over a network. For example, if you run Export locally, you can write data from a remote Oracle database into a local export file. If you run Import locally, you can read data into a remote Oracle database.

To use Import with Oracle Net, include the connection qualifier string @connect_string when entering the username/password in the exp or imp command. For the exact syntax of this clause, see the user's guide for your Oracle Net protocol.

Character Set and Globalization Support Considerations

This section describes the behavior of Export and Import with respect to globalization support.

Character Set Conversion

The Export utility always exports user data, including Unicode data, in the character sets of the Export server. The character sets are specified at database creation.

The Import utility automatically converts the data to the character sets of the Import server.

Some 8-bit characters can be lost (that is, converted to 7-bit equivalents) when you import an 8-bit character set export file. This occurs if the client system has a native 7-bit character set or if the NLS_LANG operating system environment variable is set to a 7-bit character set. Most often, you notice that accented characters lose their accent mark.

Effect of Character Set Sorting Order on Conversions

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:

create table partlist

(

part varchar2(10),

partno number(2)

)

partition by range (part)

(

partition part_low values less than ('Z')

tablespace tbs_1,

partition part_mid values less than ('z')

tablespace tbs_2,

partition part_high values less than (MAXVALUE)

tablespace tbs_3

);

This partitioning scheme makes sense because z comes after Z in ASCII character sets.

When this table is imported into a database based upon an EBCDIC character set, all of the rows in the part_mid partition will migrate to the part_low partition because z comes before Z in EBCDIC character sets.

Multibyte Character Sets and Export and Import

An export file that is produced with a multibyte character set (for example, Chinese or Japanese) must be imported on a system that has the same character set or where the ratio of the width of the widest character in the import character set to the width of the smallest character in the export character set is 1. If the ratio is not 1, Import cannot translate the character data to the import character set.

Instance Affinity and Export

Considerations When Exporting Database Objects

The following sections describe points you should consider when you export particular database objects.

Exporting Sequences

If transactions continue to access sequence numbers during an export, sequence numbers can 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.

Exporting LONG and LOB Datatypes

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.

Exporting Foreign Function Libraries

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 and user mode export. You must move the library's executables and update the library specification if the database is moved to a new location.

Exporting Offline Bitmapped Tablespaces

If the data you are exporting contains offline bitmapped 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 first create the offline bitmapped tablespaces before importing to prevent DDL commands that may reference the missing tablespaces from failing.

Exporting Directory Aliases

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.

Exporting BFILE Columns and Attributes

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.

External Tables

The contents of external tables are not included in the export file. Instead, only the table specification (name, location) is included in full database 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.

Exporting Object Type Definitions

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.

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 or user mode exports performed by the DBA.

Exporting Nested Tables

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.

Exporting Advanced Queue (AQ) Tables

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.

Exporting Synonyms

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.

Support for Fine-Grained Access Control

You can export tables with fine-grained access control policies enabled. 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.

Only user SYS, or a user with the EXPORT_FULL_DATABASE role enabled or who has been granted EXEMPT ACCESS POLICY, can perform direct path Exports on tables having fine-grained access control.

Transportable Tablespaces

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/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 binary files, such as the operating system copying facility, binary-mode FTP, or publishing on CD-ROMs.

After copying the datafiles and exporting the metadata, you can optionally put the tablespaces in read/write mode.

** Export provides the following parameters to enable export of transportable tablespace metadata.

TABLESPACES

TRANSPORT_TABLESPACE

Exporting from a Read-Only Database

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 on-disk 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.

Using Export and Import to Partition a Database Migration

When you use the Export and Import utilities to migrate a large database, it may be more efficient to partition the migration into multiple export and import jobs.

1) Partitioning a migration has the following advantages:

Time required for the migration may be reduced because many of the subjobs can be run in parallel.

The import can start as soon as the first export subjob completes, rather than waiting for the entire export to complete.

2) Disadvantages of Partitioning a Migration

The export and import processes become more complex.

Support of cross-schema references for certain types of objects may be compromised.

3) How to Use Export and Import to Partition a Database Migration

For all top-level metadata in the database, issue the following commands:

exp dba/password FILE=full FULL=y CONSTRAINTS=n TRIGGERS=n ROWS=n INDEXES=n

imp dba/password FILE=full FULL=y

For each scheman in the database, issue the following commands:

exp dba/password OWNER=scheman FILE=scheman

imp dba/password FILE=scheman FROMUSER=scheman TOUSER=scheman IGNORE=y

All exports can be done in parallel. When the import of full.dmp completes, all remaining imports can also be done in parallel.

Using Different Versions of Export

Using a Previous Version of Export

In general, you can use the Export utility from any Oracle8 release to export from an Oracle9i server and create an Oracle8 export file. Oracle Version 6 (or earlier) Export cannot be used against an Oracle9i database.

Whenever a lower version Export utility runs with a higher version of the Oracle database server, categories of database objects that did not exist in the lower version are excluded from the export.

Using a Higher Version of Export

Attempting to use a higher version of Export with an earlier Oracle database server often produces the following error:

EXP-37: Database export views not compatible with Export utility
EXP-0: Export terminated unsuccessfully

Creating Oracle Release 8.0 Export Files from an Oracle9i Database

Export does not export rows from tables containing objects and LOBs when you have specified a direct path load (DIRECT=y).

Export does not export dimensions.

Functional and domain indexes are not exported.

Secondary objects (tables, indexes, sequences, and so on, created in support of a domain index) are not exported.

Views, procedures, functions, packages, type bodies, and types containing references to new Oracle9i features may not compile.

Objects whose DDL is implemented as a stored procedure rather than SQL are not exported.

Triggers whose action is a CALL statement are not exported.

Tables containing logical ROWID columns, primary key refs, or user-defined OID columns will not be exported.

Temporary tables are not exported.

Index-organized tables (IOTs) revert to an uncompressed state.

Partitioned IOTs lose their partitioning information.

Index types and operators are not exported.

Bitmapped, temporary, and UNDO tablespaces are not exported.

Java sources, classes, and resources are not exported.

Varying-width CLOBs, collection enhancements, and LOB-storage clauses for VARRAY columns or nested table enhancements are not exported.

Fine-grained access control policies are not preserved.

External tables are not exported.

e.g.:
 F:/oracle/product>exp carmot_hangzhou/carmot@igrp_hangzhou file=E:/f1.dmp tables
=(wf_moduleinfo) log=E:/l1.log
** igrp_hangzhou 已经用NET ASSISTANT CONFIG 设置了