ch09.htm

Teach yourself Oracle8 in 21 day

	<LI><TT>HELP=[Y or N</TT>--If this parameter is set to <TT>Y</TT>, a list of export
	parameters is displayed. The default is <TT>N</TT>.
	<P>
	<LI><TT>INCTYPE</TT>type--This parameter specifies an incremental export. The values
	available are <TT>COMPLETE</TT>, <TT>CUMULATIVE</TT>, and <TT>INCREMENTAL</TT>.
	<P>
	<LI><TT>INCTYPE=COMPLET</TT>--This parameter serves as the baseline for <TT>CUMULATIVE</TT>
	and <TT>INCREMENTAL</TT> exports. It is the same as <TT>FULL=Y</TT> with additional
	information updated for incremental exports.
	<P>
	<LI><TT>INCTYPE=CUMULATIV</TT>--This parameter exports tables that have changed since
	the last <TT>COMPLETE</TT> or <TT>CUMULATIVE</TT> export.
	<P>
	<LI><TT>INCTYPE=INCREMENTA</TT>--This parameter exports tables that have changed
	since the last <TT>COMPLETE</TT>, <TT>CUMULATIVE</TT>, or <TT>INCREMENTAL</TT> export.
	<P>
	<LI><TT>INDEXES=[Y or N</TT>--This parameter specifies that indexes should be exported.
	The default is <TT>Y</TT>.
	<P>
	<LI><TT>LOG</TT>logfile--This parameter specifies the name of a log file in which
	to write error and status messages. Even when this parameter is set, messages are
	still displayed on the screen.
	<P>
	<LI><TT>OWNER</TT>owner_name(s)--This parameter specifies a list of users whose objects
	will be exported in user mode.
	<P>
	<LI><TT>PARFILE</TT>param_file--This parameter specifies the parameter filename.
	<P>
	<LI><TT>POINT_IN_TIME_RECOVER=[Y or N</TT>--This parameter specifies whether a point-in-time
	recovery will be performed on import.
	<P>
	<LI><TT>RECORD=[Y or N</TT>--This flag specifies that the system tables <TT>SYS.INCVID</TT>,
	<TT>SYS.INCFIL</TT>, and <TT>SYS.INCEXP</TT> record a cumulative or incremental export.
	The default is <TT>Y</TT>.
	<P>
	<LI><TT>RECORDLENGTH</TT>number--This parameter specifies the size in bytes of the
	file record. This is used if you are going to transfer the export file to another
	operating system.
	<P>
	<LI><TT>RECOVERY_TABLESPACE</TT>ts_name--This parameter specifies the names of the
	tablespaces to be used in the point-in-time recovery.
	<P>
	<LI><TT>ROWS=[Y or N</TT>--This parameter specifies that the rows of table data should
	be exported. The default is <TT>Y</TT>.
	<P>
	<LI><TT>STATISTICS</TT>type--Available values are <TT>ESTIMATE</TT>, <TT>COMPUTE</TT>,
	and <TT>NONE</TT>. The default is <TT>ESTIMATE</TT>.
	<P>
	<LI><TT>STATISTICS=ESTIMAT</TT>--This parameter specifies that statistics are generated
	on the tables using the <TT>ESTIMATE</TT> method when the data is imported.
	<P>
	<LI><TT>STATISTICS=COMPUT</TT>--This parameter specifies that statistics are generated
	on the tables using the <TT>COMPUTE</TT> method when the data is imported.
	<P>
	<LI><TT>STATISTICS=NON</TT>--This parameter specifies that statistics are not generated
	on the tables when the data is imported.
	<P>
	<LI><TT>TABLES</TT>tables--This parameter specifies a list of tables to export when
	using Export in table mode.
	<P>
	<LI><TT>USERID</TT>username<TT>/</TT>password--This parameter specifies the username
	and password of the Oracle user performing the export.
</UL>

<P>Here is an example of a parameter file:</P>
<PRE><FONT COLOR="#0066FF">FULL=Y
BUFFER=8192
FILE=D:\database\export\EXPDAT.DMP
STATISTICS=COMPUTE
</FONT></PRE>
<P>If no parameters are specified, the Export utility prompts you for values. Most
prompted items come with a default value. An example of using Export interactively
is shown in Listing 9.1.
<H4><FONT COLOR="#000077"><B>Listing 9.1. Example of EXP80.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">D:\&gt;exp80

Export: Release 8.0.2.0.2 - Beta on Thu Jul 17 18:15:20 1997

Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.


Username: internal
Password:

Connected to: Oracle8 Server Release 8.0.2.0.2 - Beta
With the distributed, heterogeneous, replication, objects
and parallel query options
PL/SQL Release 3.0.2.0.2 - Beta
Enter array fetch buffer size: 4096 &gt;

Export file: EXPDAT.DMP &gt;

(2)U(sers), or (3)T(ables): (2)U &gt;

Export grants (yes/no): yes &gt;

Export table data (yes/no): yes &gt;
</FONT></PRE>
<PRE><FONT COLOR="#0066FF"><TT>Compress extents (yes/no): yes &gt;</TT> </FONT></PRE>
<P>Both methods export the data in the same way. There exists the possibility of
certain error conditions that may or may not cause Export to fail. These error conditions
are described in the Oracle documentation.
<H3><FONT COLOR="#000077"><B>Import</B></FONT></H3>
<P>The Oracle Import utility has one function: to load data that has been exported
into an Oracle database. The Import utility can read only exported data. If you want
to load other data into an Oracle database, you must use another utility such as
SQL*Loader, which is discussed later today.</P>
<P>As described earlier today, Export and Import can be used for several different
functions:

<UL>
	<LI>For backup/recovery--Backup and recovery are covered on Days 16-18.
	<P>
	<LI>To move data between databases on different systems.
	<P>
	<LI>To rebuild a database--This is useful to eliminate fragmentation and chained
	rows.
	<P>
	<LI>To reorganize a database--If you want to reorganize the locations of datafiles
	and so on, you can use Export and Import.
</UL>

<P>There are several different reasons for using Export and Import. As with the Export
utility, imports can be performed via Data Manager or via a command-line program.
<H4><FONT COLOR="#000077"><B>Import Using Data Manager</B></FONT></H4>
<P>Data Manager can be used to export, import, or load data. The import operation
is much less complicated than the export operation because the export file contains
information about the options used during the export process.

<DL>
	<DD><B>1.</B> To invoke the import operation, select Data | Import in Data Manager.
	The initial screen in the Data Manager Import wizard allows you to select the export
	file from which you want to import. This filename can be typed, or it can be selected
	via the Browse function (see Figure 9.7).
</DL>

<P><A NAME="07"></A><A HREF="07.htm"><B>Figure 9.7.</B></A></P>
<P><I>Screen one of the Data Manager Import wizard.</I></P>

<DL>
	<DD><B><I><BR>
	</I>2.</B> Select the objects to be imported in screen two. By default, no objects
	are selected. You must select at least one object for the import to work. This is
	shown in Figure 9.8.
</DL>

<P><A NAME="08"></A><A HREF="08.htm"><B>Figure 9.8.</B></A></P>
<P><I>Screen two of the Data Manager Import wizard.</I></P>

<DL>
	<DD><I><BR>
	</I><B>3. </B>In screen three, the objects that were exported are selected. If you
	do not want to import a schema object (such as grants and the like), it can be deselected
	at this point.
	<P><B>4.</B> Screen four allows you to select the record length (this is necessary
	if you are im-porting an export file from another OS), buffer size, and log file.
	I find that the default settings are usually sufficient.</P>
	<P><B>5. </B>Screen five contains advanced options such as the import type and whether
	to im-port all data or only recent data. From this screen you can also write index-creation
	commands to a file to allow you to re-create the indexes rather than import them.
	You'll also find the Commit after each array insert and the Overwrite existing data
	files checkboxes here. This screen is shown in Figure 9.9.
</DL>

<P><A NAME="09"></A><A HREF="09.htm"><B>Figure 9.9.</B></A></P>
<P><I>Screen five of the Data Manager Import wizard.</I></P>

<DL>
	<DD><B><I><BR>
	</I>6.</B> As with the Export wizard, the final screen shows a list of the selected
	items for final approval before the import operation proceeds. Screen six is displayed
	only if you have chosen to schedule the import operation to occur at a later time.
	<P><B>7.</B> Like the Export utility, the Import utility shows a status screen and
	logs status in-formation to a log file. For example, Figure 9.10 contains numerous
	error messages, which are due to the fact that I tried to import data over existing
	tables and did not select to overwrite existing data in screen five. This offers
	you some protection from accidentally destroying your own data by unintentionally
	overwriting good data.
</DL>

<P><A NAME="10"></A><A HREF="10.htm"><B>Figure 9.10.</B></A></P>
<P><I>The Data Manager Import wizard's Import Status screen.</I></P>
<H4><FONT COLOR="#000077"><B>Import Using the IMP80 Utility</B></FONT></H4>
<P>As with the Export utility, the Import utility features a command-line utility
for performing imports. The Import utility's command-line utility is called IMP80
under Windows NT. Under certain other operating systems, it is known simply as IMP.</P>
<P>IMP80 supports a variety of options, many very similar to the export 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 Import</B></FONT></H4>
<P>Import is invoked with the following syntax:</P>
<PRE><FONT COLOR="#0066FF">IMP80 username/password [ options ... ]
</FONT></PRE>
<P>As with Export, you can store the Import parameters in a parameter file in Import.
I recommend this for consistency, reusability, and error reduction. To invoke Import
with the parameter file, use this syntax:</P>
<PRE><FONT COLOR="#0066FF">IMP80 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 Import parameters use the following format:</P>
<PRE><FONT COLOR="#0066FF">PARAMETER=value
</FONT></PRE>
<P>The Import parameters are

<UL>
	<LI><TT>ANALYZE=[Y or N]</TT>--This parameter specifies that the <TT>SQL ANALYZE</TT>
	command be run. The default is <TT>Y</TT>.
	<P>
	<LI><TT>BUFFER</TT>number--This parameter specifies the size of the copy buffer (in
	bytes) used by Import. If this is zero, one row at a time will be fetched.
	<P>
	<LI><TT>CHARSET</TT>character_set--This parameter specifies the character set used
	for the export if it was an Oracle6 export. From Oracle7 forward, the character-set
	information is written to the export file. Import will use this value to verify that
	the export file was written with this character set. It is usually not necessary
	to use this option.
	<P>
	<LI><TT>COMMIT=[Y or N</TT>--This parameter specifies that a commit should occur
	after each array insert. By default, the commit occurs after each table is imported.
	The default is <TT>N</TT>.
	<P>
	<LI><TT>DESTROY=[Y or N</TT>--This parameter specifies that the original datafiles
	should be reused. This essentially adds the reuse option to the <TT>CREATE TABLESPACE</TT>
	operation. The default is <TT>N</TT>.
	<P>
	<LI><TT>FEEDBACK</TT>number--This parameter specifies that a dot be displayed for
	every number of imported rows. For example, if <TT>FEEDBACK=100</TT>, you will see
	a dot for every 100 rows, indicating the progress of the import 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>FROMUSER</TT>username--This parameter specifies a schema to import the data
	for. If no schema is identified, the entire export file will be imported.
	<P>
	<LI><TT>FULL=[Y or N</TT>--This parameter specifies whether a full database import
	is performed. Specifying <TT>FULL=Y</TT> imports in full mode. The default is <TT>N</TT>.
	<P>
	<LI><TT>GRANTS=[Y or N</TT>--This parameter specifies that grants should be exported.
	The default is <TT>Y</TT>.
	<P>
	<LI><TT>HELP=[Y or N</TT>--If this parameter is set to <TT>Y</TT>, a list of Export
	parameters will be displayed. The default is <TT>N</TT>.
	<P>
	<LI><TT>IGNORE=[Y or N</TT>--This parameter describes how the system will operate
	if table-creation errors occur. If <TT>IGNORE=Y</TT>, the import will continue without
	reporting an error if an error occurs. If <TT>IGNORE=N</TT>, an error will be reported
	before the import continues. The default is <TT>N</TT>.
	<P>
	<LI><TT>INCTYPE</TT>type--This parameter specifies the type of import. Available
	options are <TT>SYSTEM</TT> and <TT>RESTORE</TT>.
	<P>
	<LI><TT>INCTYPE=SYSTE</TT>--This parameter restores the most recent copy of the <TT>SYSTEM</TT>
	objects.
	<P>
	<LI><TT>INCTYPE=RESTOR</TT>--This parameter restores all user and database objects
	that are in the export file.
	<P>
	<LI><TT>INDEXES=[Y or N</TT>--This parameter specifies that indexes should be imported.
	The default is <TT>Y</TT>.
	<P>
	<LI><TT>INDEXFILE</TT>filename--This parameter specifies that index-creation syntax
	should be written to filename. This allows you to easily re-create the indexes rather
	than restore them.
	<P>
	<LI><TT>LOG</TT>logfile--This parameter specifies the name of a log file in which
	to write error and status messages. Even with this parameter set, messages are still
	displayed on the screen.
	<P>
	<LI><TT>PARFILE</TT>param_file--This parameter specifies the parameter filename.
	<P>
	<LI><TT>POINT_IN_TIME_RECOVER=[Y or N</TT>--This parameter specifies that you want
	to recover to a particular point in time. The default is <TT>N</TT>.
	<P>
	<LI><TT>RECORDLENGTH</TT>number--This parameter specifies the size in bytes of the
	file record.
	<P>
	<LI><TT>ROWS=[Y or N</TT>--This parameter specifies that the rows of table data should
	be exported. The default is <TT>Y</TT>.
	<P>
	<LI><TT>SHOW=[Y or N</TT>--This parameter specifies that the contents of the export
	file are displayed on the screen rather than imported into the database. This can
	be useful in debugging situations. The default is <TT>N</TT>.
	<P>
	<LI><TT>SKIP_UNUSABLE_INDEXES=[Y or N</TT>--If set to <TT>Y</TT>, this parameter
	specifies that unusable indexes are not immediately rebuilt, thus allowing you to
	rebuild them after the import has completed. The default is <TT>N</TT>.
	<P>
	<LI><TT>TABLES</TT>tables--This parameter specifies a list of tables to import when
	using Import in table mode.
	<P>
	<LI><TT>TOUSER</TT>username--This parameter specifies a list of users whose schema
	will be used on the import. If you want to import one schema's data to another schema,
	use <TT>FROMUSER</TT> and <TT>TOUSER</TT>.
	<P>
	<LI><TT>USERID</TT>username<TT>/</TT>passwd--This parameter specifies the username
	and password of the Oracle user performing the export.
</UL>

<P>Here is an example of a parameter file:</P>
<PRE><FONT COLOR="#0066FF">FULL=Y
BUFFER=8192
FILE=D:\database\export\EXPDAT.DMP
STATISTICS=ESTIMATE
</FONT></PRE>
<P>If no parameters are specified, the Import utility will prompt you for values.
Most prompted items come with a default value. An example of using Import interactively
is shown in Listing 9.2.
<H4><FONT COLOR="#000077"><B>Listing 9.2. Example of IMP80.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">D:\&gt;imp80

Import: Release 8.0.3.0.0 - Production on Sat Jul 19 12:24:53 1997

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

Username: system
Password:

Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production

Import file: EXPDAT.DMP &gt; d:\database\export\EXPDAT.DMP