Oracle8i Utilities
Release 2 (8.1.6)

Part Number A76955-01

Library

Product

Contents

Index

Go to previous page Go to next page

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 using the Import utility (described in Chapter 2). This chapter covers the following topics:

What Is the Export Utility?

Export provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations. 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 transferred using FTP or physically transported (in the case of tape) to a different site. The files can then be used with the Import utility to transfer data between databases that are on machines not connected through a network. The files can also be used as backups in addition to normal backup procedures.

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

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

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 Export cannot be read by utilities other than Import. Export files created by Export cannot be read by earlier versions of the Import utility. However, Import can read files written by the current and previous releases of 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.

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.

Note also that the following schema names are reserved and will not be processed by Export:

Export Modes

The Export utility provides four 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, tablespace, and full database mode. The database objects that are exported depend on the mode you choose. Tablespace mode allows you to move a set of tablespaces from one Oracle database to another. See Transportable Tablespaces and the Oracle8i Administrator's Guide for details about how to move or copy tablespaces to another database. For an introduction to the transportable tablespaces feature, see Oracle8i Concepts.

See Export Parameters for information on specifying each mode.

You can use conventional path Export or direct path Export to export in any of the first three modes. The differences between conventional path export and direct path Export are described in Direct Path Export.

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
 
Tablespace 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, and those in the ORDSYS, CTXSYS, MDSYS and ORDPLUGINS schemas:  

For each tablespace in the TABLESPACES list, a privileged user can export and import the DDL for the following objects:  

pre-table procedural actions  

foreign function libraries  

tablespace definitions  

cluster definitions  

object type definitions used by table  

object types  

profiles  

 

table definitions  

database links  

user definitions  

For each table within the current tablespace, the following objects' DDL is included:  

pre-table actions  

sequence numbers  

roles  

 

table data by partition  

cluster definitions  

system privilege grants  

pre-table procedural actions  

nested table data  

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

role grants

default roles

tablespace quotas  

object type definitions used by the table  

owner's table grants

owner's table indexes

table constraints (primary, unique, check)  

pre-table procedural actions  

resource costs  

table definition (table rows are not included)  

analyze tables  

object type definitions used by table  

rollback segment definitions  

pre-table actions  

column and table comments  

table definitions  

database links  

table grants  

auditing information  

pre-table actions  

sequence numbers  

table indexes  

security policies for table  

table data by partition  

all directory aliases  

table constraints (primary, unique, check)  

table referential constraints  

nested table data  

application contexts  

column and table comments  

owner's table triggers  

owner's table grants  

all foreign function libraries  

referential integrity constraints  

post-table actions  

owner's table indexes (1)  

all object types

all cluster definitions  

bitmap indexes (note: not functional or domain indexes)  

post-table procedural actions and objects  

table constraints (primary, unique, check)  

default and system auditing  

post-table actions  

 

analyze table  

 

triggers  

In addition, privileged users can export and import:  

column and table comments  

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

post-table procedural actions and objects  

triggers owned by other users  

auditing information  

pre-table procedural actions  

 

indexes owned by other users  

security policies for table  

object type definitions used by table  

 

 

table referential constraints  

table definitions  

 

 

private synonyms  

pre-table actions  

 

 

user views  

table data by partition  

 

 

user stored procedures, packages, and functions  

nested table data  

 

 

referential integrity constraints  

table grants  

 

 

operators  

table indexes  

 

 

triggers (2)  

table constraints (primary, unique, check)  

 

 

post-table actions  

analyze table  

 

 

indextypes  

column and table comments  

 

 

snapshots and materialized views  

auditing information  

 

 

snapshot logs  

all referential integrity constraints  

 

 

job queues  

all synonyms  

 

 

refresh groups  

all views  

 

 

dimensions  

all stored procedures, packages, and functions  

 

 

procedural objects  

post-table actions  

 

 

post-table procedural actions and objects  

operators  

 

 

post-schema procedural actions and objects  

indextypes  

 

 

 

post-table actions  

 

 

 

all triggers  

 

 

 

analyze cluster  

 

 

 

all snapshots and materialized views  

 

 

 

all snapshot logs  

 

 

 

all job queues  

 

 

 

all refresh groups and children  

 

 

 

dimensions  

 

 

 

password history  

 

 

 

system auditing  

 

 

 

post-table procedural actions and objects  

 

 

 

post-schema procedural actions and objects  

 

  1. Nonprivileged 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. Nonprivileged 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 nonpartitioned table, along with its indexes and other table-dependent objects, is exported. All of the partitions and subpartitions of a partitioned table are exported. (This applies to both direct path Export and conventional path Export.) All Export modes (full database, user, table, and transportable tablespace) support table-level Export.

In partition-level Export, the user can export one or more specified partitions or subpartitions of a table. Full database, user, and transportable tablespace 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 or subpartitions can be imported selectively.

For information on how to specify a partition-level Export, see TABLES.

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 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) or VARRAY columns or partitions.

See the Oracle8i Reference for more information about dictionary views.

Invoking Export

You can invoke Export in one of the following ways:

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 for descriptions of the parameters.

To see how to specify an export from a remote database, refer to Exporting and Importing with Net8.

Invoking Export As SYSDBA

SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users. Therefore, you should not typically need to invoke Export as SYSDBA. However, there may be a few situations in which you need to do so, usually at the request of Oracle technical support.

To invoke Export as SYSDBA, use the following syntax:

exp username/password AS SYSDBA

or, optionally:

exp username/password@instance AS SYSDBA 
 

Note: Because the string "AS SYSDBA" contains a blank, most operating systems require that entire string 'username/password AS SYSDBA' be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character. Please see your operating system-specific documentation for information about special and reserved characters on your system. 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 for more information.

Getting Online Help

Export provides online help. Enter exp help=y on the command line to invoke it.


Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:23:43 1999

(c) Copyright 1999 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 files (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)              TRIGGERS     export triggers (Y)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
QUERY    select clause used to export a subset of a table
VOLSIZE  number of bytes to write to each tape volume

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport

Export terminated successfully without warnings.

Specifying Export Parameters on the Command Line or in the Parameter File

You can specify Export parameters in three ways: from a command-line entry, by allowing Export to prompt you for parameter values, or in the parameter file.

Command-Line Parameter Entry

You can specify all valid parameters and their values from the command line using the following syntax:

exp KEYWORD=value 

or

exp KEYWORD=(value1,value2,...,valuen)

Export Parameter Prompts

If you prefer to let Export prompt you for the value of each parameter, you can use the following syntax:

exp username/password

Export will display each parameter with a request for you to enter a value.

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

Export_start


ExpModes


ExpTTSOpts (tablespaces_spec)


ExpOpts


ExpFileOpts


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, LOGICAL ROWID, DATE, 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.

Example

This section shows an example of how to calculate buffer size.

Consider that the following table is created:

create table sample (name varchar(30), weight number);

The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.

Therefore, the maximum row size is 56 (30+2+22+2).

To perform array operations for 100 rows, a buffer size of 5600 should be specified.

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.

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 target data after an export has started.

If you specify CONSISTENT=N (the default), each table is usually exported in a single transaction. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. If a table is partitioned, each partition is exported as a separate transaction.

Therefore, if 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.

Restrictions

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

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.

For more information about direct path Exports, see Direct Path Export.

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 names of the export files. The default extension is .dmp, but you can specify any extension. Because Export supports multiple export files (see the parameter FILESIZE), you can specify multiple filenames to be used.

When Export reaches the value you have specified for the maximum FILESIZE, Export stops writing to the current file, opens another export file with the next name specified by the parameter FILE and continues until complete or the maximum value of FILESIZE is again reached. If you do not specify sufficient export filenames to complete the export, Export will prompt you to provide additional filenames.

FILESIZE

Export supports writing to multiple export files and Import can read from multiple export files. If you specify a value (byte limit) for the FILESIZE parameter, Export will write only the number of bytes you specify to each dump file.

When the amount of data Export must write exceeds the maximum value you specified for FILESIZE, it will get the name of the next export file from the FILE parameter (see FILE for more information) or, if it has used all the names specified in the FILE parameter, it will prompt you to provide a new export filename. If you do not specify a value for FILESIZE (note that a value of 0 is equivalent to not specifying FILESIZE), then Export will write to only one file, regardless of the number of files specified in the FILE parameter.

Note: If the space requirements of your export file exceed the available disk space, Export will abort and you will have to repeat the Export after making sufficient disk space available.

The FILESIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits.

Table 1-2 shows that the maximum size for dump files depends on the operating system you are using and on the version of the Oracle server that you are using.

Table 1-2 Maximum SIze for Dump Files
Operating System  Version of Oracle Server  Maximum Size 

Any  

Prior to 8.1.5  

2g  

32-bit  

8.1.5  

2g  

64-bit  

8.1.5 and later  

Unlimited  

32-bit with 32-bit files  

Any  

2g  

32-bit with 64-bit files  

8.1.6 and later  

Unlimited  

Note: The maximum value that can be stored in a file is dependent on your operating system. You should verify this maximum value in your operating system-specific documentation before specifying FILESIZE. You should also ensure that the file size you specify for Export is supported on the system on which Import will run.

The FILESIZE value can also be specified as a number followed by K (number of kilobytes). For example, FILESIZE=2K is the same as FILESIZE=2048. Similarly, M specifies megabytes (1024 * 1024) while G specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to obtain the final file size (FILESIZE=2048b is the same as FILESIZE=2048).

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 object grants. The object 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. Note that system privilege grants are always 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 for more information.

INDEXES

Default: Y

Specifies whether or not the Export utility exports indexes.

LOG

Default: none

Specifies a filename 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. If the user initiating the export is the DBA, multiple users may be listed.

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 Specifying Export Parameters on the Command Line or in the Parameter File.

QUERY

Default: none

This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter.

For example, if user SCOTT wants to export only those employees whose job title is SALESMAN and whose salary is greater than 1600, he could do the following (note that this example is UNIX-based):

exp scott/tiger tables=emp query=\"where job=\'SALESMAN\' and sal\<1600\"

Note: Because the value of the QUERY parameter contains blanks, most operating systems require that the entire strings where job=\'SALESMAN\' and sal\<1600 be placed in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded by an escape character. See your operating system-specific documentation for information about special and reserved characters on your system.

When executing this command, Export builds a SQL SELECT statement similar to this:

SELECT * FROM EMP where job='SALESMAN' and sal <1600; 
 

The QUERY is applied to all tables (or table partitions) listed in the TABLE parameter. For example, the following statement will unload rows in both EMP and BONUS that match the query:

exp scott/tiger tables=emp,bonus query=\"where job=\'SALESMAN\' and sal\<1600\" 

Again, the SQL statements that Export executes are similar to these:

SELECT * FROM EMP where where job='SALESMAN' and sal <1600;

SELECT * FROM BONUS where where job='SALESMAN' and sal <1600;

If a table is missing the columns specified in the QUERY clause, an error message will be produced and no rows will be exported for the offending table.

Restrictions

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.

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.

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 Oracle8i Concepts for information about the optimizer and the statistics it uses. See also the Import parameter RECALCULATE_STATISTICS and Importing Statistics.

In some cases, Export will place the precomputed statistics in the export file as well as the ANALYZE commands to regenerate the statistics.

However, the precomputed optimizer statistics will not be used at export time if:

TABLES

Default: none

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

The syntax you use to specify the preceding is in the form:

schemaname.tablename:partitionname
schemaname.tablename:subpartitionname

If you use tablename:partitionname, the specified table must be partitioned, and partitionname must be the name of one of its partitions or subpartitions.

See Example Export Session Using Partition-Level Export for several examples of partition-level exports.

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 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)

Note: 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 attaches a special meaning to 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.

TABLESPACES

Default: none

When TRANSPORT_TABLESPACE is specified as Y, use this parameter to provide a list of the tablespaces to be exported from the database into the export file.

See Transportable Tablespaces for more information.

TRANSPORT_TABLESPACE

Default: N

When specified as Y, this parameter enables the export of transportable tablespace metadata. See the Oracle8i Administrator's Guide and Oracle8i Concepts for more information.

TRIGGERS

Default: Y

Specifies whether or not the Export utility exports triggers.

USERID (username/password)

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.

USERID can also be:

username/password AS SYSDBA

or

username/password@instance AS SYSDBA

See Invoking Export As SYSDBA for more information. Your operating system may require you to treat AS SYSDBA as a special string, in which case the entire string would be enclosed in quotation marks.

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 Oracle8i Distributed Database Systems.

VOLSIZE

Specifies the maximum number of bytes in an export file on each volume of tape.

The VOLSIZE parameter has a maximum value equal to the maximum value that can be stored in 64 bits. See your operating system-specific documentation for more information.

The VOLSIZE value can be specified as number followed by K (number of kilobytes). For example, VOLSIZE=2K is the same as VOLSIZE=2048. Similarly, M specifies megabytes (1024 * 1024) while G specifies gigabytes (1024**3). B remains the shorthand for bytes; the number is not multiplied to get the final file size
(VOLSIZE=2048b is the same as VOLSIZE=2048).

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 and TABLE conflict 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.1.6.0.0 - Production on Wed Oct 6 15:23:51 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC 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 context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. 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$_PUSHED_TRANSACTIONS          0 rows exported
. . exporting table                  DEF$_TEMP$LOB          0 rows exported
. . exporting table        SQLPLUS_PRODUCT_PROFILE          0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table                            OL$          0 rows exported
. . exporting table                       OL$HINTS          0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. 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 synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
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.1.6.0.0 - Production on Wed Oct 6 15:24:25 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC character set and WE8DEC NCHAR character set
. exporting pre-schema procedural objects and actions
. 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 operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
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=schemaname.tablename.

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

> exp system/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 nonprivileged user can export only dependent objects for the specified tables that the user owns.

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

Example 1

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.1.6.0.0 - Production on Wed Oct 6 15:24:34 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC 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.1.6.0.0 - Production on Wed Oct 6 15:24:38 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC 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 and subpartitions of a table that you want to export.

Example 1

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

Parameter File Method


> exp scott/tiger parfile=params.dat

The params.dat file contains the following:

TABLES=(emp)
ROWS=y
Command-Line Method


> exp scott/tiger tables=emp rows=Y
Export Messages

Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:24:46 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC 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 table that is partitioned on employee name. There are two partitions, M and Z. As this example shows, if you export the table and specify a partition, only the specified partition is exported.

Parameter File Method
 

> exp scott/tiger parfile=params.dat

The params.dat file contains the following:

TABLES=(emp:m)
ROWS=y
Command-Line Method


> exp scott/tiger tables=emp:m rows=Y
Export Messages

Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:24:48 1999

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


Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC 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.

Example 3

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

Parameter File Method

> exp scott/tiger partfile=params.dat

The params.dat file contains the following:

TABLES=(emp:m,emp:sp4)
ROWS=Y
Command-Line Method

> exp scott/tiger tables=(emp:m, emp:sp4) rows=Y 
Export Messages

Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:24:48 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC character set and WE8DEC NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                            EMP 
. . exporting composite partition                    M 
. . exporting subpartition                           SP1         4 rows exported
. . exporting subpartition                           SP2         0 rows exported
. . exporting composite partition                    Z 
. . exporting subpartition                           SP4         1 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, you may have occasion to do so under specific circumstances at the request of Oracle technical support.

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.

The following is an example of the response to the Export interactive username prompt:

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:

Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:24:54 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Enter array fetch buffer size: 4096 > 
Export file: expdat.dmp > 
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 
Export grants (yes/no): yes > 
Export table data (yes/no): yes > 
Compress extents (yes/no): yes > 
Export done in WE8DEC 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 context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. 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$_PUSHED_TRANSACTIONS          0 rows exported
. . exporting table                  DEF$_TEMP$LOB          0 rows exported
. . exporting table        SQLPLUS_PRODUCT_PROFILE          0 rows exported
. about to export OUTLN's tables via Conventional Path ...
. . exporting table                            OL$          0 rows exported
. . exporting table                       OL$HINTS          0 rows exported
. about to export DBSNMP's tables via Conventional Path ...
. 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 synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
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 Enter.

Restrictions

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

Warning, Error, and Completion Messages

This section discusses the messages that Export issues in certain situations.

Log File

You can capture all Export messages in a log file, either by using the LOG parameter (see LOG) 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 nonfatal 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 nonfatal errors are known as warnings.

Export issues a warning whenever it encounters an invalid object.

For example, if a nonexistent 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

Export: Release 8.1.6.0.0 - Production on Wed Oct 6 15:25:15 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC character set and WE8DEC NCHAR character set

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 Oracle8i Error Messages 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 nonfatal 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 evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.

Direct path Export 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.

Figure 1-2 shows how data extraction differs between conventional path Export 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.

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


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.

In versions of SQL*Loader prior to 8.1.5, you could not use direct path export for tables containing objects and LOBs. If you tried to, they were exported using the conventional path method. This behavior has changed and you can now use direct path export for tables containing objects and LOBs.

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.

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. Using the NLS_LANG parameter, specify the session character set to be the same as that of the database before retrying the export.

Performance Issues

You may be able to improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path Export. Your exact performance gain 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 export file for each database block.

The following values are generally recommended for RECORDLENGTH:

Incremental, Cumulative, and Complete Exports

Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release. You should begin now to migrate to Oracle's Backup and Recovery Manager for database backups. See Oracle8i Operating System Backup and Recovery Guide for more information.

Restrictions:

  • You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y). Only users who have the role
    EXP_FULL_DATABASE can run incremental, cumulative, and complete Exports. This role contains the privileges needed to modify the system tables that track incremental exports. System Tables 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 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 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.

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 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 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.1.6.0.0 - Production on Wed Oct 6 15:25:47 1999

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

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.6.0.0 - Production
Export done in WE8DEC 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 context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. about to export OUTLN's tables via Conventional Path ...
. about to export DBSNMP'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 synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting information about dropped objects
. exporting statistics
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.

This table contains the following columns:

OWNER#

The userid of the schema containing the table.

NAME

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

TYPE

The type of the object (a code specifying INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, DIMENSION, OPERATOR, INDEXTYPE, 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 the 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 the 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 individual who initiated the export.

When you export with the parameter INCTYPE = COMPLETE, all 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

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

Additional Information: 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.

Character Set and NLS Considerations

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

Character Set Conversion

In conventional mode, the Export utility writes to the export file using the character set specified for the user session, such as 7-bit ASCII, IBM Code Page 500 (EBCDIC), or an Oracle NLS character set like JA16EUC, converting from the database server character set as necessary. Import then 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.

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.

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.

Both Export and Import provide descriptions of any required character set conversion before exporting or importing the data.

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


Note:

If the export character set has a different sorting order than the import character set, then tables that are partitioned on character columns may yield unpredictable results. For example, consider the following table definition, which is produced on a database having an ASCII character set:

create table partlist 
   ( 
   part     varchar2(10), 
   partno   number(2) 
   ) 
partition by range (part) 
  ( 
  partition part_low values less than ('Z') 
    tablespace tbs_1, 
  partition part_mid values less than ('z') 
    tablespace tbs_2, 
  partition part_high values less than 
(MAXVALUE) 
    tablespace tbs_3 
  ); 

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

When this table is imported into a database based upon an EBCDIC character set, all of the rows in the part_mid partition will migrate to the part_low partition because 'z' comes before 'Z' in EBCDIC character sets. To obtain the desired results, the owner of partlist will need to repartition the table following the import.  


For more information, see the Oracle8i National Language Support Guide.

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 automatically converts the data to the national character set of the Import server.

For more information, see the Oracle8i National Language Support Guide.

Multibyte Character Sets and Export and Import

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

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.

Instance Affinity and Export

If you use instance affinity to associate jobs with instances in databases you plan to import/export, you should refer to the information in the Oracle8i Administrator's Guide, Oracle8i Reference, and Oracle8i Parallel Server Concepts and Administration for information about use of instance affinity with the Import/Export utilities. If you are using both release 8.0 and 8.1, refer to Oracle8i Migration for possible compatibility issues.

Fine-Grained Access Support

You can export tables with fine-grained access policies enabled. When doing so, keep the following considerations in mind:

Considerations in Exporting Database Objects

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

Exporting Sequences

If transactions continue to access sequence numbers during an export, sequence numbers can be skipped. The best way to ensure that sequence numbers are not skipped is to ensure that the sequences are not accessed during the export.

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 and LOB Datatypes

On export, LONG datatypes are fetched in sections. However, enough memory must be available to hold all of the contents of each row, including the LONG data.

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 Offline Bitmapped Tablespaces

If the data you are exporting contains offline bitmapped tablespaces, Export will not be able to export the complete tablespace definition and will display an error message. You can still import the data; however, you must first create the offline bitmapped tablespaces before importing to prevent DDL commands that may reference the missing tablespaces from failing.

Exporting Directory Aliases

Directory alias definitions are included only in a full database mode Export. To move a database to a new location, the database administrator must update the directory aliases to point to the new location.

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 (DBA) must move the directories containing the specified files to a new location where they can be accessed.

Exporting Object Type Definitions

In all Export modes, the Export utility includes information about object type definitions used by the tables being exported. The information, including object name, object identifier, and object geometry, is needed to verify that the object type on the target system is consistent with the object instances contained in the export file. This ensures that the object types needed by a table are created with the same object identifier at import time.

Note, however, that in table, user, and tablespace mode, the export file does not include a full object type definition needed by a table if the user running Export does not have execute access to the object type. In this case, only enough information is written to verify that the type exists, with the same object identifier and the same geometry, on the import target system.

The user must ensure that the proper type definitions exist on the target system, either by working with the DBA to create them, or by importing them from full database or user mode exports performed by the DBA.

It is important to perform a full database mode export regularly to preserve all object type definitions. Alternatively, if object type definitions from different schemas are used, the DBA should perform a user mode export of the appropriate set of users. For example, if 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 Nested Tables

Inner nested table data is exported whenever the outer containing table is exported. Although inner nested tables can be named, they cannot be exported individually.

Exporting Advanced Queue (AQ) Tables

Queues are implemented on tables. The export and import of queues constitutes the export and import of the underlying queue tables and related dictionary tables. You can export and import queues only at queue table granularity.

When you export a queue table, both the table definition information and queue data are exported. Because the queue table data is exported as well as the table definition, the user is responsible for maintaining application-level data integrity when queue table data is imported.

See the Oracle8i Application Developer's Guide - Advanced Queuing for more information.

Exporting Synonyms

You should be cautious when exporting compiled objects that reference a name used as a synonym and as another object. Exporting and importing these objects will force a recompilation that could result in changes to the object definitions.

The following example helps to illustrate this problem:

create public synonym emp for scott.emp;

connect blake/paper;
create trigger t_emp before insert on emp begin null; end;
create view emp as select * from dual;

If the database in the preceding example were exported, the reference to emp in the trigger would refer to blake's view rather than to scott's table. This would cause an error when Import tried to reestablish the t_emp trigger.

Transportable Tablespaces

The transportable tablespace feature enables you to move a set of tablespaces from one Oracle database to another.

To move or copy a set of tablespaces, you must make the tablespaces read-only, copy the datafiles of these tablespaces, and use Export/Import to move the database information (metadata) stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying binary files, such as the operating system copying facility, binary-mode FTP, or publishing on CD-ROMs.

After copying the datafiles and exporting the metadata, you can optionally put the tablespaces in read/write mode. See Transportable Tablespaces for more information about importing from an export file that contains transportable tablespace metadata.

Export provides the following parameter keywords you can use to enable export of transportable tablespace metadata.

See TRANSPORT_TABLESPACE and TABLESPACES for more information.

Additional Information: See the Oracle8i Administrator's Guide for details about managing transportable tablespaces. For an introduction to the transportable tablespaces feature, see Oracle8i Concepts.

Using Different Versions of Export

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

Using a Previous Version of Export

In general, you can use the Export utility from any Oracle release 7 to export from an Oracle8i server and create an Oracle release 7 export file. (This procedure is described in Creating Oracle Release 7 Export Files from an Oracle8i Database.)

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

Whenever a lower version Export utility runs with a higher version of the Oracle database server, categories of database objects that did not exist in the lower version are excluded from the export. (See Excluded Objects for a complete list of Oracle8i objects excluded from an Oracle release 7 Export.)

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

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

Using a Higher Version of Export

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

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

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 database server.

Creating Oracle Release 8.0 Export Files from an Oracle8i Database

You do not need to take any special steps to create an Oracle Release 8.0 export file from an Oracle8i database; however, certain features are not supported.

Creating Oracle Release 7 Export Files from an Oracle8i Database

You can create an Oracle release 7 export file from an Oracle8i database by running Oracle release 7 Export against an Oracle8i 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 Oracle8i 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 new Oracle8i objects listed in Creating Oracle Release 8.0 Export Files from an Oracle8i Database or the following Oracle8 objects:

Enterprise Manager and Oracle7 Export

If you want to use Enterprise Manager to export 7.3.2 databases, you must use Enterprise Manager release 1.4.0 or higher.



Go to previous page
Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index