0389-0393 | Oracle Development Unleashed (3rd Edition)

Previous Table of Contents Next

Page 389

CHAPTER 17

Import and Export

IN THIS CHAPTER

Export 390
Import 403
Importing and Exporting with Personal Oracle7 411
Import and Export Hints and Tips 412

Page 390

Import and Export are two complementary utilities supplied by Oracle. You use the two utilities together primarily to back up and restore data, move data to other Oracle databases, and migrate data from an older Oracle version to a newer version. The following lists some other uses of Import and Export:

Store data in operating system files for archiving
Store database object definitions
Selectively back up parts of a database
Move data from one Oracle user's schema to another
Move data from one hardware platform or operating system to another
Save space and increase performance by reducing fragmentation

The operation of the Import and Export utilities is quite straightforward. Export writes information about the tables or database objects and data from Oracle tables themselves, such as table creation, index creation, table grants, sizing information, and so on. Export then saves this information to named operating system files. The operating system files that Export creates are known as dump files. The dump files, which are in Oracle binary format, are useful only to the Import utility, for the most part. You can name the dump file whatever your operating system allows; if you don't specify an output filename for Export to use, it typically defaults to EXPDAT.DMP.

You then can archive the output files produced by Export to disk or offline for safekeeping, or Import can use them to recreate the exported data for recovery or maintenance purposes.

NOTE

Import reads only files created by Export.

Export

Sometimes you find that you desperately need something and realize that you should have never put yourself in the position of needing it in the first place. Take Import and Export, for example. If the world were perfect, you would never need to import data and therefore never need to export. I am certain that I would still be in hot water with a few CEOs someplace if I did not export as often as I do. I find the Export utility to be the easiest method for saving myself great pain later.

Export is a comprehensive utility supplied by Oracle. It is fairly easy to use with quite a bit of flexibility built into the utility through the use of a large parameter list. The parameter options enable Export to provide solutions to complex data-management problems. On the surface, Export appears to be a simple utility, but it has the power to be an efficient backup utility and

Page 391

enables the database administrator to perform important maintenance functions for Oracle databases. Export can write operating system files that you can move to a different operating system or a different version of Oracle.

Before using Export and Import, you need to run the script CATEXP.SQL or CATALOG.SQL (which runs CATEXP.SQL). You need to do this only once. You do not need to run the script again before future exports.

Check that the storage device has enough free space to hold the export file. You might want to use the user_segments view to help estimate the disk space required.

The following code shows an example of using Export:

 exp userid=system/manager OWNER=scott... [other options]

The following code shows an example of using Export with a parameter file:

exp userid=system/manager parfile=filename

Using a Parameter File

You can use a parameter file with both Export and Import. The parameter file can help with imports and exports by providing consistency and simplicity. Using a parameter file in a nightly export is a great idea. Parameter files ensure consistent exports so you can ensure you actually export all the tables that need to be exported. You can start both the command-line and the parameter methods from a shell script, but the command-line method might be too long for your operating system to accept with many parameters. The export_ts script writes the parameter file that it needs. Remember that you cannot use the PARFILE parameter in the parameter file. Table 17.1 illustrates all the parameter options. Comments in a parameter file are preceded by a # symbol. If you are exporting tables that contain the # symbol in the name, they must be enclosed in quotation marks. Table names in quotation marks are case sensitive.

The following code illustrates a sample parameter file:

 # use the o/s for the user id and the password USERID=/ # write out a log file LOG=../archive/export_logs/scott_export BUFFER=64000 COMPRESS=Y #name the export file FILE=../archive/export_data/export_ts.dmp #list the tables to export TABLES= SCOTT.BONUS, SCOTT.CUSTOMER, SCOTT.DEPT, SCOTT.EMP, SCOTT.EXECUTABLES, SCOTT.FALL5, SCOTT.ITEM, SCOTT.ORD,

Page 392

 SCOTT.PRICE, SCOTT.PRODUCT, SCOTT.QUOTA, SCOTT.SALES1, SCOTT.SALES2, SCOTT.SALGRADE

Table 17.1. Parameter list for Export.

Keyword	Description	Default
USERID	Username/password of the account performing the export.
BUFFER	The size of the buffer used to fetch rows. If this is set to zero, only one row at a time is fetched. Oracle provides a good rule of thumb for estimating the buffer size: buffer = rows_in_array * max_row_size If your table has long data types, Export must fetch one row at a time.
FILE	Output filename created by Export.	EXPDAT.DMP
GRANTS	Indicates whether to export object grants.	Y
INDEXES	Indicates whether to export index creation statements.	Y
ROWS	Indicates whether to export table data.	Y
CONSTRAINTS	Indicates whether to export table constraints.	Y

Page 393

Keyword	Description	Default
COMPRESS	Indicates whether Export should rewrite the storage parameters of the tables and indexes in the export file.	Y
FULL	Indicates whether this is a full export. If FULL=Y, then use the INCTYPE parameter to specify if the export is COMPLETE (default), CUMULATIVE, or INCREMENTAL.	N
OWNER	A list of owners who will have their objects exported. OWNER=scott or OWNER= (scott,temp)
TABLES	A list of tables to export. TABLES=emp or TABLES=(emp,dept)
RECORDLENGTH	The length of the file record in bytes. Used to set the record length when transferring the export file to a different operating system.
INCTYPE	Used when FULL=Y. Valid options are COMPLETE, CUMULATIVE, and INCREMENTAL. If Export is used as a backup and recovery scheme, using the INCTYPE=CUMULATIVE or INCREMENTAL parameters

 continues

Previous Table of Contents Next