Oracle8(TM) Server Utilities
Release 8.0
A54652-01

Library

Product

Contents

Index


Prev Next

1
Export

This chapter describes how to use the Export utility to write data from an Oracle database into an operating system file in binary format. This file is stored outside the database, and it can be read into another Oracle database by using the Import utility (described in Chapter 2, "Import"). This chapter covers the following topics:

Import/Export Overview

Export and Import form a combined process of transforming (a portion of) one installed version of the Oracle RDBMS into a different version. This process can go either direction, for example, from Oracle7 (Version 7) into Oracle8 (Version 8) or vice versa.

Transforming an earlier version database into a later version database is called migrating, for example, migrating an Oracle7 database to Oracle8.

Upgrading is the process of transforming an installed version of the Oracle RDBMS from one release into a later release, for example, Oracle8, Release 8.0.2 (Beta-2) to Release 8.0.3.

Transforming a later version database into an earlier version database is called downgrading, for example, downgrading an Oracle8 database to Oracle7. The process of transforming an installed version of the Oracle RDBMS from a later release back into an earlier release also is downgrading, for example Oracle7, Release 7.3 down to Release 7.2.

Note: If you are working with the Advanced Replication Option, refer to the information about migration and compatibility in the Oracle8 Server Replication manual. If you are using Trusted Oracle, see the Trusted Oracle documentation for information about using the Export utility in that environment.

Export Basics

The concept behind the Export utility's basic function is quite simple: extract the object definitions and table data from an Oracle database and store them in an Oracle-binary format export file that is located typically on tape or on disk. Export files can be used to transfer data between databases that are on the same system or on another system. Export files can also be used as backup files, in addition to normal backup files.

When you run Export against an Oracle database, objects (such as tables) are extracted, followed by their related objects (such as indexes, comments, and grants) if any, and then written to the Export file. See Figure 1-1.

Figure 1-1: Exporting a Database

Reading the Contents of an Export File

Export files are stored in Oracle-binary format. Export files generated by Oracle8 Export cannot be read by utilities other than Oracle8 Import. Export files created by Oracle8 Export cannot be read by earlier versions of the Import utility. Similarly, Import can read files written by Export, but cannot read files in other formats. To load data from ASCII fixed-format or delimited files, see Part II of this manual for information about SQL*Loader.

You can, however, display the contents of an export file by using the Import SHOW parameter. For more information, see "SHOW" on page 2-24.

Access Privileges

To use Export, you must have the CREATE SESSION privilege on an Oracle database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs.

If you do not have the system privileges contained in the EXP_FULL_DATABASE role, you cannot export objects contained in another user's schema. For example, you cannot export a table in another user's schema, even if you created a synonym for it.

Export Modes

The Export utility provides three modes of export. All users can export in table mode and user mode. A user with the EXP_FULL_DATABASE role (a privileged user) can export in table mode, user mode, and full database mode. The database objects that are exported depend on the mode you choose.

Table  

Exports specified tables in the user's schema, rather than all tables. A privileged user can export specified tables owned by other users. Privileged users can also export objects (such as indexes) defined on their own table, but that are owned by other users. Non-privileged users can export only those tables and dependent objects that they own.

With table mode, you can export an entire table, including all partitions (table-level), or one or more partitions in a table (partition-level).

Exports in table mode do not include cluster definitions. As a result, the data is imported into unclustered tables. Because of this, you can use Export to uncluster tables when there is not enough free space in your database.  

User  

Exports all objects (such as tables, data, grants, and indexes) in a user's schema. Privileged users can export all objects in the schemas of a specified set of users.  

Full Database  

Exports all objects in the database, except those in the schema of SYS. (Objects owned by user SYS are generated during database creation for internal and administrative use. Because they are not exported, you should not create a user table or any other object as user SYS.)

Only privileged users can export in this mode.

With full database mode, you can perform incremental, cumulative, and complete exports of a database.  

See "Export Parameters" on page 1-11 for information on specifying each mode.

You can use the conventional path Export or direct path Export to export in any of the three modes. The differences between conventional path export and direct path Export are described in "Direct Path Export" on page 1-32.

Table 1-1 shows the objects that are exported and imported in each mode.

Table 1-1: Objects Exported and Imported in Each Mode

Table Mode

 

User Mode

 

Full Database Mode

 

For each table in the TABLES list, users can export and import:  

For each user in the Owner list, users can export and import:  

Privileged users can export and import all database objects except those owned by SYS:  

object type definitions used by table  

foreign function libraries  

tablespace definitions  

table definitions  

object types  

profiles  

pre-table actions  

database links  

user definitions  

table data by partition  

sequence numbers  

roles  

nested table data  

cluster definitions  

system privilege grants  

owner's table indexes

table constraints (primary, unique, check)

owner's table grants  

In addition, for each table that the specified user owns, users can export and import:  

role grants

default roles

tablespace quotas  

analyze tables  

object type definitions used by table  

resource costs  

column and table comments  

table definitions  

rollback segment definitions  

auditing information  

pre-table actions  

database links  

table referential constraints  

table data by partition  

sequence numbers  

owner's table triggers  

nested table data  

all directory aliases  

post-table actions  

owner's table indexes1  

all foreign function libraries  

In addition, privileged users can export and import:  

table constraints (primary,unique,check)

owner's table grants  

all object types

all cluster definitions  

triggers owned by other users  

analyze table  

password history  

indexes owned by other users  

column and table comments  

default and system auditing  

 

private synonyms  

 

 

user stored procedures, packages, and functions  

For each table, the privileged user can export and import:  

 

auditing information  

object type definitions used by table  

 

user views  

table definitions  

 

analyze cluster  

pre-table actions  

 

referential integrity constraints  

table data by partition  

 

triggers2  

nested table data  

 

post-table actions  

table indexes  

 

snapshots  

table constraints (primary, unique, check)  

 

snapshot logs  

table grants  

 

job queues  

analyze table  

 

refresh groups  

column and table comments  

 

 

auditing information  

 

 

all referential integrity constraints  

 

 

all synonyms  

 

 

all views  

 

 

all stored procedures, packages, and functions  

 

 

all triggers  

 

 

post-table actions  

 

 

analyze cluster  

 

 

all snapshots  

 

 

all snapshot logs  

 

 

all job queues  

 

 

all refresh groups and children  

  1. Non-privileged users can export and import only indexes they own on tables they own. They cannot export indexes they own that are on tables owned by other users, nor can they export indexes owned by other users on their own tables. Privileged users can export and import indexes on the specified users' tables, even if the indexes are owned by other users. Indexes owned by the specified user on other users' tables are not included, unless those other users are included in the list of users to export.
  2. Non-privileged and privileged users can export and import all triggers owned by the user, even if they are on tables owned by other users.

 

Understanding Table-Level and Partition-Level Export

In table-level Export, an entire partitioned or non-partitioned table, along with its indexes and other table-dependent objects, is exported. All the partitions of a partitioned table are exported. (This applies to both direct path Export and conventional path Export.) All Export modes (full, user, table) support table-level Export.

In partition-level Export, the user can export one or more specified partitions of a table. Full database and user mode Export do not support partition-level Export; only table mode Export does. Because incremental Exports (incremental, cumulative, and complete) can be done only in full database mode, partition-level Export cannot be specified for incremental exports.

In all modes, partitioned data is exported in a format such that partitions can be imported selectively.

For information on how to specify a partition-level Export, see "TABLES" on page 1-20.

Using Export

This section describes how to use the Export utility, including what you need to do before you begin exporting and how to invoke Export.

Before Using Export

To use Export, you must run the script CATEXP.SQL or CATALOG.SQL (which runs CATEXP.SQL) after the database has been created.

Note: The actual names of the script files depend on your operating system. The script file names and the method for running them are described in your Oracle operating system-specific documentation.

CATEXP.SQL or CATALOG.SQL needs to be run only once on a database. You do not need to run it again before you perform the export. The script performs the following tasks to prepare the database for Export:

Before you run Export, ensure that there is sufficient disk or tape storage space to which to write the export file. If there is not enough space, Export terminates with a write-failure error.

You can use table sizes to estimate the maximum space needed. Table sizes can be found in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables:

select sum(bytes) from user_segments where segment_type='TABLE';

The result of the query does not include disk space used for data stored in LOB (large object) columns.

See the Oracle8 Server Reference Manual for more information about dictionary views.

Invoking Export

You can invoke Export in one of the following ways:

PARFILE is a file containing the export parameters you typically use. If you use different parameters for different databases, you can have multiple parameter files. This is the recommended method.

Note: The number of parameters cannot exceed the maximum length of a command line on the system.

You can use a combination of the first and second options. That is, you can list parameters both in the parameters file and on the command line. In fact, you can specify the same parameter in both places. The position of the PARFILE parameter and other parameters on the command line determines what parameters override others. For example, assume the parameters file params.dat contains the parameter INDEXES=Y and Export is invoked with the following line:

exp system/manager PARFILE=params.dat INDEXES=N

In this case, because INDEXES=N occurs after PARFILE=params.dat, INDEXES=N overrides the value of the INDEXES parameter in the PARFILE.

You can specify the username and password in the parameter file, although, for security reasons, this is not recommended. If you omit username/password, Export prompts you for it.

See "Export Parameters" on page 1-11 for descriptions of the parameters.

To see how to specify an export from a database that is not the default database, refer to "Exporting and Importing with Net8" on page 1-44.

Getting Online Help

Export provides online help. Enter exp help=y on the command line to see a help screen like the one shown below.

> exp help=y
Export: Release 8.0.3.0.0 - Production on Tue May 27 6:20:22 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
Keyword  Description (Default)        Keyword      Description (Default)
-----------------------------------------------------------------------
USERID   username/password           FULL        export entire file (N)
BUFFER   size of data buffer           OWNER        list of owner usernames
FILE    output file (EXPDAT.DMP)     TABLES       list of table names   
COMPRESS import into one extent (Y)   RECORDLENGTH length of IO record    
GRANTS   export grants (Y)             INCTYPE       incremental export type
INDEXES  export indexes (Y)            RECORD       track incr. export (Y)  
ROWS     export data rows (Y)         PARFILE      parameter filename     
CONSTRAINTS export constraints (Y)    CONSISTENT   cross-table consistency  
LOG    log  file  of  screen  output      STATISTICS    analyze  objects  (ESTIMATE)
DIRECT   direct path (N)                                        
FEEDBACK display progress every x rows (0)
POINT_IN_TIME_RECOVER   Tablespace Point-in-time Recovery (N)
RECOVERY_TABLESPACES    List of tablespace names to recover

The Parameter File

The parameter file allows you to specify Export parameters in a file where they can easily be modified or reused. Create the parameter file using any flat file text editor. The command-line option PARFILE=filename 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

The syntax for parameter file specifications is one of the following:

KEYWORD=value
KEYWORD=(value)
KEYWORD=(value1, value2, ...)

The following example shows a partial parameter file listing:

FULL=Y
FILE=DBA.DMP
GRANTS=Y
INDEXES=Y
CONSISTENT=Y

Additional Information: The maximum size of the parameter file may be limited by the operating system. The name of the parameter file is subject to the file naming conventions of the operating system. See your Oracle operating system-specific documentation for more information.

You can add comments to the parameter file by preceding them with the pound (#) sign. Export ignores all characters to the right of the pound (#) sign.

Export Parameters

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

The remainder of this section describes each parameter.

BUFFER

Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.

Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum number 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 utility fetches only one row at a time.

Tables with LONG, LOB, BFILE, REF, ROWID, or type columns are fetched one row at a time.

Note: The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export.

COMPRESS

Default: Y

Specifies how Export and Import manage the initial extent for table data.

The default, COMPRESS=Y, causes Export to flag table data for consolidation into one initial extent upon Import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data when you specify COMPRESS=Y.

If you specify COMPRESS=N, Export uses the current storage parameters, including the values 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 upon import, you can specify the COMPRESS parameter only when you export, not when you import. The Export utility, not the Import utility, generates the data definitions, including the storage parameter definitions. Thus, if you specify COMPRESS=Y when you export, you can import the data in consolidated form only.

Note: LOB data is not compressed. For LOB data, the original values of initial extent size and next extent size are used.

CONSISTENT

Default: N

Specifies whether or not Export uses the SET TRANSACTION READ ONLY statement 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 export command. You should specify CONSISTENT=Y when you anticipate that other applications will be updating the database after an export has started.

If you specify CONSISTENT=N (the default), tables are usually exported in a single transaction. 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 nested 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.

The following chart shows a sequence of events by two users: USER1 exports partitions in a table and USER2 updates data in that table.

Time Sequence   USER1   USER2  

1  

Begins export of TAB:P1  

 

2  

 

Updates TAB:P2
Updates TAB:P1
Commit transaction  

3  

Ends export of TAB:P1  

 

4  

Exports TAB:P2  

 

If the export uses CONSISTENT=Y, none of the updates by USER2 are written to the export file.

If the export uses CONSISTENT=N, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, USER2's 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 will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.

Keep in mind the following points about using CONSISTENT=Y:

For example, export the EMP and DEPT tables together in a consistent export, and then export the remainder of the database in a second pass.

The "snapshot too old" error occurs when rollback space has been used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.

If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.

To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.

Note: You cannot specify CONSISTENT=Y with an incremental export.

CONSTRAINTS

Default: Y

Specifies whether or not the Export utility exports table constraints.

DIRECT

Default: N

Specifies whether you use direct path or conventional path Export.

Specifying DIRECT=Y causes Export to extract data by reading the data directly, bypassing the SQL Command Processing layer (evaluating buffer). This method can be much faster than a conventional path export.

You can further improve performance by using direct path Export with the database in direct read mode. Contention for resources with other users is eliminated because database blocks are read into the private buffer cache, rather than a public buffer cache. Direct read mode is enabled if the database compatibility mode is 7.1.5 or higher. For more information about direct read mode, see the Oracle8 Server Administrator's Guide.

Direct path Export cannot be used to export data from tables that contain column types that were introduced in Oracle8. Those column types are REF, LOB, BFILE, or object type columns (which include VARRAYs and nested tables). If a table contains any of these objects, only the table definition is exported, not the data, and a warning message is given.

For more information about direct path Exports, see "Direct Path Export" on page 1-32.

FEEDBACK

Default: 0 (zero)

Specifies that Export should display a progress meter in the form of a dot for n number of rows exported. For example, if you specify FEEDBACK=10, Export displays a dot each time 10 rows are exported. The FEEDBACK value applies to all tables being exported; it cannot be set on a per-table basis.

FILE

Default: expdat.dmp

Specifies the name of the export file. The default extension is .dmp, but you can specify any extension.

FULL

Default: N

Indicates that the Export is a full database mode Export (that is, it exports the entire database.) Specify FULL=Y to export in full database mode. You need the EXP_FULL_DATABASE role to export in this mode.

GRANTS

Default: Y

Specifies whether or not the Export utility exports grants. The grants that are exported depend on whether you use full database or user mode. In full database mode, all grants on a table are exported. In user mode, only those granted by the owner of the table are exported.

HELP

Default: N

Displays a help message with descriptions of the Export parameters.

INCTYPE

Default: none

Specifies the type of incremental Export. The options are:

INCREMENTAL  

Exports all database objects that have changed since the last incremental, cumulative, or complete export, as tracked by the table SYS.INCEXP, then updates the table with a new ITIME and EXPID.  

CUMULATIVE  

Exports all database objects that have changed since the last cumulative or complete export, as tracked by the table SYS.INCEXP, then updates the table with a new CTIME, ITIME, and EXPID.  

COMPLETE  

Exports all objects and then updates the tables SYS.INCEXP and SYS.INCVID. (A FULL=Y export does not update these tables unless you specify the INCTYPE parameter.)  

For more information on the system tables that support incremental export and for the definitions of ITIME, EXPID, and CTIME, see "System Tables" on page 1-42.

See "Incremental, Cumulative, and Complete Exports" on page 1-36 for information on using these options.

INDEXES

Default: Y

Specifies whether or not the Export utility exports indexes.

LOG

Default: none

Specifies a file name to receive informational and error messages. For example:

exp system/manager LOG=export.log

If you specify this parameter, messages are logged in the log file and displayed to the terminal display.

OWNER

Default: undefined

Indicates that the Export is a user-mode Export and lists the users whose objects will be exported.

PARFILE

Default: undefined

Specifies a filename for a file that contains a list of Export parameters. For more information on using a parameter file, see "The Parameter File" on page 1-10.

POINT_IN_TIME_RECOVER

Default: N

Indicates whether or not the Export utility exports one or more tablespaces in an Oracle database. On Import, you can recover the tablespace to a prior point in time, without affecting the rest of the database. For more information, see the Oracle8 Server Readme, Release 8.0.3.

RECORD

Default: Y

Indicates whether or not to record an incremental or cumulative export in the system tables SYS.INCEXP, SYS.INCFIL, and SYS.INCVID. For information about these tables, see "System Tables" on page 1-42.

RECORDLENGTH

Default: operating system dependent

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

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

You can set RECORDLENGTH to any value equal to or greater than your system's BUFSIZ. (The highest value is 64KB.) Changing the RECORDLENGTH parameter affects only the size of data that accumulates before writing to the 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.

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

RECOVERY_TABLESPACES

Default: undefined

Specifies the tablespaces that will be recovered using point-in-time recovery. For more information about point-in-time recovery, see the Oracle8 Server Readme, Release 8.0.3.

ROWS

Default: Y

Specifies whether or not the rows of table data are exported.

STATISTICS

Default: ESTIMATE

Specifies the type of database optimizer statistics to generate when the exported data is imported. Options are ESTIMATE, COMPUTE, and NONE. See the Oracle8 Server Concepts manual for information about the optimizer.

TABLES

Default: undefined

Specifies that the Export is a table-mode Export and lists the table names and partition names to export. You can specify the following when you specify the name of the table:

If you use table-name:partition-name, the specified table must be partitioned, and partition-name must be the name of one of its partitions.

The following line shows an example of a partition-level Export:

exp system/manager FILE = export.dmp TABLES = (scott.b:px, scott.b:py, mary.c, d:qb)

In this example, scott.b must be a partitioned table, and px and py must be two of its partitions. The table denoted by mary.c can be a partitioned or non-partitioned table. Table d, however, must be a partitioned table, and qb must be one of its partitions.

If the table-name or partition-name for the same table is used redundantly in the command line, Export aborts with an error. For example, the following partition-level Export command line with a redundant specification for table sc and partition px of table sc causes Export to abort with an error:

exp system/manager FILE = export.dmp TABLES = (sc, sc:px, sc)

Additional Information: Some operating systems, such as UNIX, require that you use escape characters before special characters, such as a parenthesis, so that the character is not treated as a special character. On UNIX, use a backslash (\) as the escape character, as shown in the following example:

TABLES=\(EMP,DEPT\)

Table-Name Restrictions

Table names specified on the command line cannot include a pound (#) sign, unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table name includes a pound (#) sign, the Export utility interprets the rest of the line as a comment, unless the table name is enclosed in quotation marks.

For example, if the parameter file contains the following line, Export interprets everything on the line after EMP# as a comment, and therefore does not export the tables DEPT and MYDATA:

TABLES=(EMP#, DEPT, MYDATA)

However, given the following line, the Export utility exports all three tables:

TABLES=("EMP#", DEPT, MYDATA)

Attention: When you specify the table name using quotation marks, the name is case sensitive. The name must exactly match the table name stored in the database. By default, table names in a database are stored as uppercase.

In the previous example, a table named EMP# is exported, not a table named emp#. Because the tables DEPT and MYDATA are not specified in quotation marks, the names are not case sensitive.

Additional Information: Some operating systems require single quotation marks rather than double quotation marks, or vice versa; see your Oracle operating system-specific documentation. Different operating systems also have other restrictions on table naming. For example, the UNIX C shell does not handle a dollar sign ($) or pound sign (#) (or certain other special characters). You must use escape characters to get such characters in the name past the shell and into Export.

USERID

Default: none

Specifies the username/password of the user initiating the export.

Parameter Interactions

Certain parameters can conflict with each other. For example, because specifying TABLES can conflict with an OWNER specification, the following command causes Export to terminate with an error:

exp system/manager OWNER=jones TABLES=scott.emp

Similarly, OWNER conflicts with FULL=Y and TABLE conflicts with FULL=Y.

Although ROWS=N and INCTYPE=INCREMENTAL can both be used, specifying ROWS=N (no data) defeats the purpose of incremental exports, which is to make a backup copy of tables that have changed.

Example Export Sessions

The following examples show you how to use the command line and parameter file methods in the full database, user, and table modes.

Example Export Session in Full Database Mode

Only users with the DBA role or the EXP_FULL_DATABASE role can export in full database mode. In this example, an entire database is exported to the file dba.dmp with all GRANTS and all data.

Parameter File Method

> exp system/manager parfile=params.dat

The params.dat file contains the following information:

FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y

Command-Line Method

> exp system/manager full=Y file=dba.dmp grants=Y rows=Y

Export Messages

Export: Release 8.0.3.0.0 - Production on Mon May 5 10:56:16 1997
(c) Copyright 1997 Oracle Corporation.  All rights reserved.
Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting foreign function library names
. exporting object type definitions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL          0 rows exported
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
. . exporting table               DEF$_DEFAULTDEST          0 rows exported
. . exporting table               DEF$_DESTINATION          0 rows exported
. . exporting table                     DEF$_ERROR          0 rows exported
. . exporting table                       DEF$_LOB          0 rows exported
. . exporting table                    DEF$_ORIGIN          0 rows exported
. . exporting table                DEF$_PROPAGATOR          0 rows exported
. . exporting table                  DEF$_TEMP$LOB          0 rows exported
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. about to export ADAMS's tables via Conventional Path ...
. about to export JONES's tables via Conventional Path ...
. about to export CLARK's tables via Conventional Path ...
. about to export BLAKE's tables via Conventional Path ...
. . exporting table                           DEPT          8 rows exported
. . exporting table                        MANAGER          4 rows exported
. exporting referential integrity constraints
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting user history table
. exporting default and system auditing options
Export terminated successfully without warnings.

Example Export Session in User Mode

Exports in user mode can 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 

Export Messages

Export: Release 8.0.3.0.0 - Production on Mon May 5 11:16:1 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set
. exporting foreign function library names for user SCOTT
. exporting object type definitions for user SCOTT

About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
Export terminated successfully without warnings.

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=schema.table

If schema is not specified, Export defaults to the previous schema 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/manager tables=(a, scott.b, c, mary.d)

A user without the EXP_FULL_DATABASE role can export only tables that the user owns. A user with the EXP_FULL_DATABASE role can export dependent objects that are owned by other users. A non-privileged user can export only dependent objects that the user owns.

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

Example 1

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

Parameter File Method

> exp system/manager 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/manager tables=(scott.emp,blake.dept) grants=Y indexes=Y

Export Messages

Export: Release 8.0.3.0.0 - Production on Mon May 5 11:22:13 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table                            EMP         14 rows exported
Current user changed to BLAKE
. . exporting table                           DEPT          8 rows exported
Export terminated successfully without warnings.

Example 2

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

Export Messages

Export: Release 8.0.3.0.0 - Production on Mon May 5 11:25:9 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           DEPT          8 rows exported
. . exporting table                        MANAGER          4 rows exported
Export terminated successfully without warnings.

Example Export Session Using Partition-Level Export

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

Example 1

Assume EMP is a partitioned table with two partitions M and Z (partitioned on employee name). 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

Export Messages

Export: Release 8.0.3.0.0 - Production on Fri May 9 7:11:11 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                                     EMP                                                    
. . exporting partition                              M          8 rows exported
. . exporting partition                              Z          6 rows exported
Export terminated successfully without warnings.

Example 2

Assume EMP is a partitioned table with two partitions M and Z (partitioned on employee name). 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

Export Messages

Export: Release 8.0.3.0.0 - Production on Fri May 9 7:14:52 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set

About to export specified tables via Conventional Path ...
. .exporting table                              EMP                                  
. . exporting partition                              M          8 rows exported
Export terminated successfully without warnings.

Using the Interactive Method

Starting Export from the command line with no parameters initiates the interactive method. The interactive method does not provide prompts for all Export functionality. The interactive method is provided only for backward compatibility.

If you do not specify a username/password on the command line, the Export utility prompts you for this information. Then, Export displays the following prompts:

Enter array fetch buffer size: 4096 > 30720

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > E

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and WE8DEC NCHAR character set
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting foreign function library names
. exporting object type definitions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL          0 rows exported
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
. . exporting table               DEF$_DEFAULTDEST          0 rows exported
. . exporting table               DEF$_DESTINATION          0 rows exported
. . exporting table                     DEF$_ERROR          0 rows exported
. . exporting table                       DEF$_LOB          0 rows exported
. . exporting table                    DEF$_ORIGIN          0 rows exported
. . exporting table                DEF$_PROPAGATOR          0 rows exported
. . exporting table                  DEF$_TEMP$LOB          0 rows exported
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
. about to export ADAMS's tables via Conventional Path ...
. about to export JONES's tables via Conventional Path ...
. about to export CLARK's tables via Conventional Path ...
. about to export BLAKE's tables via Conventional Path ...
. . exporting table                           DEPT          8 rows exported
. . exporting table                        MANAGER          4 rows exported
. exporting referential integrity constraints
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting user history table
. exporting default and system auditing options
Export terminated successfully without warnings.

You may not see all prompts in a given Export session because some prompts depend on your responses to other prompts. Some prompts show a default answer. If the default is acceptable, press [Return].

Keep in mind the following points when you use the interactive method:

Warning, Error, and Completion Messages

The Export utility attempts to save as much of the database as possible, even when part of it has become corrupted, but errors can occur. This section discusses how Export handles those errors.

Log File

You can capture all Export messages in a log file, either by using the LOG parameter (see "LOG" on page 1-18) or, for those systems that permit it, by redirecting Export's output to a file. The Export utility writes a log of detailed information about successful unloads and any errors that may occur. Refer to the operating system-specific Oracle documentation for information on redirecting output.

Warning Messages

Export does not terminate after non-fatal errors. For example, if an error occurs while exporting a table, Export displays (or logs) an error message, skips to the next table, and continues processing. These non-fatal errors are known as warnings.

Export issues a warning whenever it encounters an invalid object. For example, if a non-existent table is specified as part of a table-mode export, the Export utility exports all other tables. Then, it issues a warning and terminates successfully, as shown in the following listing:

> exp scott/tiger tables=xxx,emp
...
About to export specified tables via Conventional Path ...
EXP-00011: SCOTT.XXX does not exist
. . exporting table                            EMP         14 rows exported
Export terminated successfully with warnings.

Fatal Error Messages

Some errors are fatal and terminate the Export session. These errors typically occur because of an internal problem or because a resource, such as memory, is not available or has been exhausted. For example, if the CATEXP.SQL script is not executed, Export issues the following fatal error message:

EXP-00024: Export views not installed, please notify your DBA

Additional Information: Messages are documented in the Oracle8 Server Messages manual and in your Oracle operating system-specific documentation.

Completion Messages

When Export completes without errors, Export displays the message "Export terminated successfully without warnings." If one or more non-fatal errors occurs but Export is able to continue to completion, Export displays the message "Export terminated successfully with warnings." If a fatal error occurs, Export terminates immediately with the message "Export terminated unsuccessfully."

Direct Path Export

Export provides two methods for exporting table data:

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 evaluation 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 extracts data much faster than a conventional path export. Direct path Export achieves this performance gain by reading data directly, bypassing the SQL Command Processing layer and saves on data copies whenever possible.

For added performance, you can set the database to direct read mode. This eliminates contention with other users for database resources because database blocks are read into the Export session's private buffer, rather than into a public buffer cache. For more information about direct read mode, see the Oracle8 Server Administrator's Guide.

Figure 1-2 on page 1-34 shows how data extraction differs between conventional path and direct path Export.

In a direct path Export, 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.

Note: The Export parameter BUFFER applies only to conventional path exports. For direct path Export, use the parameter RECORDLENGTH to specify the size of the buffer that Export uses for writing to the export file.

Figure 1-2: Database Reads on Conventional Path and Direct Path

Character Set Conversion

Direct path Export exports in the database server character set only. If the character set of the export session is not the same as the database character set when an export is initiated, Export displays a warning and aborts. Specify the session character set to be the same as that of the database before retrying the export.

Performance Issues

To reduce contention with other users for database resources during a direct path Export, you can use database direct read mode. To enable the database direct read mode, enter the following in the INIT.ORA file:

compatible = <db_version_number>, 

The db_version_number must be 7.1.5 or higher. For more information about direct read mode, see the Oracle8 Server Administrator's Guide.

You may improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export. Your exact performance gain varies depending upon the following factors:

When using direct path Export, set the RECORDLENGTH parameter equal to the DB_BLOCK_SIZE database parameter, so that each table scan returns a full database block worth of data. If the data does not fit in the export I/O buffer, the Export utility performs multiple writes to the disk for each database block.

The following values are generally recommended for RECORDLENGTH:

Note: Other factors also affect the use of direct read mode. See the Oracle8 Server Administrator's Guide for more information.

Restrictions

The following restrictions apply when executing a direct path Export:

Incremental, Cumulative, and Complete Exports

Incremental, cumulative, and complete Exports provide time- and space-effective backup strategies. This section shows how to set up and use these export strategies.

Restrictions

You can do incremental, cumulative, and complete Exports only in full database mode (FULL=Y). Only users who have the EXP_FULL_DATABASE role can run incremental, cumulative, and complete Exports. This role contains the privileges needed to modify the system tables that track incremental exports. "System Tables" on page 1-42 describes those tables.

You cannot specify incremental Exports as read-consistent.

Base Backups

If you use cumulative and incremental Exports, you should periodically perform a complete Export to create a base backup. Following the complete Export, perform frequent incremental Exports and occasional cumulative Exports. After a given period of time, you should begin the cycle again with another complete Export.

Incremental Exports

An incremental Export backs up only tables that have changed since the last incremental, cumulative, or complete Export. An incremental Export exports the table definition and all its data, not just the changed rows. Typically, you perform incremental Exports more often than cumulative or complete Exports.

Assume that a complete Export was done at Time 1. Figure 1-3 on page page 1-37 shows an incremental Export at Time 2, after three tables have been modified. Only the modified tables and associated indexes are exported.

Figure 1-3: Incremental Export at Time 2

Figure 1-4 shows another incremental Export at Time 3, after two tables have been modified since Time 2. Because Table 3 was modified a second time, it is exported at Time 3 as well as at Time 2.

Figure 1-4: Incremental Export at Time 3

Cumulative Exports

A cumulative Export backs up tables that have changed since the last cumulative or complete Export. A cumulative Export compresses a number of incremental Exports into a single cumulative export file. It is not necessary to save incremental export files taken before a cumulative export because the cumulative export file replaces them.

Figure 1-5 shows a cumulative Export at Time 4. Tables 1 and 6 have been modified since Time 3. All tables modified since the complete Export at Time 1 are exported.

Figure 1-5: Cumulative Export at Time 4

This cumulative export file includes the changes from the incremental Exports from Time 2 and Time 3. Table 3, which was modified at both times, occurs only once in the export file. In this way, cumulative exports save space over multiple incremental Exports.

Complete Exports

A complete Export establishes a base for incremental and cumulative Exports. It is equivalent to a full database Export, except that it also updates the tables that track incremental and cumulative Exports.

Figure 1-6 on page 1-39 shows a complete Export at Time 5. With the complete Export, all objects in the database are exported regardless of when (or if) they were modified.

Benefits

Incremental and cumulative Exports help solve the problems faced by administrators who work in environments where many users create their own tables. For example, administrators can restore tables accidentally dropped by users.

The benefits of incremental and cumulative Exports include:

These benefits result because not all tables have changed. As a result, the time and space required for an incremental or cumulative Export is shorter than for a full database Export.

Figure 1-6: Complete Export at Time 5

A Scenario

The scenario described in this section shows how you can use cumulative and incremental Exports.

Assume that as manager of a data center, you do the following tasks:

Your export schedule follows:

DAY: 1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22
     X  I  I  I  I  I  I  C  I  I  I  I  I  I  C  I  I  I  I  I  I  X
        Sun                                    Sun                                    Sun                                   Sun

To restore through day 18, first you import the system information from the incremental Export taken on day 18. Then, you import the data from:

  1. the complete Export taken on day 1
  2. the cumulative Export taken on day 8
  3. the cumulative Export taken on day 15
  4. three incremental Exports taken on days 16, 17, and 18

The incremental Exports on days 2 through 7 can be discarded on day 8, after the cumulative Export is done, because the cumulative Export incorporates all the incremental Exports. Similarly, the incremental Exports on days 9 through 14 can be discarded after the cumulative Export on day 15.

Note: The section "INCTYPE" on page 1-17 explains the syntax to specify incremental, cumulative, and complete Exports.

Which Data Is Exported?

The purpose of an incremental or cumulative Export is to identify and export only those database objects (such as clusters, tables, views, and synonyms) that have changed since the last Export. Each table is associated with other objects, such as the data, indexes, grants, audits, triggers, and comments.

The entire grant structure for tables or views is exported with the underlying base tables. Indexes are exported with their base table, regardless of who created the index. If the base view is included, "instead of" triggers on views are included.

Any modification (UPDATE, INSERT, or DELETE) on a table automatically qualifies that table for incremental Export. When a table is exported, all of its inner nested tables and LOB columns are exported also. Modifying an inner nested table column causes the outer table to be exported. Modifying a LOB column causes the entire table containing the LOB data to be exported.

Also, the underlying base tables and data are exported if database structures have changed in the following ways:

In addition to the base tables and data, the following data is exported:

Example Incremental Export Session

The following example shows an incremental Export session after the tables SCOTT.EMP and SCOTT.DEPT are modified:

> exp system/manager full=y inctype=incremental

Export: Release 8.0.3.0.0 - Production on Tue May 20 7:57:47 1997

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Connected to: Oracle8 Server Release 8.0.3.0.0 - Production
PL/SQL Release 8.0.3.0.0 - Production
Export done in US7ASCII character set and WE8DEC NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting foreign function library names
. exporting object type definitions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                           DEPT          8 rows exported
. . exporting table                            EMP         23 rows exported
. about to export ADAMS's tables via Conventional Path ...
. about to export JONES's tables via Conventional Path ...
. about to export CLARK's tables via Conventional Path ...
. about to export BLAKE's tables via Conventional Path ...
. exporting referential integrity constraints
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting default and system auditing options
. exporting information about dropped objects
Export terminated successfully without warnings.

System Tables

The user SYS owns three tables (INCEXP, INCFIL, and INCVID) that are maintained by Export. These tables are updated when you specify RECORD=Y (the default). You should not alter these tables in any way.

SYS.INCEXP

The table SYS.INCEXP tracks which objects were exported in specific exports. It contains the following columns:

OWNER#  

The username of the schema containing the table.  

NAME  

The object name. With OWNER#. The primary key consists of OWNER#, NAME and TYPE#.  

TYPE#  

The type of the object (a code standing for TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, SNAPSHOT, SNAPSHOT LOG, or PACKAGE BODY).  

CTIME  

The date and time of the last cumulative export that included this object.  

ITIME  

The date and time of the last incremental export that included this object.  

EXPID  

The ID of the incremental or cumulative export, also found in table SYS.INCFIL.  

You can use this information in several ways. For example, you could generate a report from SYS.INCEXP after each export to document the export file. You can use the views DBA_EXP_OBJECTS, DBA_EXP_VERSION, and DBA_EXP_FILES to display information about incremental exports.

SYS.INCFIL

The table SYS.INCFIL tracks the incremental and cumulative exports and assigns a unique identifier to each. This table contains the following columns:

EXPID  

The ID of the incremental or cumulative export, also found in table SYS.INCEXP.  

EXPTYPE  

The type of export (incremental or cumulative).  

EXPFILE  

The name of the export file.  

EXPDATE  

The date of the export.  

EXPUSER  

The username of the user doing the export.  

When you export with the parameter INCTYPE = COMPLETE, all the previous entries are removed from SYS.INCFIL and a new row is added specifying an "x" in the column EXPTYPE.

SYS.INCVID

The table SYS.INCVID contains one column for the EXPID of the last valid export. This information determines the EXPID of the next export.

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 Net8

By overcoming the boundaries between different machines and operating systems on a network, Net8 (previous versions are called SQL*Net) provides a distributed processing environment for Oracle8 Server products. With Net8 (and SQL*Net V2), 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 Export with Net8, include the @connect_string after the username/password when you enter the exp command, as shown in the following example:

exp scott/tiger@SUN2 FILE=export.dmp FULL=Y

For the exact syntax of this clause, see the user's guide for your Net8 or SQL*Net protocol. For more information on Net8, see the Net8 Administrator's Guide. If you use Oracle Names, also see the Oracle Names Administrator's Guide.

Character Set and NLS Considerations

This section describes the behavior of Export and Import with respect to National Language Support (NLS).

Character Set Conversion

The Export utility writes to the export file using the character set specified for the user session, such as 7-bit ASCII or IBM Code Page 500 (EBCDIC). If necessary, Import translates the data to the character set of its host system. Import converts character data to the user-session character set if that character set is different from the one in the export file.

The export file identifies the character encoding scheme used for the character data in the file. If that character set is any single-byte character set (for example, EBCDIC or USASCII7), and if the character set used by the target database is also a single-byte character set, the data is automatically converted to the character encoding scheme specified for the user session during import, as specified by the NLS_LANG environment variable. After the data is converted to the session character set, it is then converted to the database character set. See also "Single-Byte Character Sets During Export and Import" on page 1-46.

During the conversion, any characters in the export file that have no equivalent in the target character set are replaced with a default character. (The default character is defined by the target character set.) To guarantee 100% conversion, the target character set should be a superset or equivalent of the source character set.

For multi-byte character sets, conversion is performed only if the length of the character string cannot expand as a result of the conversion.

When you use direct path Export, the character set of the user's session must be the same as the database character set.

Caution: When the character set width differs between the export client and the export server, truncation of data can occur if conversion causes expansion of data. If truncation occurs, Export displays a warning message.

For more information, refer to the National Language Support section of the Oracle8 Server Reference Manual.

NCHAR Conversion During Export and Import

The Export utility always exports NCHAR data in the national character set of the Export server. (You specify the national character set with the NATIONAL character set statement at database creation.)

The Import utility does no translation of NCHAR data, but, if needed, OCI automatically converts the data to the national character set of the Import server.

Single-Byte Character Sets During Export and Import

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 machine 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.

This situation occurs because the 8-bit characters in the export file are converted to 7-bit characters through the client application. When sent to the database, the 7-bit characters are converted by the server into 8-bit characters. To avoid this situation, you must turn off one of these conversions. One way to do this is to set NLS_LANG to the character set of the export file data.

Multi-Byte Character Sets and Export and Import

An export file that is produced with a multi-byte 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.

Considerations in Exporting Database Objects

The following sections describe points you should take into consideration 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.

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.

Exporting LONG Datatypes

On export, LONG datatypes can be fetched in sections and do not require contiguous memory. However, enough memory must be available to hold the contents of each row, including the LONG data.

LONG columns can be up to 2 gigabytes in length.

Note: 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.

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. The database administrator must move the library and update the library specification if the database is moved to a new location.

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.

Directory aliases are not included in user 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.

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 must move the directories containing the external files to a location where they can be accessed.

Exporting Array Data

When the Export utility processes array columns and attributes, it allocates a buffer to accommodate an array using the largest dimensions that could be expected for the column or attribute. If the maximum dimension of the array greatly exceeds the memory used in each instance of the array, the Export may result in memory exhaustion. For example, if an array usually had 10 elements, but was dimensioned for a million elements, the Export utility would size its buffers to accommodate a million element instance.

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 and object identifier, is needed to verify that the object type on the target system is consistent with the object instances contained in the dump file.

In full database or user mode, the Export utility writes all object type definitions to the export file before it writes the table definitions.

In all modes, the Export utility also writes object type definitions for a table to the export file immediately preceding the table definition. This ensures that the object types needed by a table are created with the same object identifier at import time. If the object types already exist on the importing system, this allows Import to verify that the object identifiers are the same.

Note however, that the information preceding the table definition does not always include all the object type definitions needed by the table. Note the following points about the information preceding the table definition:

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.

The user must be cautious when performing table mode Import because the full definitions of object types from other schemas are not included in the information preceding the table.

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 proper set of users. For example, if Scott's table TABLE1 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.

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.

When you export a queue table, both the table definition information and the 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.

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.

Using Different Versions of Export

This section describes the general behavior and restrictions of running an Export version that is different from the Oracle Server.

Using a Previous Version of Export

In general, you can use the Export utility from any Oracle7 release to export from an Oracle8 Server and create an Oracle7 export file. (This procedure is described in "Creating Oracle7 Export Files from an Oracle8 Server" on page 1-51.)

Oracle Version 6 (or earlier) Export cannot be used against an Oracle8 database.

Whenever a lower version Export utility runs with a higher version of the Oracle Server, categories of database objects that did not exist in the lower version are excluded from the export. (See "Excluded Objects" on page 1-52 for a complete list of Oracle8 objects excluded from an Oracle7 Export.)

Attention: When backward compatibility is an issue, use the earlier release or version of the Export utility against the Oracle8 database, and use conventional path export.

Attention: Export files generated by Oracle8 Export, either direct path or conventional path, are incompatible with earlier releases of Import and can be imported only with Oracle8 Import.

Using a Higher Version Export

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

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

EXP-0:  Export terminated unsuccessfully

The error occurs because views that the higher version of Export expects are not present. To avoid this problem, use the version of the Export utility that matches the Oracle Server.

Creating Oracle7 Export Files from an Oracle8 Server

You can create an Oracle7 export file from an Oracle8 database by running Oracle7 Export against an Oracle8 Server. To do so, however, the user SYS must first run the CATEXP7.SQL script, which creates the export views that make the database look, to Export, like an Oracle7 database.

Note: An Oracle8 Export requires that the CATEXP.SQL script is run against the database before performing the Export. CATEXP.SQL is usually run automatically when the user SYS runs CATALOG.SQL to create the necessary views. CATEXP7.SQL, however, is not run automatically and must be executed manually. CATEXP7.SQL and CATEXP.SQL can be run in any order; after one of these scripts has been run, it need not be run again.

However, to use Oracle8 Export after the CATEXP7.SQL script has been run, you must run CATEXP.SQL because the set of privileges granted to the EXP_FULL_DATABASE role are different in each script.

Excluded Objects

The Oracle7 Export utility produces an Oracle7 export file by issuing queries against the views created by CATEXP7.SQL. These views are fully compatible with Oracle7 and consequently do not contain the following Oracle8 objects:

If you use the Oracle7 Export utility on a partitioned table or index, the partitioning information is lost. The table is exported as a non-partitioned table.

Exporting to Version 6

If you need to export data from a Version 6 system, use the Oracle7 Export utility. Refer to the Oracle7 documentation for information about any restrictions.




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library

Product

Contents

Index