ch09.htm

Teach yourself Oracle8 in 21 day

Enter insert buffer size (minimum is 4096) 30720>

Export file created by EXPORT:V08.00.03 via conventional path
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no >

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no >
Username: ETW

Enter table(T) or partition(T:P) names. Null list means all tables for user
<TT>Enter table(T) or partition(T:P) name or . if done:</TT> </FONT></PRE>
<P>Whether you use Import via Data Manager or with the IMP80 utility, the outcome
will be the same. As mentioned earlier, Import can be used only with data generated
from Export. If you want to load ASCII data or other data into a database, you must
use the SQL*Loader utility.
<H2><FONT COLOR="#000077"><B>Using SQL*Loader</B></FONT></H2>
<P>SQL*Loader is another Oracle utility that is used for loading data into an Oracle
database. Where the Import utility is designed to accept data in a specific format,
SQL*Loader is designed to be flexible and to accept data in a variety of formats.</P>
<P>SQL*Loader accepts two input file types: the actual input datafile and a loader
control file. The control file is used to specify the format of the datafile(s).
The control file is also used to specify such things as the column data types, field
delimiters, and various other data-specific information.</P>
<P>Like Export and Import, SQL*Loader can be invoked from within Data Manager or
from the command line. Regardless of which method you use, you are still required
to specify a control file.
<H3><FONT COLOR="#000077"><B>The Control File</B></FONT></H3>
<P>The control file is used to specify information about the data to be loaded. The
format of the control file contains control information and can also contain the
data itself.</P>
<P>The control file can contain multiple lines for each statement and is not case
sensitive except for characters within single or double quotes. The control file
can also include comments that are indicated by double hyphens (<TT>--</TT>).</P>
<P>The control file has more than 90 keywords that can be used to specify the format
of the data and how it is to be loaded. Because of the large number of options, I
cover only the key topics here. The entire list of keywords can be found in the Oracle
documentation.</P>
<P>The basics of the control file involve control statements that tell SQL*Loader
the following:

<UL>
	<LI>What operation to perform
	<P>
	<LI>Where to find the input datafile
	<P>
	<LI>Where to load the data to
	<P>
	<LI>The format of the data
</UL>

<P>The data can be of fixed length or delimited.
<H4><FONT COLOR="#000077"><B>Fixed Record Format</B></FONT></H4>
<P>When loading data that has a fixed record format (each field is the same length),
you must specify the length of the fields in the control file. Here is an example
of a fixed record control file:</P>
<PRE><FONT COLOR="#0066FF">LOAD DATA
INFILE `D:\database\load\dogs.dat'
INTO TABLE &quot;ETW&quot;.dogs2 (
Id    POSITION(01:02) INTEGER EXTERNAL,
Name  POSITION(05:08) CHAR,
OWNER_ID    POSITION(11:11) INTEGER EXTERNAL,
BREED_ID    POSITION(13:13) INTEGER EXTERNAL,
RANK    POSITION(15:16) INTEGER EXTERNAL,
NOTES    POSITION(18:20) CHAR)
</FONT></PRE>
<P>The various components consist of

<UL>
	<LI>A load directive
	<P>
	<LI>The input file specification
	<P>
	<LI>A table definition
	<P>
	<LI>A data format definition
</UL>

<P>This is enough information to define the load operation.
<H4><FONT COLOR="#000077"><B>Variable Record Format</B></FONT></H4>
<P>To load a variable record format datafile (all columns are not the same size),
you must specify a column delimiter. This column delimiter indicates to SQL*Loader
where one column finishes and another picks up. Here is an example of a variable
record control file:</P>
<PRE><FONT COLOR="#0066FF">LOAD DATA
INFILE `D:\database\load\dogs2.dat'
INTO TABLE &quot;ETW&quot;.dogs2
FIELDS TERMINATED BY &quot;,&quot; OPTIONALLY ENCLOSED BY `&quot;`
(id, name CHAR, owner_id, breed_id, rank, notes CHAR)
</FONT></PRE>
<P>The various components consist of

<UL>
	<LI>A load directive
	<P>
	<LI>The input file specification
	<P>
	<LI>A table definition
	<P>
	<LI>A data format definition
</UL>

<P>This provides SQL*Loader with enough information to load the data. There are a
few more optional parameters, which you will see next.
<H4><FONT COLOR="#000077"><B>Optional Parameters in the Control File</B></FONT></H4>
<P>There are numerous options available to SQL*Loader via the control file or at
the command line. Here are some useful options available to the loader:

<UL>
	<LI><TT>BAD=</TT>filename--The bad file filename. This is where bad data is logged.
	<P>
	<LI><TT>CONTROL</TT>filename--The name of the control file.
	<P>
	<LI><TT>DATA</TT>filename--The name of the input datafile.
	<P>
	<LI><TT>DIRECT=(TRUE or FALSE</TT>--This specifies whether the direct path loader
	is used. The direct path loader is described in the next section.
	<P>
	<LI><TT>PARALLEL=(TRUE or FALSE</TT>--This specifies a parallel load.
	<P>
	<LI><TT>LOAD</TT>n--The number of records to load. The default is <TT>all</TT>.
	<P>
	<LI><TT>LOG</TT>filename--The log file filename.
	<P>
	<LI><TT>ROWS</TT>n--The number of rows in each array insert.
	<P>
	<LI><TT>SKIP</TT>n--The number of logical records to skip.
	<P>
	<LI><TT>UNRECOVERABLE=(TRUE or FALSE</TT>--Available only with the direct path load.
	This parameter specifies that the load data operation does not log to the redo log
	files. This makes the loading operation faster but data cannot be recovered if the
	load fails.
</UL>

<H4><FONT COLOR="#000077"><B>Using the Direct Path Loader</B></FONT></H4>
<P>The conventional loading technique uses SQL <TT>INSERT</TT> statements to load
the data into the database. Each insert goes through all the logic and steps performed
in a standard <TT>INSERT</TT> statement. To improve performance, another option is
available: the direct path loader.</P>
<P>When you use the direct path loader, data is inserted directly into the datafiles,
thus bypassing much of the logic involved in the conventional path load. The direct
path loader is faster and more efficient, but there are a few restrictions on it:

<UL>
	<LI>You cannot direct path load a clustered table.
	<P>
	<LI>You cannot direct path load a table when active transactions are pending.
</UL>

<P>The advantages of using the direct path loader include the capacity to bypass
many of the steps taken by the conventional loader and the capability to load in
parallel. Another advantage is the capability to use the <TT>UNRECOVERABLE</TT> option.</P>
<P>With the <TT>UNRECOVERABLE</TT> option set, the load is not logged; thus, performance
is enhanced. Nonetheless, there is an inherent danger in using the <TT>UNRECOVERABLE</TT>
option: Because it is not logged, it is unrecoverable. Be sure to perform a backup
soon after using the <TT>UNRECOVERABLE</TT> option.
<H3><FONT COLOR="#000077"><B>Loading Using Data Manager</B></FONT></H3>
<P>To use the loader from Data Manager, select Data | Load. This invokes the Data
Manager Load wizard. The first screen, shown in Figure 9.11, prompts you to select
a control file, and you cannot proceed until you do so. Either type the name of the
control file or use the Browse function.</P>
<P><A NAME="11"></A><A HREF="11.htm"><B>Figure 9.11.</B></A></P>
<P><I>Screen one of the Data Manager Load wizard.</I></P>
<P>After you select the control file, proceed to screen two (shown in Figure 9.12).
Here you can fill out the following file descriptors:

<UL>
	<LI>Data File--Enter the name of the datafile.
	<P>
	<LI>Log File--Specify where the logging information is to be stored.
	<P>
	<LI>Bad File--Specify where records that have errors should be stored.
	<P>
	<LI>Discard File--Specify where rejected and uninserted records should be stored.
	<P>
	<LI>Parallel File--Specify whether direct loads can perform concurrent sessions.
</UL>

<P>These files will be used in the load if not already specified in the control file.</P>
<P><A NAME="12"></A><A HREF="12.htm"><B>Figure 9.12.</B></A></P>
<P><I>Screen two of the Data Manager Load wizard.</I></P>
<P>Screen three, shown in Figure 9.13, allows you to select advanced options such
as direct path loading, skipped records, records to load, and so on.</P>
<P><A NAME="13"></A><A HREF="13.htm"><B>Figure 9.13.</B></A></P>
<P><I>Screen three of the Data Manager Load wizard.</I></P>
<P>After you select from these options, you jump to screen five, the summary page,
which allows you to review your selections before you proceed. Screen four is displayed
only if you have chosen to schedule the load.</P>
<P>The Data Manager wizard makes it quite convenient to perform operations such as
exports, imports, and loads. But as with the other utilities, SQL*Loader can also
be run from the command line, as shown in the next section.
<H3><FONT COLOR="#000077"><B>Loading Using the SQLLDR80 Utility</B></FONT></H3>
<P>Invoke SQL*Loader on the command line by using the program SQLLDR80. This is the
Windows NT filename; under other operating systems, it may be known simply as SQLLDR.
All the options mentioned in the section about control files are available to SQL*Loader
from the command line.</P>
<P>Because most of the options can be placed in the control file, only a few command-line
options are necessary:

<UL>
	<LI><TT>USERNAME=</TT>username<TT>/</TT>password--This parameter specifies the username
	under which to run the loader.
	<P>
	<LI><TT>PARFILE</TT>parameter_file--This parameter optionally specifies the name
	of a parameter file that contains additional parameters.
	<P>
	<LI><TT>BAD</TT>filename--This parameter specifies the bad file filename. This is
	where bad data is logged.
	<P>
	<LI><TT>CONTROL</TT>filename--This parameter specifies the name of the control file.
	<P>
	<LI><TT>DATA</TT>filename--This parameter specifies the name of the input datafile.
	<P>
	<LI><TT>DIRECT=(TRUE or FALSE</TT>--This parameter specifies whether the direct path
	loader is used.
	<P>
	<LI><TT>PARALLEL=(TRUE or FALSE</TT>--This parameter specifies whether a parallel
	load can occur.
	<P>
	<LI><TT>LOAD</TT>n--This parameter specifies the number of records to load. The default
	is <TT>all</TT>.
	<P>
	<LI><TT>LOG</TT>filename--This parameter specifies the log file filename.
	<P>
	<LI><TT>ROWS</TT>n--This parameter specifies the number of rows in each array insert.
	<P>
	<LI><TT>SKIP</TT>n--This parameter specifies the number of logical records to skip.
</UL>

<P>These parameters are also available for use in the control file. If you are specifying
multiple parameters and running the loader frequently, I recommend putting the parameters
in the control file or in a parameter file.
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Today you learned the various methods for moving data in and out of the Oracle
database. The utilities described here are very useful and are frequently used.</P>
<P>I have included the Export and Import utilities here rather than in the backup
and recovery lessons because they have significantly more uses than just for backup
and recovery. The Export and Import utilities can be used to move data between systems
or to help reorganize a database, whereas the backup and recovery process has only
one use.</P>
<P>SQL*Loader can be used to load both fixed and variable length records into the
database. You saw both methods today.</P>
<P>Both a graphical and a command-line option are available with all these utilities.
The graphical option is available through the Enterprise Manager's Data Manager utility.
Command-line options are available through various utilities.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>On Day 10, &quot;Administering User Accounts,&quot; you will learn how to administer
user accounts in the Oracle RDBMS. You will see how users are defined and modified,
as well as profiles and roles. Administering user accounts is probably the most common
job of the Oracle DBA because new users are constantly being added or modified. You
will learn tomorrow how this task is simplified by using roles and profiles.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q Why would I use the Export/Import utilities?</B>
	<P><B>A</B> The Export/Import utilities can be used for several purposes, including
	backups, movement of data between systems, database rebuilds, and database reorganization.</P>
	<P><B>Q How is the loader different from Import?</B></P>
	<P><B>A</B> SQL*Loader is designed to load formatted datafiles of various formats,
	whereas the Import utility can only load export files.</P>
	<P><B>Q What kind of data can be loaded with SQL*Loader?</B></P>
	<P><B>A</B> Any data can be loaded with SQL*Loader.</P>
	<P><B>Q What is the difference between the conventional path loader and the direct
	path loader?</B></P>
	<P><B>A</B> The conventional path loader essentially loads the data by using <TT>INSERT</TT>
	statements, whereas the direct path loader bypasses much of the logic involved with
	that and loads directly into the datafiles.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. Find answers to the quiz questions in Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1. </B>What is Export used for?
	<P><B>2.</B> What is Import used for?</P>
	<P><B>3.</B> What is SQL*Loader used for?</P>
	<P><B>4.</B> What Oracle utilities can be used to load export files?</P>
	<P><B>5. </B>Name two uses of Export/Import.</P>
	<P><B>6. </B>What is the name of the Windows NT version of the Export utility?</P>
	<P><B>7.</B> What is the name of the Windows NT version of the Import utility?</P>
	<P><B>8. </B>What is the name of the Windows NT version of SQL*Loader?</P>
	<P><B>9. </B>What is the different between a fixed and a variable record load file?</P>
	<P><B>10.</B> What is the parameter file?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1. </B>Perform an export using Data Manager.
	<P><B>2.</B> Perform an export using the EXP80 utility.</P>
	<P><B>3.</B> Import the data using Data Manager.</P>
	<P><B>4.</B> Import the data using the IMP80 utility.</P>
	<P><B>5. </B>Load a small dummy table using the load option in Data Manager.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<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> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>