Incremental, Cumulative, and Complete Exports - Oracle 8i

Important: Incremental, cumulative, and complete Exports are obsolete features that will be phased out in a subsequent release. You should begin now to migrate to Oracle’s Backup and Recovery Manager for database backups.


  • You can do incremental, cumulative, and complete exports only in full database mode (FULL=Y). Only users who have the role EXP_FULL_DATABASE can run incremental, cumulative, and complete Exports. This role contains the privileges needed to modify the system tables that track incremental exports.
  • You cannot specify incremental Exports as read-consistent.

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

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

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

Figure : Incremental Export at Time 2

Figure : Incremental Export at Time 2

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

Figure : Incremental Export at Time 3

Figure : Incremental Export at Time 3

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

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

Figure : Cumulative Export at Time 4

Figure : Cumulative Export at Time 4

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

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

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

Figure : Complete Export at Time 5

Complete Exports

A Scenario
The scenario described in this section shows how you can use cumulative and incremental Exports.
Assume that as manager of a data center, you do the following tasks:

  • A complete Export (X) every three weeks
  • A cumulative Export (C) every Sunday
  • An incremental Export (I) every night

Your export schedule follows:

export schedule

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 complete Export taken on day 1
  • The cumulative Export taken on day
  • The cumulative Export taken on day 15
  • Three incremental Exports taken on days 16, 17, and 18

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

Note: The section INCTYPE explains the syntax to specify incremental, cumulative, and complete Exports.

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

The entire grant structure for tables or views is exported with the underlying base tables. Indexes are exported with their base table, regardless of who created the index. If the base view is included, "instead of" triggers on views are included. Any modification (UPDATE, INSERT, or DELETE) on a table automatically qualifies that table for incremental Export. When a table is exported, all of its inner nested
tables and LOB columns are exported also. Modifying an inner nested table column causes the outer table to be exported. Modifying a LOB column causes the entire table containing the LOB data to be exported.

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

  • A table is created.
  • A table definition is changed by an ALTER TABLE statement.
  • Comments are added or edited.
  • Auditing options are updated.
  • Grants (of any level) are altered.
  • Indexes are added or dropped.
  • Index storage parameters are changed by an ALTER INDEX statement.

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

  • All system objects (including tablespace definitions, rollback segment definitions, and user privileges, but not including temporary segments)
  • Information about dropped objects
  • Clusters, tables, views, procedures, functions, dimensions, and synonyms created since the last export
  • All type definitions

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

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

> exp system/manager full=y inctype=incremental

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

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

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

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

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

The table SYS.INCEXP tracks which objects were exported in specific exports.

This table contains the following columns:

OWNER# The userid of the schema containing the table.

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


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

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

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

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

The table SYS.INCFIL tracks the incremental and cumulative exports and assigns a unique identifier to each.

This table contains the following columns:

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

EXPTYPE The type of export (incremental or cumulative).

EXPFILE The name of the export file.

EXPDATE The date of the export.

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

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

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 8i Topics