Release 8.0
A58244-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Utilities Release 8.0 A58244-01 |
|
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:
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.
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.
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.
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
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.
This section describes how to use the Export utility, including what you need to do before you begin exporting and how to invoke Export.
To use Export, you must run the script CATEXP.SQL or CATALOG.SQL (which runs CATEXP.SQL) after the database has been created.
Note: The actual names of the script files depend on your operating system. The script file names and the method for running them are described in your Oracle operating system-specific documentation.
CATEXP.SQL or CATALOG.SQL needs to be run only once on a database. You do not need to run it again before you perform the export. The script performs the following tasks to prepare the database for Export:
Before you run Export, ensure that there is sufficient disk or tape storage space to which to write the export file. If there is not enough space, Export terminates with a write-failure error.
You can use table sizes to estimate the maximum space needed. Table sizes can be found in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables:
select sum(bytes) from user_segments where segment_type='TABLE';
The result of the query does not include disk space used for data stored in LOB (large object) columns.
See the Oracle8 Reference for more information about dictionary views.
You can invoke Export in one of the following ways:
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.
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.
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.
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 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.
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.
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.
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.
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 |
|
3 |
Ends export of TAB:P1 |
|
|
4 |
Exports TAB:P2 |
|
If the export uses CONSISTENT=Y, none of the updates by USER2 are written to the export file.
If the export uses CONSISTENT=N, the updates to TAB:P1 are not written to the export file. However, the updates to TAB:P2 are written to the export file because the update transaction is committed before the export of TAB:P2 begins. As a result, USER2's transaction is only partially recorded in the export file, making it inconsistent.
If you use CONSISTENT=Y and the volume of updates is large, the rollback segment will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.
Keep in mind the following points about using CONSISTENT=Y:
For example, export the EMP and DEPT tables together in a consistent export, and then export the remainder of the database in a second pass.
The "snapshot too old" error occurs when rollback space has been used up, and space taken up by committed transactions is reused for new transactions. Reusing space in the rollback segment allows database integrity to be preserved with minimum space requirements, but it imposes a limit on the amount of time that a read-consistent image can be preserved.
If a committed transaction has been overwritten and the information is needed for a read-consistent view of the database, a "snapshot too old" error results.
To avoid this error, you should minimize the time taken by a read-consistent export. (Do this by restricting the number of objects exported and, if possible, by reducing the database transaction rate.) Also, make the rollback segment as large as possible.
Note: You cannot specify CONSISTENT=Y with an incremental export.
Default: Y
Specifies whether or not the Export utility exports table constraints.
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.
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.
Default: expdat.dmp
Specifies the name of the export file. The default extension is .dmp, but you can specify any extension.
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.
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.
Default: N
Displays a help message with descriptions of the Export parameters.
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.
Default: Y
Specifies whether or not the Export utility exports indexes.
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.
Default: undefined
Indicates that the Export is a user-mode Export and lists the users whose objects will be exported.
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.
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.
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.
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.
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.
Default: Y
Specifies whether or not the rows of table data are exported.
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.
Default: undefined
Specifies that the Export is a table-mode Export and lists the table names and partition names to export. You can specify the following when you specify the name of the table:
If you use 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 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.
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.
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.
The following examples show you how to use the command line and parameter file methods in the full database, user, and table modes.
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.
>
exp system/manager parfile=params.dat
The params.dat file contains the following information:
FILE=dba.dmp GRANTS=y FULL=y ROWS=y
>
exp system/manager full=Y file=dba.dmp grants=Y rows=Y
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.
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.
>
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
>
exp scott/tiger file=scott.dmp owner=scott grants=Y rows=Y compress=y
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.
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.
In this example, a DBA exports specified tables for two users.
>
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
>
exp system/manager tables=(scott.emp,blake.dept) grants=Y indexes=Y
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.
In this example, user BLAKE exports selected tables that he owns.
>
exp blake/paper parfile=params.dat
The params.dat file contains the following information:
FILE=blake.dmp TABLES=(dept,manager) ROWS=Y COMPRESS=Y
>
exp blake/paper file=blake.dmp tables=(dept, manager) rows=y compress=Y
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.
In partition-level export, you can specify the partitions of a table that you want to export.
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.
> exp scott/tiger parfile=params.dat
The params.dat file contains the following:
TABLES=(emp) ROWS=y
> exp scott/tiger tables=emp rows=Y
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.
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.
>
exp scott/tiger parfile=params.dat
The params.dat file contains the following:
TABLES=(emp:m) ROWS=y
>
exp scott/tiger tables=emp:m rows=Y
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.
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.
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].
Keep in mind the following points when you use the interactive method:
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.
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.
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.
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.
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 DBAAdditional Information: Messages are documented in the Oracle8 Messages manual and in your Oracle operating system-specific documentation.
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."
Export provides two methods for exporting table data:
Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into a buffer cache, and rows are transferred to the evaluation buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.
Direct path Export extracts data much faster than a conventional path export. Direct path Export achieves this performance gain by reading data directly, bypassing the SQL Command Processing layer and saves on data copies whenever possible.
For added performance, you can set the database to direct read mode. This eliminates contention with other users for database resources because database blocks are read into the Export session's private buffer, rather than into a public buffer cache. For more information about direct read mode, see the Oracle8 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.
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.
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.
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:
When using direct path Export, set the RECORDLENGTH parameter equal to the DB_BLOCK_SIZE database parameter, so that each table scan returns a full database block worth of data. If the data does not fit in the export I/O buffer, the Export utility performs multiple writes to the disk for each database block.
The following values are generally recommended for RECORDLENGTH:
Note: Other factors also affect the use of direct read mode. See the Oracle8 Administrator's Guide for more information.
The following restrictions apply when executing a direct path Export:
Incremental, cumulative, and complete Exports provide time- and space-effective backup strategies. This section shows how to set up and use these export strategies.
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.
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.
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-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.
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.
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.
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.
Incremental and cumulative Exports help solve the problems faced by administrators who work in environments where many users create their own tables. For example, administrators can restore tables accidentally dropped by users.
The benefits of incremental and cumulative Exports include:
These benefits result because not all tables have changed. As a result, the time and space required for an incremental or cumulative Export is shorter than for a full database Export.
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:
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.
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:
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.
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.
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.
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.
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.
The table SYS.INCVID contains one column for the EXPID of the last valid export. This information determines the EXPID of the next export.
This section describes factors to take into account when you use Export and Import 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.
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.
This section describes the behavior of Export and Import with respect to National Language Support (NLS).
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.
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.
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.
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.
The following sections describe points you should take into consideration when you export particular database objects.
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.
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.
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.
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.
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.
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.
In all Export modes, the Export utility includes information about object type definitions used by the tables being exported. The information, including object name and object identifier, is needed to verify that the object type on the target system is consistent with the object instances contained in the dump file.
In full database or user mode, the Export utility writes all object type definitions to the export file before it writes the table definitions.
In all modes, the Export utility also writes object type definitions for a table to the export file immediately preceding the table definition. This ensures that the object types needed by a table are created with the same object identifier at import time. If the object types already exist on the importing system, this allows Import to verify that the object identifiers are the same.
Note however, that the information preceding the table definition does not always include all the object type definitions needed by the table. Note the following points about the information preceding the table definition:
The user must ensure that the proper type definitions exist on the target system, either by working with the DBA to create them, or by importing them from full database or user mode exports performed by the DBA.
The user must be cautious when performing table mode Import because the full definitions of object types from other schemas are not included in the information preceding the table.
It is important to perform a full database mode Export regularly to preserve all object type definitions. Alternatively, if object type definitions from different schemas are used, the DBA should perform a user mode Export of the proper set of users. For example, if Scott's table TABLE1 contains a column on Blake's type Type1, the DBA should perform a user mode Export of both Blake and Scott to preserve the type definitions needed by the table.
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.
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.
This section describes the general behavior and restrictions of running an Export version that is different from Oracle8.
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.
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.
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.
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:
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.
