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:
New Term: 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 export file. 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
As you can see, there are several different reasons for using Export and Import.
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.
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.
NOTE: 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.
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.
NOTE: Before you can run Export and Import, the administrative SQL script CATEXP.SQL must have been run once on the database. The administrative SQL script CATALOG.SQL, which is typically run at database-creation time, automatically runs CATEXP.SQL.
As with almost every Oracle utility, the export function can be accomplished graphically via Data Manager or by using the EXP80 program.
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.
NOTE: Data Manager must be run by a user with DBA privileges if a full export is to be performed.
Screen one of the Data Manager Export wizard.
TIP: 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.
Screen two of the Data Manager Export wizard.
5. 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.
6. 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.
Screen five of the Data Manager Export wizard.
Screen seven of the Data Manager Export wizard.
Screen six of the Data Manager Export wizard.
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.
NOTE: Under Windows NT, the export program is called EXP80. Under other operating systems, it may be known simply as EXP.
Export has several options and modes of operation:
NOTE: That the SYS schema objects are not exported in the full mode is another good reason not to create objects under the SYS schema.
Export can also be used to export partitions in a partitioned table. This is accomplished by exporting in table mode and specifying the table partition.
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.
Export is invoked with the following syntax:
EXP80 username/password [ options ... ]
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:
EXP80 username/password PARFILE=filename [ options ... ]
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:
The Export parameters are
Here is an example of a parameter file:
FULL=Y BUFFER=8192 FILE=D:\database\export\EXPDAT.DMP STATISTICS=COMPUTE
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.
D:\>exp80 Export: Release 22.214.171.124.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 126.96.36.199.2 - Beta With the distributed, heterogeneous, replication, objects and parallel query options PL/SQL Release 188.8.131.52.2 - Beta Enter array fetch buffer size: 4096 > Export file: EXPDAT.DMP > (2)U(sers), or (3)T(ables): (2)U > Export grants (yes/no): yes > Export table data (yes/no): yes >
Compress extents (yes/no): yes >
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.
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.
As described earlier today, Export and Import can be used for several different functions:
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.
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.
Screen one of the Data Manager Import wizard.
Screen two of the Data Manager Import wizard.
4. 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.
5. 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.
Screen five of the Data Manager Import wizard.
7. 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.
The Data Manager Import wizard's Import Status screen.
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.
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.
Import is invoked with the following syntax:
IMP80 username/password [ options ... ]
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:
IMP80 username/password PARFILE=filename [ options ... ]
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:
The Import parameters are
Here is an example of a parameter file:
FULL=Y BUFFER=8192 FILE=D:\database\export\EXPDAT.DMP STATISTICS=ESTIMATE
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.
D:\>imp80 Import: Release 184.108.40.206.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 220.127.116.11.0 - Production With the Partitioning and Objects options PL/SQL Release 18.104.22.168.0 - Production Import file: EXPDAT.DMP > d:\database\export\EXPDAT.DMP 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 Enter table(T) or partition(T:P) name or . if done:
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.
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.
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.
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.
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.
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 (--).
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.
The basics of the control file involve control statements that tell SQL*Loader the following:
The data can be of fixed length or delimited.
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:
LOAD DATA INFILE `D:\database\load\dogs.dat' INTO TABLE "ETW".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)
The various components consist of
This is enough information to define the load operation.
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:
LOAD DATA INFILE `D:\database\load\dogs2.dat' INTO TABLE "ETW".dogs2 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY `"` (id, name CHAR, owner_id, breed_id, rank, notes CHAR)
The various components consist of
This provides SQL*Loader with enough information to load the data. There are a few more optional parameters, which you will see next.
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:
The conventional loading technique uses SQL INSERT statements to load the data into the database. Each insert goes through all the logic and steps performed in a standard INSERT statement. To improve performance, another option is available: the direct path loader.
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:
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 UNRECOVERABLE option.
With the UNRECOVERABLE option set, the load is not logged; thus, performance is enhanced. Nonetheless, there is an inherent danger in using the UNRECOVERABLE option: Because it is not logged, it is unrecoverable. Be sure to perform a backup soon after using the UNRECOVERABLE option.
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.
Screen one of the Data Manager Load wizard.
After you select the control file, proceed to screen two (shown in Figure 9.12). Here you can fill out the following file descriptors:
These files will be used in the load if not already specified in the control file.
Screen two of the Data Manager Load wizard.
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.
Screen three of the Data Manager Load wizard.
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.
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.
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.
Because most of the options can be placed in the control file, only a few command-line options are necessary:
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.
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.
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.
SQL*Loader can be used to load both fixed and variable length records into the database. You saw both methods today.
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.
On Day 10, "Administering User Accounts," 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.
A The Export/Import utilities can be used for several purposes, including backups, movement of data between systems, database rebuilds, and database reorganization.
Q How is the loader different from Import?
A SQL*Loader is designed to load formatted datafiles of various formats, whereas the Import utility can only load export files.
Q What kind of data can be loaded with SQL*Loader?
A Any data can be loaded with SQL*Loader.
Q What is the difference between the conventional path loader and the direct path loader?
A The conventional path loader essentially loads the data by using INSERT statements, whereas the direct path loader bypasses much of the logic involved with that and loads directly into the datafiles.
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, "Answers."
2. What is Import used for?
3. What is SQL*Loader used for?
4. What Oracle utilities can be used to load export files?
5. Name two uses of Export/Import.
6. What is the name of the Windows NT version of the Export utility?
7. What is the name of the Windows NT version of the Import utility?
8. What is the name of the Windows NT version of SQL*Loader?
9. What is the different between a fixed and a variable record load file?
10. What is the parameter file?
2. Perform an export using the EXP80 utility.
3. Import the data using Data Manager.
4. Import the data using the IMP80 utility.
5. Load a small dummy table using the load option in Data Manager.
© Copyright, Macmillan Computer Publishing. All rights reserved.