Previous | Table of Contents | Next
Page 305
Import/Export
In this chapter
- Understanding the Purpose and Capabilities of
Import/Export 306
- Understanding Behavior 307
- Controlling and Configuring Import and Export 309
- Taking Walkthroughs of Import and Export
Sessions 319
- Using the SHOW and INDEXFILE Options 326
Page 306
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:
- Back up and recover your databases.
Export and Import are frequently used as part of a backup plan. Typically, full or
hot backups are used to back up the entire database in the event of a disk or
computer failure. However, if just one table in the entire database needs to be recovered, the
entire database would have to be recovered on another machine, and the one table would
be copied over. This is incredibly time and resource consuming. Import and Export
can save you this hassle by providing the capability to export the entire database and
import just the tables you need recovered. They may also serve as a backup and
recovery mechanism for the entire database, because Point-in-time Recovery is also an option.
- Move data and objects among schemas.
You can use Import and Export to copy tables, indexes, grants, procedures, and
views, among all other object types from one schema to another. This helps save time and
effort because you can just specify those objects you desire to move. Also, moving data may
be used as a form of data replication among different Oracle databases.
- Migrate databases from one Oracle version to another.
You can upgrade (or downgrade) the version of Oracle by using the Import and
Export utilities. You can export an entire Oracle7 database, for example. Then, providing
you have the Oracle8 Server installed, the database can be imported, making the data
and application function in an Oracle8 environment. This process is called
migration.
- Defragment a tablespace or the entire database.
Fragmentation occurs when objects such as tables and indexes are created,
deleted, enlarged, and reduced in size over time. Fragmentation also occurs when object
storage parameters are poorly defined. By exporting a tablespace, coalescing space,
and importing objects again, you can defragment tablespaces.
- Generate CREATE scripts.
You can also use Import and Export to generate CREATE scripts for tables,
partitions, views, grants, indexes, constraints, and tablespaces, among all other objects in
the database. This proves quite useful for modifying objects and safeguarding the
structure of your objects in the event one gets corrupted or deleted.
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:
- FULL
export: Exports all objects, structures, and data within the database.
- OWNER
export: Exports only those objects owned by a particular user account.
- TABLE
export: Exports only the specified tables and partitions.
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