Previous | Table of Contents | Next

Page 305

Chapter 13

Import/Export

In this chapter

Page 306

Understanding the Purpose and Capabilities of
Import/Export

The Oracle8 Server comes with two important utilities: Import and Export. These two utilities are useful for many important database functions such as backing up data, copying objects among different schemas and databases, and generating object creation scripts. They are also useful in migrating from one version of Oracle to another, and may be used to upgrade your database to Oracle8.

The Import and Export utilities provide a wide range of capabilities. The main purpose is to back up and recover data and objects. The objects and data are stored in a binary form that may be read only by Oracle databases. Export and Import can perform the following tasks:

Page 307

NOTE
The export file is in a binary format that may only be used with Oracle databases. You cannot export from Oracle and import into a non-Oracle database. Similarly, you cannot import from a non-Oracle database. If you wish to copy data to Oracle from another database product such as Microsoft Access, you should use SQL*Loader on a delimited file format of the data, such as CSV (comma-separated values). To transfer data from Oracle to a non-Oracle database, you must make a delimited file manually by spooling from within PL/SQL or SQL*Plus.

Understanding Behavior

There are three types of exports:

With dozens of types of structures that may be exported, it is important to distinguish what gets exported with each of the three export categories. Table 13.1 shows which structures are exported with each export option (in the order that Export exports). The following section then describes the syntax of running exp80 and imp80, executables for export and import with Oracle8.

NOTE
In a Windows NT environment, exp80 and imp80 are the commands for the Export and Import utilities. On UNIX and other operating systems, exp and imp are the commands for the Export and Import utilities. For simplicity, this book provides all examples with the xp80 and imp80 commands.

Table 13.1What Objects Get Exported with Each of the Three Export Options

Type of Object with FULL=Y option with OWNER option with TABLE
option
Tablespace
definitions
All objects
Profiles All objects
User
definitions
All objects
Roles All objects
Resource costs All objects
                                              continues

Page 308

Table 13.1Continued

Type of Object with FULL=Y option with OWNER option with TABLE
option Rollback
segment
definitions All objects Database links All objects Just for owner Sequence numbers All objects Just for owner
Directory aliases All objects
Foreign
function
library names All objects Just for owner Object type
definitions All objects Just for owner
Cluster definitions All objects Just for owner Just for table
and owner Tables All objects Just for owner Just for table and
owner Indexes All objects Just for owner Just for table and
owner Referential
integrity
constraints All objects Just for owner Just for table
and owner
Postable actions All objects Just for owner Synonyms All objects Just for owner Just for table and
owner Views All objects Just for owner Stored procedures All objects Just for owner
Triggers All objects Just for owner Just for table and
owner Snapshots All objects Just for owner Snapshot logs All objects Just for owner Job queues All objects Just for owner

Previous | Table of Contents | Next