| Oracle7 Server Utilities User's Guide |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Utilities User's Guide | Library |
Product |
Contents |
Index |
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 can be stored independently of the database or read into another Oracle database, using the Import utility (described in Chapter 2).
This chapter covers the following topics:
It also provides several example export sessions that show how the export utility works in certain situations.
The concept behind Export's basic function is quite simple: extract the object definitions and table data from an Oracle database and store them in an Oracle-binary format export file that is located typically on tape or on disk. Export files can be used to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.
When Export is run against an Oracle database, objects, such as tables, are extracted followed by their related objects, like indexes, comments, and grants, if any, then written to the Export file. See Figure 1 - 1.
Figure
1 - 1. Exporting a Database
Because Export files are saved in Oracle-binary format, export files cannot be read by utilities other than Import. Similarly, Import can read files written by Export, but cannot read files in other formats. If you need to load data from ASCII fixed-format or delimited files, see Part II of this manual for information about SQL*Loader.
Export writes export files using the character set specified for the user session; for example, 7-bit ASCII or IBM Code Page 500 (EBCDIC).
Note: Import automatically maps the data to the character set of its host system or to the character set specified for the user session if it is different from that used in the export file.
Additional Information: For the Trusted Oracle7 Server, export files are labeled by the operating system.
See your platform-specific Trusted Oracle7 Server documentation for details about file naming procedures.
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 have created a synonym for it.
Since files created by Export are stored in Oracle-binary format, they cannot be read by any Oracle7 Server utility or product other than Import.
You can, however, display the contents of an export file by using the
Import SHOW option described![[*]](jump.gif){kind=small}
.
In addition, export files can be read only by Import. You cannot
use them to transfer data to non-Oracle systems.
Export 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.
Oracle Corporation recommends that you capture all of Export's messages in a log file either by using the LOG parameter (See page 1 - 15 for the log file specification) or, for those systems that permit it, by redirecting Export's output to a file. This file will contain detailed information about successful loads, and any errors that may occur.
Additional Information: Refer to your Oracle operating system-specific documentation for information on redirecting output.
Export will 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 will issue a warning whenever an invalid object is encountered. That is, if a non-existent table was specified as part of a table-mode export, then all other tables would be exported, after which the export would issue a warning and terminate successfully, as shown in the following listing:
exp scott/tiger tables=xxx,emp
...
About to export specified tables ...
EXP-00011: SCOTT.XXX does not exist
EXP-00222:
System error message 2
. exporting table EMP 10 rows
exported
Export terminated successfully with warnings.
Some errors are fatal, and will 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, you see the following message if you attempt to run Export without having created the necessary Export views:
EXP-00024: Export views not installed, please notify your DBA
Additional Information: Messages are documented in the Oracle7 Server Messages manual and in your Oracle operating system-specific documentation.
When Export completes without errors, the message "Export terminated successfully without warnings" is displayed or logged. If one or more non-fatal errors occurred, but Export was able to continue to completion, then the message "Export terminated successfully with warnings" is displayed or logged. If a fatal error occurs, Export terminates immediately with the message "Export terminated unsuccessfully".
The database objects that are exported depend on the mode you choose. All users have at least two choices of export mode; a user with the EXP_FULL_DATABASE role (a privileged user) has three choices:
Exports specified tables in the user's schema, rather than all tables. A privileged user can qualify the tables by specifying the schema containing them. The default is to export all tables belonging to the user doing the export.
Exports in table mode do not include cluster definitions. As a result, the data is imported into unclustered tables. Thus, you can use Export to uncluster tables when there is not enough free space in your database.
Exports all objects in a user's schema (such as tables, data, grants, and indexes). A privileged user exporting in user mode can export all objects in the schemas of a specified set of users.
Only users with the EXP_FULL_DATABASE role can export in this mode. All objects in the database are exported, except those in the schema of SYS. (Objects owned by SYS are generated during database creation for internal and administrative use. Because they are not exported, you should not create a user table or any other object under SYS.)
See "Export Parameters" for more information.
Table 1 - 1 shows the objects that are exported in each mode and the order in which they are exported:
If transactions continue to access sequence numbers during an export, sequence numbers can be skipped. This section describes the circumstances under which it can happen. 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 import, LONGs require contiguous memory. Therefore, it is not always possible to export LONG columns from one operating system and import them on another system. Even on the same system, memory limitations may make it impossible to import very large LONG columns that were successfully exported on that system.
Warning: LONG columns can be up to 2 gigabytes in length. Because they can be exported in sections, Oracle LONGs are always exportable.
There are additional steps and considerations when you are exporting data from a Trusted Oracle7 Server. The Trusted Oracle7 Server Administrator's Guide contains more guidelines for using Export with the Trusted Oracle7 Server.
This section describes factors to take into account when using Export and Import across a network.
Since the export file is in binary format, when transferring it across a network, be sure to use a protocol that supports binary transfers to prevent corruption of the file. For example, using FTP or a similar file transfer protocol, transmit the file in binary mode. Transmitting export files in character mode causes errors when the file is imported.
By eliminating the boundaries between different machines and operating systems on a network, SQL*Net provides a distributed processing environment for Oracle7 Server products. SQL*Net lets you export and import over a network. For example, running Export locally, you can write data from a remote Oracle database into a local export file. Running Import locally, you can read data into a remote Oracle database.
To use Export or Import with SQL*Net, include the @connect_string when entering the EXP or IMP command. For the exact syntax of this clause, see the user's guide for your SQL*Net protocol. For more information on SQL*Net, see Understanding SQL*Net. If you use Oracle Names, see also the Oracle Names Administrator's Guide.
This section describes behavior of Export and Import with respect to National Language Support (NLS).
Export writes export files using the character set specified for the user session; for example, 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, then the data is automatically converted to the character encoding scheme specified for the user session during import, as specified by the NLS_LANG parameter. After the data has been 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.
For multi-byte character sets, conversion is only performed if the length of the character string cannot change as a result of the conversion.
For more information, refer to the National Language Support section of the Oracle7 Server Reference.
Some eight-bit characters can be "dropped" when importing an eight-bit character set export file. This occurs if the client machine has a native seven-bit character set, or the NLS_LANG operating system environment parameter is set to a seven-bit character set. "dropped" implies that some eight-bit characters are converted to seven-bit equivalents. Typically, accented European characters lose their accent mark.
This situation occurs because the eight-bit characters in the export file are converted to seven-bit characters via the client application. When sent to the database, the seven-bit characters are converted by the server into eight-bit characters. To avoid this situation, it is necessary to turn off one of these conversions. One possibility is to set NLS_LANG to the character set of the export file data.
When importing an Oracle version 6 export file with a character set
different from that of the native operating system or the setting for NLS_LANG,
you need to set the CHARSET import parameter to indicate the character
set of the export file data. The CHARSET parameter is described .
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 a character set with the same character length. That is, the export character set and the import character set must have a 1:1 ratio.
To use Export, the script CATEXP.SQL or CATALOG.SQL (which runs CATEXP.SQL) must be run after the database has been created.
Additional Information: 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 only needs to be run once. Once run, it need not be run before future exports. The following operations are performed to prepare the database for Export.
Before running Export, ensure that there is enough disk or tape storage space to write the export file to. If there is not enough space, Export will terminate 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 in the Oracle data dictionary (see the Oracle7 Server Administrator's Guide for more information).
You can invoke Export in three ways:
The username and password can also be specified in the parameter file, although for security reasons, this is not recommended.
If you omit username/password, Export will prompt you for it.
Export provides online help. Enter EXP HELP=Y on the command line to see a help screen like the one shown in Figure 1 - 2.
Figure
1 - 2. Export Help Screen
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:
KEYWORD=value
or
KEYWORD=(value) or KEYWORD=(value1, value2, ...)
The following is an example of 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 and operating system file naming conventions will apply. See your Oracle operating system-specific documentation for more information.
You can add comments to the parameter file by preceding them with the # sign. All characters to the right of the # sign are ignored.
Table names specified on the command line cannot include a # sign, unless the table name is enclosed in quotation marks. Similarly, in the parameter file, if a table has a # sign in the name, the rest of the line is interpreted as a comment unless the table name is enclosed in quotation marks.
Additional Information: Some operating systems require single vs. double quote marks, or vice versa. See your Oracle operating system-specific documentation.
For example, if the parameter file contains the line:
TABLES=(EMP#, DEPT, MYDATA)
nothing on the line after EMP# is seen as input by Export. As a result, DEPT and MYDATA are not exported.
Attention: When the name is specified in quotation marks, it is case-sensitive. The name must therefore exactly match the table name stored in the database. By default, database names are stored as uppercase.
The parameters listed below can be specified in the parameter file. They are described in detail in the remainder of this section.
| USERID | RECORDLENGTH |
| BUFFER | INCTYPE |
| FILE | RECORD |
| GRANTS | HELP |
| INDEXES | LOG |
| ROWS | CONSISTENT |
| CONSTRAINTS | STATISTICS |
| COMPRESS | FEEDBACK |
| FULL | MLS* |
| OWNER | MLS_LABEL_FORMAT* |
| TABLES | |
| *Trusted Oracle7 Server parameter |
Default: operating system dependent
The parameter BUFFER determines the maximum number of rows in an array fetched by Export by specifying the size of the buffer. You can calculate the buffer size as follows:
buffer_size = rows_in_array * maximum_row_size
buffer_size is the size in bytes of the buffer into which data rows are fetched. If zero is specified, or if rows contain LONG data, only one row at a time is fetched.
Additional Information: See your Oracle operating system-specific documentation to determine the default value for this parameter.
Specifies how Export/Import will manage the initial extent for table data.
The default, COMPRESS=Y, causes Export to flag the table data for consolidation into one initial extent upon Import.
If COMPRESS=N is specified, Export will sum the lengths of all current extents on export and Import will use that value as the size of the initial extent for the imported table data. Note that, when a table has had many deletes, this size may be much larger than the table actually requires.
Note: Although used extents are consolidated into one extent upon import, you must request consolidation when exporting, because your request causes the table definition to be exported differently. Thus, if you request consolidation when exporting, you can import the data in consolidated form only.
Specifies whether Export will use the SET TRANSACTION READ ONLY statement to insure that the export is consistent to a single point in time. Specifying CONSISTENT=Y is important when other applications will be updating the database after an export has started because these transactions will be rolled back, if necessary.
If you specify CONSISTENT=Y, a rollback segment must be retained for the duration of the export to allow backing out of the effects of any uncommitted transactions when a table is exported. Note that, if the volume of updates is large, the rollback segment will itself be large. In addition, the export of each table will be slower, since the rollback segment must be scanned for uncommitted transactions.
Note: CONSISTENT cannot be used with an incremental export.
Suggestion: To minimize the time and space required for such exports, tables that need to remain consistent should be exported separately from those that do not.
For example, export the EMP and DEPT tables together in a consistent export, then export the remainder of the database in a second pass.
Exporting the minimum number of objects that must be guaranteed consistent helps to reduce the chances of encountering a "snapshot too old" error.
This 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, then a "snapshot too old" error results.
To avoid this error, minimize the time taken by a read-consistent export (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.
A flag to indicate whether to export table constraints.
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.
Performance can also be improved 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.
For more information about direct path exports, see page 1 - 32.
Default: 0 (zero)
Specifies that Export should display a progress meter in the form of a dot for x number of rows exported. For example, were you to specify FEEDBACK=10, Export would display a dot each time 10 rows had been exported. The FEEDBACK value applies to all tables being exported, it cannot be set on a per table basis.
The name of the export file. The default extension is .DMP, but you can specify any extension.
Specifies whether Export should export the entire database or not. Specify FULL=Y to export in full database mode (you must have the EXP_FULL_DATABASE role enabled to do this).
Specifies whether grants should be exported or not.
Displays a help message with descriptions of the Export parameters.
Specifies the type of incremental export. Options are COMPLETE, CUMULATIVE, and INCREMENTAL. See the section in this chapter called "Incremental, Cumulative and Complete Exports" for a description of these options.
Specifies whether indexes should be exported or not.
Specifies a file name to receive informational and error messages. For example:
EXP system/manager LOG=export.log
If this parameter is specified, messages are logged in the log file and displayed via the terminal display.
The Export utility can be used on any Trusted Oracle7 database to produce a normal export file. In DBMS Mandatory Access Control (MAC) mode, the MLS parameter can also be specified. With this parameter, the Multi-Level Security (MLS) labels on each row and object in the database are included in the export file. This parameter is further described in the Trusted Oracle7 Server Administrator's Guide.
This parameter allows you to override the default format specified in the database for human-readable MLS labels. The format you specify will be used in the export file instead of the format defined by the database initialization parameter. This parameter is further described in the Trusted Oracle7 Server Administrator's Guide.
Specifies a list of usernames whose objects will be exported. Specify OWNER=userlist to export in user mode.
A flag to indicate whether to record an incremental or cumulative export in database tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. See page 1 - 31.
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.
Additional Information: See your Oracle operating system-specific documentation for the system being exported to determine the proper value, or when you want to create a file with a different record size.
Specifies whether the rows of table data should be exported or not.
Specifies the type of database optimizer statistics to generate when the exported data is imported later. Options are ESTIMATE, COMPUTE, and NONE. See Chapter 13 of the Oracle7 Server Concepts manual for information about the optimizer.
Specifies list of table names to export. Specify TABLES=tablelist to export in table mode.
Storage Parameters: Tables are exported using the current storage parameters. Unless ALTER TABLE command has been executed to change them, these are the parameters in effect when the tables were created.
Specifies the username/password of the user initiating the export.
Certain parameters can conflict with each other. For example, specifying TABLES can conflict with an OWNER specification, the following command will cause Export to terminate with an error:
EXP system/manager OWNER=jones TABLES=scott.emp
Similarly, OWNER 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.
Starting Export from the command line with no arguments will initiate Export's interactive mode. Note that the interactive mode does not provide prompts for all of Exports functionality. It is provided only for backward compatibility.
You may not see all the 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]. To end your input, enter a period (.) followed by [RETURN].
If you have not specified a username/password on the command line, EXPORT first prompts you for this information. Then the prompts described below are displayed.
Default: 4096
The value specified here determines the space available to buffer rows. Export uses the array fetch program interface call to speed performance. The default is usually adequate, but the buffer must be at least as large as the longest row. If you enter zero, only one row at a time is fetched.
Default: EXPDAT.DMP
Specify the name of the export file (the default is EXPDAT.DMP). If you do not specify an extension, the file extension of the export file will default to .DMP.
Default: U
Specify either (T)able or (U)ser mode. Privileged users can also choose (E)ntire database mode. The options above relate to the Export parameters TABLES, OWNER and FULL respectively.
Specify (Y)es to export grants or (N)o. The grants that will be exported depend on whether you are in FULL DATABASE mode 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: Y
Specify (Y)es, to export data in the tables with the table definitions. Specify (N)o, to export only the table definitions.
Default: Y
Specify (Y)es, to have the data for each exported table's used extents consolidated into one large initial extent when imported. Specifying (N)o, will cause all tables to be created with the storage values in effect when the table was exported.
Note: Although used extents are consolidated into one extent upon import, you must request consolidation when exporting, because your request causes the table definition to be exported differently. Thus, if you request consolidation when exporting, you can import the data in consolidated form only.
Default: none
Specify the name of the schema to export. To indicate "no user" (and terminate the current Export session), press [RETURN].
Default: none
Specify the name of the table to export. Entering a null table list causes all tables in the schema to be exported.
If no schema prefix is specified, Export defaults to the exporter's schema or the schema containing the last table exported in the current session.
For example, if BETH is a privileged user exporting in table mode, then Export assumes that all tables are in BETH's schema until another schema is specified. Only a privileged user (someone with BACKUP ANY TABLE privileges) can export tables in another user's schema.
The following example export session shows you how the command line and interactive methods in the full database, user, and table modes might be used.
Only users with the DBA role, the EXP_FULL_DATABASE role, or the BECOME USER privilege can export in full database mode. In this example, an entire database is exported to file DBA.DMP with all GRANTs and all data.
Command Line Method
> exp system/manager full=Y file=dba.dmp
Interactive Method
> exp system/manager
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP> dba.dmp
E(ntire database), U(sers), T(ables): U> e
Export grants (Y/N): Y> y
Export table data (Y/N): Y> y
Compress extents (Y/N): Y> y
Export Messages
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 job queues
. exporting refresh groups and children
. exporting cluster definitions
. about to export SYSTEM's tables ...
. .exporting table DEF$_CALL 0 rows exported
. .exporting table DEF$_CALLDEST 0 rows exported
. .exporting table DEF$_DEFAULTDEST 0 rows exported
. .exporting table DEF$_ERROR 0 rows exported
. .exporting table DEF$_SCHEDULE 0 rows exported
. .exporting table DEF$_TRAN 0 rows exported
. .exporting table DEF$_TRANDEST 0 rows exported
. about to export SCOTT's tables ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 5 rows exported
. . exporting table EMP 14 rows exported
. . exporting table SALGRADE 5 rows exported
. about to export ADAMS's tables ...
. about to export JONES's tables ...
. about to export CLARK's tables ...
. about to export BLAKE's tables ...
. exporting referential integrity constraints
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting triggers
. exporting default and system auditing options
Export terminated successfully without warnings.
Exports in user mode can back up one database user. For example, aDBA may want to back up the tables of a deleted user 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.
This example shows user SCOTT exporting all his tables in user mode.
Command Line Method
exp scott/tiger file=scott.dmp
Interactive Method
exp scott/tiger
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP> scott.dmp
U(sers), or T(ables): U> u
Export grants (Y/N): Y> y
Export table data (Y/N): Y> y
Compress extents (Y/N): Y> y
Export Messages
About to export SCOTT's objects ...
. exporting job queues
. exporting refresh groups and children
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. exporting stored procedures
. about to export SCOTT's tables ...
. exporting table BONUS 0 rows exported
. exporting table DEPT 7 rows exported
. exporting table EMP 22 rows exported
. exporting table PROJ 0 rows exported
. exporting table SALGRADE 5 rows exported
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
Export terminated successfully without warnings.
In table mode, you can export table data or the table definitions. (If no data is 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 this mode to export tables from any user's schema by specifying TABLES=(schema.table). If schema is not specified, it defaults to the previous schema from which an object was exported. If there is not a previous object, it defaults to the exporter's schema. In the following example, schema defaults to SYSTEM 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 he or she owns.
Exports in table mode do not include cluster definitions. As a result, the data is imported into unclustered tables. Thus, you can use Export to uncluster tables when there is not enough free space in your database.
In this example, a DBA exports specified tables for two users.
Command Line Method
> exp system/manager tables=(mort.bar, mary.app) grants=Y indexes=Y
Interactive Method
> exp system/manager
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP > (RETURN)
E(ntire database), U(sers), T(ables): U> t
Export table data (Y/N): Y> (RETURN)
Compress extents (Y/N): Y> y
Export Messages
About to export specified tables ...
Table Name: mort.bar [interactive session only]
Current user changed to MORT
. exporting table BAR 2355 rows exported
Table Name: mary.app [interactive session only]
Current user changed to MARY
. exporting table APP 14947 rows exported
Table Name: (RETURN) [interactive session only]
[Export writes file]
In this example, user LEWIS exports selected tables that he owns.
Command Line Method
> exp lewis/newyork file=lew.dmp tables=(credits, debits)
Interactive Method
exp lewis/newyork
...
Enter array fetch buffer size: 4096 > (RETURN)
Export file: EXPDAT.DMP> lew.dmp
U(sers), T(ables): U> T
Export table data (Y/N): Y> y
Compress extents (Y/N): Y> y
Export Messages
About to export specified tables ...
Table Name: credits [interactive session only]
. exporting table CREDITS 423 rows exported
Table Name: debits [interactive session only]
. exporting table DEBITS 423 rows exported
Table Name: (RETURN) [interactive session only]
[Export writes file]
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. Those tables are discussed at the end of this section.
If you choose to use cumulative and incremental exports, it is advised that you periodically perform a complete export to create a base backup. Following the complete export, the administrator can take frequent incremental exports and occasional cumulative exports. After a givenperiod of time, the cycle should begin 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's definition and all its data, not just the changed rows. Incremental exports are typically done more often than cumulative or complete exports.
Figure 1 - 3 shows an incremental export at time 1, after 3 tables have been modified. Only the modified tables (and associated indexes) are exported.
Figure
1 - 3. Incremental Export at Time 1
Figure 1 - 4 shows another incremental export at time 2, after 2 tables have been modified. Table 3 was modified a second time, so it is exported at time 2 as well as at time 1.
Figure
1 - 4. Incremental Export at Time 2
Note: Incremental exports cannot be specified as read-consistent.
A cumulative export backs up tables that have changed since the last cumulative or complete export. In essence, 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 1, after 3 tables have been modified. The modified tables (and associated indexes) are exported. This export is equivalent to an incremental export.
Figure
1 - 5. Cumulative Export at Time 1
Figure 1 - 6 shows a cumulative export at time 2, after 2 tables have been modified. This time, the two tables modified at time 2 are exported, and the tables modified at time 1 are exported as well.
Figure
1 - 6. Cumulative Export at Time 2
This cumulative export file is a combination of the incremental exports from time 1 and time 2, except that table 3 (which was modified at both times) only occurs 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 - 7 shows a complete export at time 2. 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. So the time and space required for an incremental or cumulative export is shorter than for a full database export.
The following scenario 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
X I I I I I C I I I I I I I C I I I I I X
To restore through day 18, you would first import the system information from the incremental export taken on day 18. You would then import the data from:
The incremental exports on days 2 through 6 can be discarded on day 7, when the cumulative export is done, because it incorporates all of them. Similarly, the incremental exports on days 8 through 14 can be discarded after the cumulative export on day 15.
Note: The section "Incremental, Cumulative and Complete Imports" on page 2-29 explains how to do an incremental import.
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 that you can export. Besides the data itself, there are indexes, grants, audits, and comments.
The entire grant structure for tables or views is exported with the underlying base table(s). Indexes are exported with their base table, regardless of who created the index.
Export automatically exports a read-consistent view of a table, even if the table is being updated during the export.
Any modification (UPDATE, INSERT, or DELETE) on a table automatically qualifies that table for export.
Also, if database structures have changed in the following ways, then the underlying base tables and data are exported:
In addition, the following data is backed up:
The command syntax is as follows:
where:
INCREMENTAL
Exports all database objects that have changed since the last incremental export, as tracked by table SYS.INCEXP, then updates the table with a new ITIME and EXPID.
CUMULATIVE
Exports all database objects that have changed since the last cumulative export, as tracked by SYS.INCEXP, then updates the table with a new CTIME, ITIME, and EXPID.
COMPLETE
Exports all objects and updates the tables SYS.INCEXP and SYS.INCVID. (A FULL=Y export does not update these tables unless you specify the INCTYPE parameter.).
Note: See page 1 - 31 for definitions of ITIME, EXPID and CTIME.
Note: For incremental and cumulative exports, a row is also added to table SYS.INCFIL to identify the export file and the user doing the export. For more information on the system tables that support incremental export, see page 1 - 31. When exporting 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 following example shows an incremental export session after the tables SCOTT.EMP and SCOTT.DEPT are modified:
> exp system/manager inctype=incremental
...
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 job queues
. exporting refresh groups and children
. exporting cluster definitions
. exporting stored procedures
. about to export SYSTEM's tables ...
. about to export SCOTT's tables ...
. exporting table DEPT 7 rows exported
. exporting table EMP 22 rows exported
. about to export ADAMS's tables ...
. about to export JONES's tables ...
. about to export CLARK's tables ...
. about to export BLAKE's tables ...
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
. exporting synonyms
. exporting views
. exporting default and system auditing options
. exporting information about dropped objects
Export terminated successfully without warnings.
SYS owns three tables (INCEXP, INCFIL, and INCVID) maintained by Export/Import. None of these tables should be altered in any way.
Export maintains a table to track which objects were exported in specific exports. That table, SYS.INCEXP, contains these columns:
OWNER#
The userid of the schema containing the table.
NAME
The object name. With OWNER#. The primary key consists of OWNER#, NAME and TYPE.
TYPE
The type of the object (a code standing for INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, SNAPSHOT, SNAPSHOT LOG, or PACKAGE BODY).
The date and time of the last cumulative export that included this object.
The date and time of the last incremental export that included this object.
The ID of the incremental or cumulative export, also found in table SYS.INCFIL.
You can use this information in several ways. For example, you could generate a report from SYS.INCEXP after each export to document the export file.
The table SYS.INCFIL tracks the incremental and cumulative exports and assigns a unique identifier to each. This table contains the following columns:
The ID of the incremental or cumulative export, also found in table SYS.INCEXP.
The type of export (incremental or cumulative).
The username of the user doing the export.
A third table, SYS.INCVID, contains one column for the EXPID of the last valid export. This information determines the EXPID of the next export.
Oracle Export utility's Direct Path export feature 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.
For added performance, the database can be set to direct read mode thus eliminating contention with other users for database resources because database blocks are read into the Export session's private buffer, rather than a public buffer cache.
Export provides two methods for exporting table data:
A conventional path export uses the SQL statement "SELECT * FROM TABLE" to extract data from database tables. Data are read from disk into a buffer cache and rows are transferred to the evaluation buffer. The data, after passing expression evaluation, is transferred over the network to the Export client which then writes the data into the export file.
A direct path export causes the export data to be read directly, bypassing the evaluation buffer, and saves on data copies whenever possible. It optimizes the execution of a SELECT * FROM TABLE statement.
Used in conjunction with the database in direct read mode (which causes the Export session's private buffer to be used rather than a public buffer), contention with other users for database resources is eliminated and performance improved.
Figure 1 - 8 shows how database table data extraction differs between these two methods.
Figure
1 - 8. Database Reads on Direct Path and Conventional Path
In a direct path export, data is read from disk into the buffer cache and rows are transferred directly over the network 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 over the network to the Export client which then writes the data into the export file.
Before you can use direct path Export, you must run the upgraded CATEXP.SQL script shipped with release 7.3 after the database has been created.
Additional Information: 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 only needs to be run once. Once run, it need not be run before future exports. This script creates the export views and character set assignments necessary for direct path exports.
Before running Export, ensure that there is enough disk or tape storage space to write the export file to. If there is not enough space, Export will terminate 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 in the Oracle data dictionary. See the Oracle7 Server Reference for more information.
If you do not run CATEXP.SQL before attempting a direct path export, the following errors will occur:
EXP-00008: ORACLE error 904 encountered ORA-00904: invalid column name EXP-00222: invalid column name
Export terminated successfully with warnings.
You invoke a direct path export using the following syntax:
EXPORT DIRECT=Y
DIRECT=Y specifies that table data is to be extracted using direct path mode. The default is DIRECT=N, table data is to be extracted using the conventional path.
You can also specify direct path export from a parameter file by using the command-line option PARFILE. For more information about the parameter file, see "The Parameter File" on Page 1 - 11 of this manual.
Note: The Export parameter BUFFER, used to specify the size of the fetch array, applies only to conventional path exports. It has no effect on a direct path export. The parameter RECORDLENGTH can be used to specify the size of the Export I/O buffer.
If you want to export release 7.3 database objects to a previous release, and you are concerned about backward compatibility, you may need to use conventional path export, because the direct path export dump file format is not compatible with releases prior to 7.3.
Following are factors that can affect the size differences between direct path and conventional exports.
The overhead is 2 extra bytes per extra column piece, which can be significant. This overhead occurs because direct path export reads directly from the buffer cache, where data is not in rows. Conventional path export reads from the SQL interface, where the concept of rows is enforced.
Note that when a direct path export completes table scans, it reverts to conventional path export behavior when it must handle other objects or write table-end markers (when in table mode).
The amount of zero padding in direct path exports depends upon the point at which export switches from table scans to writing other objects, as well upon the size of the export I/O buffer (set in the RECORDLENGTH parameter).
Given these factors, it's unlikely that the size of dump files for direct path and conventional exports are never the same.
Error, warning, and completion messages are logged as described in "Warning, Error, and Completion Messages" on Page 1 - 4 of this manual.
Direct path export only exports in the database character set. If the export session character set is different from the database character set, a warning will be displayed, and the export will abort. The user must set the session character set to that of the database before retrying the export. However, the import session and target database character set can differ from the source database character set requiring a character set conversion.
Any character set conversion will lengthen the processing time required for an import. Therefore it is advisable to limit the number of character sets conversions to as few as possible.
The ideal scenario is one in which the import session and target database character set are the same as the source database character set requiring no character set conversion.
In the situation where the import session character set and the target character set are the same, but differ from the source database character set, one character set conversion will be required.
If you currently have differing character sets for the source and target databases and/or the import session, and you plan to export/import between these databases regularly, it is advisable to do a one-time export to align these character sets.
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>,
where
db_version_number is 7.1.5 or higher.
You may improve performance by increasing the value of the RECORDLENGTH parameter when you invoke a direct path export. Your exact performance gain will vary depending upon the following factors:
If you leave the RECORDLENGTH parameter undefined, it defaults to your system-dependent value. For more information about your RECORDLENGTH default value, see your operating system-specific documentation.
You can change RECORDLENGTH to any value equal to or greater than your system's BUFSIZE. The highest value is 64k because this value is read into a 2-byte number. Changing the RECORDLENGTH parameter only affects the size of data that accumulates before writing to the disk. It does not affect the operating system file block size. If DB_BLOCK_SIZE is greater than BUFSIZE, it is more advantageous to use DB_BLOCK_SIZE, because each table scan can only return data up to DB_BLOCK_SIZE. If this does not fit in the export I/O buffer, export performs separate writes to the disk.
Consider the following values for RECORDLENGTH:
Additional Information: Other factors affect the use of direct read mode. See the Oracle7 Server Administrator's Guide for more information.
The following restrictions apply when executing a direct path Export:
Export files and dump sites generated using direct path export are incompatible with earlier Import versions. For example, an Export file generated using direct path export could not be imported using the release 7.2 Import utility. If backward compatibility is an issue, we recommend that you use Export's conventional path export.
This section describes the general behavior and restrictions of running an Export version that is different from the Oracle Server.
In general, any lower version Export utility may be run with a higher version Oracle Server. For example, Export Release 6 can be used with the Oracle7 Server, but note that it creates a Release 6 export file. (This procedure is described in the next section, "Creating Oracle Server Release 6 Export Files from Oracle7".)
Whenever a lower version Export utility runs with a higher version of the Oracle Server, any categories of database objects that did not exist in the lower version are excluded from the export. For example, when running a Release 6 version of Export with the Oracle7 Server, snapshots (which did not exist in Release 6) are excluded from the export. (A complete list of objects excluded in this export is given in the next section.)
Attempting to use a higher version of Export with an 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.
In Release 7.1, the export file format was changed to accommodate stored procedures, functions, and packages that have comments embedded among the creation-statement keywords. As a result, these code objects (and triggers) exported from release 7.1 and beyond cannot be imported into earlier releases, unless a patch is applied that makes earlier versions of Import and Export use the new export file format. All other objects can be imported normally without the patch.
To export from a 7.0.16 database containing code objects with comments between the creation keywords, for example:
CREATE PROCEDURE /* comment */ FOO /* comment */ AS ...
or to export from a 7.0.12 or earlier 7.0 database that contains such code objects, or that contain code objects in which the above creation keywords spanned multiple lines; then the export can be made to succeed either by appropriately re-creating the code objects, or by applying a patch that makes earlier versions of Import and Export use the new export file format.
The following table summarizes the situations and solutions:
| Exporting From | Objects Affected | Situation | Solution |
| 7.0.12 | procedures, functions, packages, package bodies | If the creation keywords for these objects are defined on multiple lines, or if they have comments embedded among the creation keywords, Export fails with a memory violation. | Apply the patch or change the definitions so that the creation keywords are all one line to successfully export these objects. |
| 7.0.16 | procedures, functions, packages, package bodies | If these objects have comments embedded among the creation keywords, they cannot be imported from the export file. | Apply the patch or change the definitions to remove the embedded comments, then re-export these objects. |
| 7.1 | triggers, procedures, functions, packages, package bodies | These objects cannot be imported into a 7.0 release from a 7.1 export file. | Apply the patch to the earlier release in to read the new export file format or else put the objects into the proper format and use a Release 7.0 export on the 7.1 database. |
Table 1 - 2. Exports and Imports Affected by the Change in Export File Format
It is possible to create a Version 6 export file from Oracle7 by running the Version 6 export executable with the Oracle7 Server. To do so, you must first run CATEXP6.SQL as SYS. This script creates the export views that make the database look to Export like a Version 6 database.
Note: A normal Oracle7 export requires CATEXP.SQL to be run by SYS after CATALOG.SQL to create the necessary views. If needed, both CATEXP.SQL and CATEXP6.SQL can be run, in any order. Once one of these scripts has been run, it does not have to be run again.
The Version 6 export utility produces a Version 6 export file by issuing queries against the views created by CATEXP6.SQL. Because this file is Version 6 compatible, it excludes the following Oracle7 objects:
Suggestion: You can use the UTLEXP6.SQL script to obtain the names of all objects that are excluded from the export file. Review this script for the most up-to-date notes on conversion issues.
The Oracle7 VARCHAR2 datatype is automatically converted to its Version 6 equivalent CHAR. Fixed-length Oracle7 CHAR columns are also converted to variable-length Version 6 CHAR data.
Database link names in Oracle7 have the format name.d1.d2.d3..., where name is the database name, d1 is a domain specification, and d2..d3 are optional components in a domain name. In a Version 6 export, link names are truncated, leaving only the name component.
Oracle7 character data (VARCHAR2) can be up to 2,000 characters long, whereas Version 6 CHAR data is limited to 255. The entire column is placed in the export file. The CREATE TABLE statement in the export file includes the actual length of the column, as it was defined in the Oracle7 database.
When Version 6 Import attempts to create the table, the specification of a CHAR column greater than 255 produces an error. One solution is to change the table's datatype to LONG, but be aware of the many restrictions on the Version 6 LONG datatype. See "Selecting a Datatype" in the Oracle7 Server Application Developer's Guide for details.
Oracle7 LONG data can be 2 gigabytes. Unlike VARCHAR data, Version 6 Export truncates LONG data when writing to the export file.
In Version 6.0.35.0 and later, Export truncates LONG data to 64K - 3 bytes and produces a warning message. Earlier releases truncate to 64K - 2 bytes with no warning.
Warning: When importing into a Version 6 database, such overly long data produces an error and causes the rest of the table data to be skipped.
Oracle7 object audit options on tables and views are automatically exported in their equivalent version 6 form. However, the wide array of Oracle7 privileges makes it impossible to translate them into any Version 6 equivalent. As a result, system audit options are not exported. After Importing into Version 6, issue one or more of the following statements to re-establish system audits:
AUDIT DBA [WHENEVER [NOT] SUCCESSFUL]
AUDIT CONNECT [WHENEVER [NOT] SUCCESSFUL]
AUDIT NOT EXISTS
AUDIT ALL [WHENEVER [NOT] SUCCESSFUL]
|
Prev Next |
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|
