 |
|
Oracle Tips by Burleson |
Enhancements to Oracle’s
Export Utility
One of the oldest tools for backing-up and
recovering any Oracle database is Oracle export utility. Dating from
Oracle7, the export utility allows you to quickly “punch” a copy of
row data into a flat file on your operating system. This flat file
can be used to recovery a single table or a whole schema.
The export utility is quite simple to use. In
Oracle 10g, we have two utilities exp.exe and imp.exe, which can be
invoked directly from the DOS or UNIX command line.
The online version of the export utility
prompts you for all required information making it easy to create a
Oracle database backup of a table. In the example below, we take a
backup of the book table (Figure 1).
Figure 1 – A backup of the book table
Instead of using interactive mode, we could
pass all of the arguments directly to the exp utility. In the example
below (Figure 2), we invoke the exp utility, specifying the user ID
and password, the name of the export data file, compress=y to compress
extents, and tables=(book) to specify the book table for export.
Figure 2 – An export with multiple
command-line arguments
For more sophisticated backups, the export
utility allows us to create a parameter file. Here is an example of a
parameter file called exp_pubs.par:
compress=y
file=exp_pubs_all.dat
rows=y
grants=y
owner=pubs
log=exp_pubs.log
One we have created the parameter file, we can execute it from
the DOS prompt.
SQL> exp
system/manager parfile=exp_pubs.par
Once the export has completed the log file
will have a complete record of all export activities. Here is the
resulting log file from the export (exp_pubs.log):
Connected
to: Oracle 10g Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user PUBS
. exporting object type definitions for user PUBS
About to export PUBS's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export PUBS's tables via Conventional Path ...
. . exporting table AUTHOR 10 rows
exported
. . exporting table BOOK 20 rows
exported
. . exporting table BOOK_AUTHOR 25 rows
exported
. . exporting table BOOK_TYPE_SALES 5 rows
exported
. . exporting table EMP 10 rows
exported
. . exporting table EMP_EXT
. . exporting table JOB 4 rows
exported
. . exporting table NEW_TITLE 20 rows
exported
. . exporting table PLAN_TABLE 0 rows
exported
. . exporting table PREVIOUS_EVENTS 33 rows
exported
. . exporting table PUBLISHER 10 rows
exported
. . exporting table PUB_SALARIES 9 rows
exported
. . exporting table SALES 100 rows
exported
. . exporting table STORE 10 rows
exported
. . exporting table T1 1 rows
exported
. . exporting table TEST_TABLE 31329 rows
exported
. . exporting table TITLE_CAPS 20 rows
exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export
terminated successfully without warnings.
In practice, the DBA will always check the
last line “Export terminated successfully without warnings.” to ensure
that the export worked.
Also note that all associated objects for a
schema are exported. This includes index definitions, referential
integrity constraints, snapshot logs, materialized views, and
triggers. Hence, the resulting export file contains everything that
you need to restore the Oracle database. The resulting data file from
the export utility can be used to easily copy an entire schema from
one location to another.
To demonstrate how easy export/import is,
let’s create a new schema owner for the sample database, and clone the
entire schema into our new user.
We begin by creating a new schema owner.
SQL> create
user new_pubs identified by pubs;
User
created.
SQL> grant
dba to new_pubs;
Grant
succeeded.
SQL> alter
user new_pubs default tablespace users;
User
altered.
SQL> alter
user new_pubs temporary tablespace temp;
User
altered.
Now that we have defined the user, we can
import the data from our exp_pubs_all.dat file directly into the new
schema:
C:\pubsdb>
imp system/manager file=exp_pubs_all.dat fromuser=pubs touser=new_pubs
Here we see that we have easily cloned the
entire pubs schema into the new_pubs user:
Connected
to: Oracle 10g Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export file
created by EXPORT:V09.00.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character
set
. importing PUBS's objects into NEW_PUBS
. . importing table "AUTHOR" 10 rows
imported
. . importing table "BOOK" 20 rows
imported
. . importing table "BOOK_AUTHOR" 25 rows
imported
. . importing table "BOOK_TYPE_SALES" 5 rows
imported
. . importing table "EMP" 10 rows
imported
. . importing table "JOB" 4 rows
imported
. . importing table "NEW_TITLE" 20 rows
imported
. . importing table "PLAN_TABLE" 0 rows
imported
. . importing table "PREVIOUS_EVENTS" 33 rows
imported
. . importing table "PUBLISHER" 10 rows
imported
. . importing table "PUB_SALARIES" 9 rows
imported
. . importing table "SALES" 100 rows
imported
. . importing table "STORE" 10 rows
imported
. . importing table "T1" 1 rows
imported
. . importing table "TEST_TABLE" 31329 rows
imported
. . importing table "TITLE_CAPS" 20 rows
imported
Import terminated successfully without warnings.
Now that we have illustrated the concept, we
must now remove our cloned database. This is easy in Oracle, and can
be done with a single command.
SQL > drop
user new_pubs cascade;
The cascade option tells Oracle to remove all
tables and indexes owner by this user. Nexct, let’s look at advanced
export options.
Advanced concepts in export/import
The export utility also supports the selective
export of rows. This allows you to perform a logical backup of select
subsets of table data. To illustrate, let’s assume that we want to
export only those books that belong to publisher ‘p001’;
STEP 1 - Create this parameter file as
exp_p001.par on your server.
rows=y
file=exp_p001.dat
log=exp_p001.log
tables=book query="where pub_key = 'P001'"
Note the single quotes around the key and
double-quotes around the query string.
STEP 2 - Invoke the export utility, using
your parameter file as input.
C:\pubsdb>
imp system/manager parfile=exp_p001.par
Step 3 – Review the log in file
exp_p001.log
Connected
to: Oracle 10g Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR
character
set
About to
export specified tables via Conventional Path ...
. . exporting table BOOK 4 rows
exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
Note that the export utility only exported the
book information for books belonging to pub_key P001. Also note that
the export utility attempted to export statistics for the cost-based
optimizer, and the utility noted that these statistics are
questionable because you only exported a sub-set of the table rows.
As we can see, the Oracle export/import
utility is great for taking small logical backups from Oracle
databases. However, there are some important limitations to the
export/import utility:
* Slow speed – For large Oracle
databases, using export import can take many hours, and export/import
is far slower than other methods such as tablespace-level backups and
using Create Table As Select (CTAS) to make table copies.
* Consistent row images – You can use
the consistent=y argument to tell Oracle to make a read-consistent
export of the table rows from the time that the export started, but it
can be tricky to restore from this export if the tables are actively
being updated. This is because you cannot be certain that you will
not loose row information.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
