Oracle8 Utilities Release 8.0 A58244-01 |
Library | Product | Contents | Index

# 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:

- What is the Export Utility?
- Export Modes
- Using Export
- Export Parameters
- Example Export Sessions
- Using the Interactive Method
- Warning, Error, and Completion Messages
- Direct Path Export
- Incremental, Cumulative, and Complete Exports
- Network Considerations
- Character Set and NLS Considerations
- Considerations in Exporting Database Objects
- Using Different Versions of Export
- Creating Oracle Release 7 Export Files from an Oracle8 Server

## What is the Export Utility?

Export provides a simple way for you to transfer data objects between Oracle database. Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format Export dump file located typically on disk or tape.

Such files can then be FTPed or physically transported (in the case of tape) to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.

The Export and Import utilities can also facilitate certain aspects of Oracle Advanced Replication functionality like offline instantiation. See Oracle8 Replication for more information.

Note that, Export dump files can only be read by the Oracle utility, Import (see Chapter 2, "Import"). If you need to read load data from ASCII fixed-format or delimited files, see Part II, SQL*Loader of this manual.

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.

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

  #### 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-27.

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

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

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 indexes 1 | 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
| triggers 2 | 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
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. 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-19.

## 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:

- creates the necessary export views
- assigns all necessary privileges to the EXP_FULL_DATABASE role
- assigns EXP_FULL_DATABASE to the DBA role

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 Reference for more information about dictionary views.

### Invoking Export

You can invoke Export in one of the following ways:

- Enter the following command:

      exp username/password PARFILE=filename

  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.

- Enter the command

      exp username/password

  followed by the parameters you need.

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

Enter only the command `exp username/password` to begin an interactive session and let Export prompt you for the information it needs. The interactive method provides less functionality than the parameter-driven method. It exists for backward compatibility.

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 the username/password combination, 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.

#### Invoking Export as SYSDBA

Typically, you should not need to invoke Export as SYSDBA. However, if you are using Tablespace Point-In-Time Recovery (TSPITR) which enables you to quickly recover one or more tablespaces to a point-in-time different from that of the rest of the database, you will need to know how to do so.

Attention: It is recommended that you read the information about TSPITR in the Oracle8 Backup and Recovery Guide, "POINT_IN_TIME_RECOVER" on page 2-26, and "RECOVERY_TABLESPACES" on page 1-19 before continuing with this section.

To invoke Export as SYSDBA, use the following syntax:

    exp username/password AS SYSDBA

or, optionally

    exp username/password@instance AS SYSDBA

Note: Since the string "AS SYSDBA" contains a blank, most operating systems require that entire string `username/password AS SYSDBA' be placed in quotes or marked as a literal by some method. Note that some operating systems also require that quotes on the command line be escaped as well. Please see your operating system-specific Oracle documentation for information about special and reserved characters on your system.

Note that if either the username or password is omitted, Export will prompt you for it.

If you prefer to use the Export interactive mode, please see "Interactively Invoking Export as SYSDBA" on page 1-29 for more information.

### 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.4.0.0 - Production on Fri Nov 03 9:26:39 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

    USERID must be the first parameter on the command line.

    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
    VOLSIZE  number of bytes to write to each tape volume

    Export terminated successfully without warnings.

### 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:

- To minimize the time and space required for such exports, you should export tables that need to remain consistent separately from those that do not.

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

- To reduce the chances of encountering a "snapshot too old" error, export the minimum number of objects that must be guaranteed consistent.

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

### 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 COMPLETE, CUMULATIVE, and INCREMENTAL. See "Incremental, Cumulative, and Complete Exports" on page 1-37 for more information.

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

### 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 Backup and Recovery Guide.

### 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-43.

### 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 Backup and Recovery Guide.

### 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 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:

- schema specifies the name of the user's schema from which to export the table or partition.
- tablename indicates that the export is a table-level Export. Table-level Export lets you export entire partitioned or non-partitioned tables. If a table in the list is partitioned and you do not specify a partition name, all its partitions are exported.
- partition name indicates that the export is a partition-level Export. Partition-level Export lets you export one or more specified partitions within a table.

If you use tablename: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 Export recognizes the duplicate entries and exports the table or partition only once. For example, the following:

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

causes one export of table 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 (and optional connect string) of the user initiating the export. If you omit the password Export will prompt you for it.

When using Tablespace Point-in-Time-Recovery USERID can also be:

    username/password AS SYSDBA

or

    username/password@instance AS SYSDBA

See "Invoking Export as SYSDBA" on page 1-8 for more information. Note also that your operating system may require you to treat AS SYSDBA as a special string requiring you to enclose the entire string in quotes as described on 1 - 8.

Optionally, you can specify the @connect_string clause for Net8. See the user's guide for your Net8 protocol for the exact syntax of @connect_string. See also Oracle8 Distributed Database Systems.

### 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.4.0.0 - Production on Fri Nov 7 8:23:12 1997
    (c) Copyright 1997 Oracle Corporation.  All rights reserved.
    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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.4.0.0 - Production on Fri Nov 7 4:12:14 1997

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

    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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.4.0.0 - Production on Fri Nov 7 9:24:34 1997

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

    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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.4.0.0 - Production on Fri Nov 5 9:25:33 1997

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

    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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.4.0.0 - Production on Fri Nov7 12:44:14 1997

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

    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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.4.0.0 - Production on Fri Nov 7 10:32:12 1997

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

    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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 combination on the command line, the Export utility prompts you for this information.

#### Interactively Invoking Export as SYSDBA

Typically, you should not need to invoke Export as SYSDBA. However, if you are using Tablespace Point-In-Time Recovery (TSPITR) which enables you to quickly recover one or more tablespaces to a point-in-time different from that of the rest of the database, you will need to know how to do so.

Attention: It is recommended that you read the information about TSPITR in the Oracle8 Backup and Recovery Guide, "POINT_IN_TIME_RECOVER" on page 2-26, and "RECOVERY_TABLESPACES" on page 1-19 before continuing with this section.

If you use the Export interactive mode, you will not be prompted to specify whether you want to connect as SYSDBA or @instance. You must specify "AS SYSDBA" and/or "@instance" with the username.

So the response to the Export interactive username prompt could be for example:

    username/password@instance as sysdba
    username/password@instance
    username/password as sysdba
    username/password
    username@instance as sysdba    (prompts for password)
    username@instance                        (prompts for password)
    username                                          (prompts for password)
    username AS sysdba                      (prompts for password)
    /    as sysdba                              (no prompt for password, OS authentication
                                                                is used)
    /                                                        (no prompt for password, OS authentication
                                                                is used)
    /@instance as sysdba                  (no prompt for password, OS authentication
                                                                is used)
    /@instance                                      (no prompt for password, OS authentication
                                                                is used)

Note: if you omit the password and allow Export to prompt you for it, you cannot specify the @instance string as well. You can specify @instance only with username.

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

### Restrictions

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

- In user mode, Export prompts for all user names 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 [Return].
- 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.

  For example, if BETH is a privileged user exporting in table mode, Export assumes that all tables are in BETH's schema until another schema is specified. Only a privileged user (someone with the EXP_FULL_DATABASE role) can export tables in another user's schema.

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

## 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-17) 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 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
- 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 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 Administrator's Guide.

Figure 1-2 on page 1 - 35 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 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:

- DB_BLOCK_SIZE
- the types of columns in your table
- your I/O layout (The drive receiving the export file should be separate from the disk drive where the database files reside.)

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:

- multiples of the file system I/O block size
- multiples of DB_BLOCK_SIZE

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

### Restrictions

The following restrictions apply when executing a direct path Export:

- You cannot use direct path Export to export rows that contain LOB, BFILE, REF, or object type columns, including VARRAY columns and nested tables. Only the data definition to create the table is exported, not the data.
- You cannot use the interactive method to invoke 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-43 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 1 - 38 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 - 40 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:

- smaller export files
- less time to export

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:

- a complete Export (X) every three weeks
- a cumulative Export (C) every Sunday
- an incremental Export (I) every night

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:

- a table is created
- a table definition is changed by an ALTER TABLE statement
- comments are added or edited
- auditing options are updated
- grants (of any level) are altered
- indexes are added or dropped
- index storage parameters are changed by an ALTER INDEX statement

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

- all system objects (including tablespace definitions, rollback segment definitions, and user privileges, but not including temporary segments)
- information about dropped objects
- clusters, tables, views, procedures, functions, and synonyms created since the last export
- all type definitions

Note: Export does not export grants on data dictionary views for security reasons that affect Import. If such grants were exported, access privileges would be changed and the user would not be aware of this. Also, not forcing grants on import allows the user more flexibility to set up appropriate grants on import.

### 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.4.0.0 - Production on Fri Nov 7 10:03:22 1997

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

    Connected to: Oracle8 Release 8.0.4.0.0 - Production
    PL/SQL Release 8.0.4.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:

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:

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 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 or Oracle Names, see the Net8 Administrator's Guide. See also "Invoking Export as SYSDBA" on page 1-8 if you are using Tablespace Point-in-Time Recovery.

## 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 Reference.

### 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:

- If a column is an object type owned by the owner of the table, its full type definition is included.
- If the object type definition depends on nested or referenced types, those nested or referenced types are exported iteratively, until Export reaches a type not owned by the table owner.
- If object types from other schemas are used, Export writes a warning message.
- If the user is not a privileged user, only object type definitions to which the user has execute access are included.
- If a column is an object type not owned by the owner of the table, the full object type definition is not written to the export file. Only enough information is written to verify that the type exists, with the same object identifier, on the import target system.

The user must ensure that the proper type definitions exist on the target system, either by working with the DBA to create them, or by importing them from full database 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 Oracle8.

### Using a Previous Version of Export

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

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-51 for a complete list of Oracle8 objects excluded from an Oracle release 7 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 Oracle Release 7 Export Files from an Oracle8 Server

You can create an Oracle release 7 export file from an Oracle8 database by running Oracle release 7 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 Oracle release 7 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.

### Excluded Objects

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

- directory aliases
- foreign function libraries
- object types
- tables containing objects introduced in Oracle8 (such objects include LOB, REF, and BFILE columns and nested tables)
- partitioned tables
- Index Organized Tables (IOT)
- tables containing more than 254 columns
- tables containing NCHAR columns
- tables containing VARCHAR columns longer than 2,000 characters
- reverse indexes
- password history

### Exporting to Version 6

If you need to export data from a Version 6 system, use the Oracle release 7.2 or earlier Export utility. Refer to the Oracle release 7.2 or earler documentation for information about any restrictions. Note that release 7.3 cannot be used.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index