📄 ch09.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Ch 9 -- Managing Data</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="../ch08/ch08.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch10/ch10.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Day 9 -<BR>
Managing Data</FONT></H1>
</CENTER>
<P>An important aspect of Oracle8 administration is getting data into and out of
the database. There are several different ways of performing both of these tasks:
<UL>
<LI>Export--Put a database's content and structure into a binary export file. Export
files can be read only by the Oracle Import utility.
<P>
<LI>Import--Use data from an import file to re-create the database's content and
structure.
<P>
<LI>SQL*Loader--This is a very flexible tool that is used to load ASCII or <I>flat-file</I>
data into an Oracle database. The SQL*Loader utility offers many options, as you
will see later today.
<P>
<LI>Backup/recovery--The backup and recovery features have their own way of loading
and restoring data. Because this is covered on Days 17, "Recovering the Database,"
and 18, "Administering Oracle Replication," I do not go into it here.
</UL>
<H2><FONT COLOR="#000077"><B>Using Export and Import</B></FONT></H2>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>The Oracle Export utility
is designed to write Oracle object definitions and data to an Oracle-specific binary
file. This file is known as the <I>export file</I>. An export file is Oracle specific
and can be read only by the Oracle Import utility, which you will learn about today.
The Export utility can be used for several different purposes, including
<UL>
<LI>To back up the database--Export can be used to create a backup of the Oracle
database, but is not the most efficient backup mechanism. Backup and recovery are
covered on Days 16-18.
<P>
<LI>To move data between databases--You can export data to an export file and then
import this data back into a different database. This is a great way to transfer
tables from one database to another.
<P>
<LI>To rebuild a database--If you have a database whose tablespaces are fragmented,
you can use Export and Import to defragment the database. This could improve performance
if fragmentation is slowing you down.
<P>
<LI>To reorganize a database--If you want to reorganize the locations of datafiles
and so on, you can use Export and Import. In this manner, you can create new tablespaces
and use Import to reload this data.
</UL>
<P>As you can see, there are several different reasons for using Export and Import.
<H3><FONT COLOR="#000077"><B>Export</B></FONT></H3>
<P>The Oracle Export utility provides a straightforward and simple service. Export
writes object definitions and table data to an Oracle-format binary file. This information
can be used to transfer data between databases on different machines or to supplement
the normal backup process.</P>
<P>The export file first contains the object data followed by the related objects.
For example, if tables have indexes on them, first the table data is written to the
export file, then the object's indexes.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The binary files written by the
Oracle Export utility can be read only by the Oracle Import utility. If you want
to transfer data to another RDBMS or read it with another utility, do not use Export.
<HR>
</BLOCKQUOTE>
<P>The export file is also used to reorganize data within the database. The normal
backup process copies an image of the datafile; if recovery is needed, it can only
write back the same image. Because Export organizes the data as it is written to
the export file, importing that data does not necessarily place the data in the exact
same place on disk. This provides a great advantage because it can reduce fragmentation
and row chaining.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Before you can run Export and Import,
the administrative SQL script <TT>CATEXP.SQL</TT> must have been run once on the
database. The administrative SQL script <TT>CATALOG.SQL</TT>, which is typically
run at database-creation time, automatically runs <TT>CATEXP.SQL</TT>.
<HR>
</BLOCKQUOTE>
<P>As with almost every Oracle utility, the export function can be accomplished graphically
via Data Manager or by using the EXP80 program.
<H4><FONT COLOR="#000077"><B>Export Using Data Manager</B></FONT></H4>
<P>To perform an export graphically, you must first invoke Data Manager. For Data
Manager to work, the Oracle agent and Enterprise Manager must be running.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Data Manager must be run by a user
with DBA privileges if a full export is to be performed.
<HR>
</BLOCKQUOTE>
<DL>
<DD><B>1. </B>When you invoke Data Manager, you will see the initial screen (shown
in Figure 9.1).
</DL>
<P><A NAME="01"></A><A HREF="01.htm"><B>Figure 9.1.</B></A></P>
<P><I>Data Manager.</I></P>
<DL>
<DD><B>2.</B> From Data Manager, you can invoke the export operation by selecting
Data | Export. This invokes the Data Manager Export wizard. This wizard asks a number
of questions concerning the export operation. As shown in Figure 9.2, the first screen
involves the location of the export file.
</DL>
<P><A NAME="02"></A><A HREF="02.htm"><B>Figure 9.2.</B></A></P>
<P><I>Screen one of the Data Manager Export wizard.</I></P>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Instead of creating the export file
in the default directory, I prefer to create a directory specifically for export
files. You can easily change the location where the export file will be created in
the Data Manager.
<HR>
</BLOCKQUOTE>
<DL>
<DD><B>3. </B>Screen two of the Data Manager Export wizard lets you choose the objects
to be exported. By default, the entire database is selected, as shown in Figure 9.3.
</DL>
<P><A NAME="03"></A><A HREF="03.htm"><B>Figure 9.3.</B></A></P>
<P><I>Screen two of the Data Manager Export wizard.</I></P>
<DL>
<DD><B><I><BR>
</I>4.</B> In screen three, you can select the objects you want to export, including
grants, in-dexes, table rows, and constraints. To perform an export in full mode,
leave all of the boxes checked. At this point you can also choose the Direct Export
option, which is not the default.
<P><B>5.</B> Screen four allows you to select the record length (this is necessary
if you are transferring an export file to another OS), buffer size, and log file.
I find that the default settings are usually sufficient.</P>
<P><B>6. </B>Screen five allows you to select whether you are performing a complete
or incremental export. Statistics modes are also selected here. These indicate whether
estimated or calculated statistics will be gathered for the optimizer when this data
is imported. Finally, you can configure the consistency mode and the extent merging
options here. This screen is shown in Figure 9.4.
</DL>
<P><A NAME="04"></A><A HREF="04.htm"><B>Figure 9.4.</B></A></P>
<P><I>Screen five of the Data Manager Export wizard.</I></P>
<DL>
<DD><B><I><BR>
</I>7.</B> Screen seven summarizes the export actions that will be taken when the
Finish button is clicked, as shown in Figure 9.5.
</DL>
<P><A NAME="05"></A><A HREF="05.htm"><B>Figure 9.5.</B></A></P>
<P><I>Screen seven of the Data Manager Export wizard.</I></P>
<DL>
<DD><B><I><BR>
</I>8. </B>If you selected to schedule the export rather than perform this operation
immediately, you are presented with screen six, which is shown in Figure 9.6. If
you did not select the schedule option, the Data Manager Export wizard jumps from
screen five to screen seven.
</DL>
<P><A NAME="06"></A><A HREF="06.htm"><B>Figure 9.6.</B></A></P>
<P><I>Screen six of the Data Manager Export wizard.</I></P>
<DL>
<DD><B><I><BR>
</I>9.</B> The status of the export is displayed in the Export Status screen. Errors
and successful completion messages are displayed here.
</DL>
<H4><FONT COLOR="#000077"><B>Export Using the EXP80 Utility</B></FONT></H4>
<P>Exports can be performed via the graphical utilities, as well as with a command-line
option. When you run the Export utility via the NT command prompt, a program called
EXP80 is run.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Under Windows NT, the export program
is called EXP80. Under other operating systems, it may be known simply as EXP.
<HR>
</BLOCKQUOTE>
<P>Export has several options and modes of operation:
<UL>
<LI>Full--Operating Export in full mode causes all database objects not in the <TT>SYS</TT>
schema to be exported. Because the <TT>SYS</TT> schema and schema objects are created
at database-creation time, the <TT>SYS</TT> schema and schema objects are excluded.
</UL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>That the <TT>SYS</TT> schema objects
are not exported in the full mode is another good reason not to create objects under
the <TT>SYS</TT> schema.
<HR>
</BLOCKQUOTE>
<UL>
<LI>Table--Operating Export in table mode allows you to specify which schema objects
to export.
</UL>
<UL>
<LI>User--Operating Export in user mode exports all objects belonging to the specified
user. These objects include tables, data, grants, and indexes.
</UL>
<P>Export can also be used to export partitions in a partitioned table. This is accomplished
by exporting in table mode and specifying the <TT>table</TT> partition.</P>
<P><FONT COLOR="#000077"><B>Using Export</B></FONT></P>
<P>Export is invoked from the command line and supports a variety of options. Because
of the importance of the Export and Import commands, these options are described
here. For more detailed information, refer to the Oracle8 documentation.
<H4><FONT COLOR="#000077"><B>The Syntax for Export</B></FONT></H4>
<P>Export is invoked with the following syntax:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">EXP80 username/password [ options ... ]
</FONT></PRE>
<P>To simplify the usage of Export and to allow for the standard use of options,
you can use a parameter file and store the export parameters. I recommend this for
consistency, reusability, and error reduction. To invoke Export with the parameter
file, use this syntax:</P>
<PRE><FONT COLOR="#0066FF">EXP80 username/password PARFILE=filename [ options ... ]
</FONT></PRE>
<P>If you use the parameter file, it is not necessary to use command-line options,
but you can if you want. The parameter file contains a list of parameters, one per
line. An example of a parameter file is included at the end of this section. The
available Export parameters use the following format:</P>
<PRE><FONT COLOR="#0066FF">PARAMETER=value
</FONT></PRE>
<P>The Export parameters are
<UL>
<LI><TT>BUFFER=</TT>number--This parameter specifies the size of the copy buffer
(in bytes) used by Export. If this is zero, one row at a time will be fetched.
<P>
<LI><TT>COMPRESS=[Y or N</TT>--This parameter specifies how the initial extent is
treated. If this parameter is set to <TT>Y</TT>, Export consolidates all table data
into one extent. If this parameter is set to <TT>N</TT>, Export uses the current
storage parameters to create the extents. The default is <TT>Y</TT>.
<P>
<LI><TT>CONSISTENT=[Y or N</TT>--This parameter specifies whether the export will
be performed in a manner that causes data to be consistent to a single point in time.
A value of <TT>Y</TT> causes the export to be consistent but can consume significant
rollback space and can fail if there is significant update activity. The default
is <TT>N</TT>.
<P>
<LI><TT>CONSTRAINTS=[Y or N</TT>--This parameter specifies whether to export table
constraints. The default is <TT>Y</TT>.
<P>
<LI><TT>DIRECT=[Y or N</TT>--This parameter specifies whether the direct path export
option is used, thus bypassing the SQL command-processing layer. Direct path export
cannot be used on some schema objects. The default is <TT>N</TT>.
<P>
<LI><TT>FEEDBACK</TT>number--This parameter specifies that a dot be displayed for
every number exported rows. For example, if <TT>FEEDBACK=100</TT>, you see a dot
for every 100 exported rows to indicate the progress of the export operation. The
default is <TT>0</TT>.
<P>
<LI><TT>FILE</TT>filename--This parameter specifies the name of the export file.
The default is <TT>EXPDAT.DMP</TT>.
<P>
<LI><TT>FULL=[Y or N</TT>--This parameter specifies whether a full database export
is performed. Specifying <TT>FULL=Y</TT> exports in full mode. The default is <TT>N</TT>.
<P>
<LI><TT>GRANTS=[Y or N</TT>--This parameter specifies that grants are exported. The
default is <TT>Y</TT>.
<P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -