Previous | Table of Contents | Next

Page 591

your export will run (and the more system memory will be used). You can compute the number of rows that will fit into the buffer as follows:

rows=buffer_size/maximum_row_size

If the table contains a LONG column, only one row will be in the buffer at one time.

OWNER lists the schemas you want to export. Use this keyword if you are exporting full schemas. If ROWS=N, only the SQL code needed to regenerate a schema will be exported—no table rows.

FILE is the name of the export file. You should use a filename that is reasonably descriptive of the data contained. In some OSs (UNIX in particular), you can export directly to a tape device. This is particularly useful for large database backups when you don't have enough disk space anywhere for the export file.

CAUTION
When you specify a filename for the FILE keyword, be aware that these files can be rather large, depending on what you are exporting. In OSs like UNIX, you can export directly to tape, which eliminates this problem. If this is not an option, you will have to break up your export into small enough pieces to fit in either several different filesystems or in one location, so that each piece can be moved off to tape before the next piece is created in a subsequent EXP run. You may want to run EXP during off hours to reduce the impact of a full filesystem.

TABLES lists individual tables you want to export. If ROWS=N, only the SQL code needed to regenerate the specified tables is exported; no rows are exported.

COMPRESS has many implications when it is time to import the data. COMPRESS=Y will cause IMP to construct the initial extent of a new object to be large enough to hold all the object's data. For instance, if a table has four extents and each one is 20MB in size, the initial extent of the new table will be approximately 80MB. If COMPRESS=N, the new table will have four 20MB extents just as the original did.

CAUTION
Although COMPRESS=Y is very useful when you are trying to reorganize a table (to reduce the number of extents, thereby improving performance), you need to do some homework before using this option. Suppose you have a table, "ORDERS," that has these extent sizes: 30MB, 100MB, 1000MB, 1500MB, 1500MB, and 1000MB. The total size of this table is a little more than 5GB. Now suppose your machine only has disk drives of 2GB and 4GB. The largest single extent your system could have is 4GB, yet Oracle will expect to find a 5GB extent. The import process will not work because Oracle will be unable to allocate a 5GB extent. Before using COMPRESS=Y, use the following SQL statement on each table you intend to export:

SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE SEGMENT_NAME=<table name>

AND OWNER=<table owner>

Using this statement will help make sure you can accommodate the table in a single extent.

Page 592

RECORDLENGTH is used for moving export files to an Oracle database on another operation system. If you are moving from system A to system B, you must know what the record size is on system B. This information is found in the operating system documentation supplied by Oracle.

GRANTS indicates to EXP whether to export GRANTS associated with objects being exported.

INCTYPE specifies a COMPLETE, CUMULATIVE, or INCREMENTAL backup. You must use a USERID with EXP_FULL_DATABASE because this option is only useful with FULL=Y. In most OLTP production environments, this capability is seldom used.

NOTE
A CUMULATIVE or INCREMENTAL export will export an entire table if it has changed. EXP will not just export changed rows. If you do choose to use this option, be sure you regularly perform a COMPLETE export to keep your baseline export current.n

INDEXES indicates if you want to export the indexes on the tables being exported.

RECORD specifies whether or not Oracle keeps track of a COMPLETE, CUMULATIVE, or INCREMENTAL export. This is useful in the following case:

You normally run a COMPLETE on Sunday and CUMULATIVEs on Monday through Friday. Tuesday night you plan to reload the test system with a COMPLETE export of the production system. If RECORD=Y when you exported the production system Tuesday night, Wednesday's CUMULATIVE export will be based on Tuesday night's COMPLETE instead of Sunday's. Basically, RECORD=N will be used if you are running an export on the database out of sequence with your normally scheduled exports.

ROWS indicates whether or not you want to export the data contained in the tables you are exporting. If ROWS=N, only the SQL code needed to re-create the table will be exported.

PARFILE is the name of a parameter file containing any valid keyword settings used for EXP. Here's an example of a parameter file:

TABLES=CUSTOMER,VEDOR
OWNER=SUSAN
COMPRESS=N
RECORD=N

As you can see, there's nothing fancy about a parameter file. It only has a keyword and value on each line. This is particularly useful if you have a standard export that you need to run periodically because you don't need to worry about forgetting to set a keyword.

CONSTRAINTS indicates whether or not you want to export constraints associated with the objects you are exporting.

CONSISTENT is useful if you are exporting tables while they are also being updated. CONSISTENT=Y ensures that each table in the export is consistent to a single point in time. If EXP encounters an updated row while exporting a table, it will use the rollback infor- mation and export the old version. If a new row is inserted, EXP will ignore it. Do not use

Page 593

CONSISTENT=Y unless it is needed because EXP will keep a rollback segment during the entire export. Because this also places extra load on your database, it is an option best used during off hours.

If you encounter a Snapshot too old error, you probably need to run your export during a period of lighter system load. You may even need to run it with the database open in RESTRICT mode to keep users from updating the database during your export. You may also need to export while in RESTRICT mode if you need a consistent view of several tables. CONSISTENT=Y provides read consistency only on a per-table basis.

LOG is a filename where you would like to save the export messages and errors. Normally, EXP just displays this information onscreen. However, because the information may race by too quickly for you to read, you'll probably want to save your EXP log output to file. All the messages and errors are shown on your screen, even when you specify a LOG filename.

TIP
If you are using a UNIX system, you can redirect EXP's screen output to /dev/null if you would rather not see the messages fly by on your monitor. If EXP is displaying a large amount of information, it will usually run faster if you redirect the output to /dev/null. This is because it is much faster for UNIX to simply throw away the information than send it to your screen.

STATISTICS specifies how statistics are generated when the exported file is imported. The options are COMPUTE, ESTIMATE, or NONE. If your export is large, setting STATISTICS to ESTIMATE or NONE will save you time (NONE saves the most). You can always use the ANALYZE TABLE command later to generate the statistical data.

DIRECT is a recent enhancement of EXP. It greatly improves exporting performance by reading the data blocks directly (rather than going through the normal database engine). Generally, if you have this option, you should use it! Your exports will run much faster.

FEEDBACK is a recent addition to EXP. If you set FEEDBACK=n (where n is some positive integer) EXP will display a period (.) for every n rows it exports. Setting FEEDBACK=0 disables this feature.

You can enter as many keywords as you want in any order on the command line. Use the following syntax:

exp [<keyword>=<value>[,<value>,...] [<keyword>=<value>[,<value>...] ...]]

Generally, I prefer to use EXP entirely in batch mode because I can easily repeat the command, either unchanged or with minor modifications. If EXP needs more information than you provided on the command line, such as the user ID/password, it will prompt you to enter it. To run EXP entirely interactively with no keywords, just type in exp at your system's command prompt. EXP will prompt you for all the information it needs.

Full Logical Backups

EXP is particularly well suited for full logical database backups. The only requirements are that you must have the EXP_FULL_DATABASE privilege and you must have enough storage space for

Previous | Table of Contents | Next