Oracle Export : Extracting data.

Oracle Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format Export dump file located on disk or tape.

Such files can then be FTPed or physically transported to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.

There are a few points regarding the enforcement of Oracle security policies.

• For any tables protected by an Oracle security policy, only rows with labels authorized for read access will be exported; unauthorized rows will not be included in the export file. Consequently, to perform an Oracle export of all the data in protected tables, you must have a privilege (such as FULL or READ) which gives you complete access.
• SQL statements to reapply policies are exported along with tables and schemas that are exported. These statements are executed during import to reapply policies with the same enforcement options as in the original database.
• The HIDE property is not exported. When protected tables are exported, the label columns in those tables are also exported (as numeric values). However, if a label column is hidden, it is exported as a normal, unhidden column.
• The LBACSYS schema cannot be exported due to the use of opaque types in Oracle Label Security. To export an entire database, you must individually specify all of the schemas and/or tables (except for the LBACSYS schema). Use standard Oracle backup techniques to back up the LBACSYS schema.

If a user attempts to access rows containing invalid numeric labels, the operation will fail.

To use Oracle Export, you must run the script CATEXP.SQL or CATALOG.SQL (which runs CATEXP.SQL) after the database has been created.

CATEXP.SQL or CATALOG.SQL needs to be run only once on a database. You do not need to run it again before you perform the export. The script performs the following tasks to prepare the database for Export:

• Creates the necessary export views,
• assigns all necessary privileges to the EXP_FULL_DATABASE role
• assigns the EXP_FULL_DATABASE role to the DBA role

Before you run Oracle Export, ensure that there is sufficient disk or tape storage space to write the export file.

If there is not enough space, Oracle Export terminates with a write-failure error.

You can use table sizes to estimate the maximum space needed. Table sizes can be found in the USER_SEGMENTS view of the Oracle data dictionary. The following query displays disk usage for all tables:

SELECT SUM(bytes) FROM user_segments
WHERE segment_type='TABLE';

The result of the query does not include disk space used for data stored in LOB (large object) or VARRAY columns or partitions.

You can invoke Oracle Export in one of the following ways:

• Enter the following command: exp username/password PARFILE=filename. PARFILE is a file containing the export parameters you typically use. If you use different parameters for different databases, you can have multiple parameter files. This is the recommended method.
• Enter the command: exp username/passwordfollowed by the parameters you need. Note: The number of parameters cannot exceed the maximum length of a command line on the system... (Which shows that some parts of Oracle are still VERY old...)
• Enter only the command exp username/password to begin an interactive session and let Export prompt you for the information it needs. The interactive method provides less functionality than the parameter-driven method. It exists for backward compatibility.

You can use a combination of the first and second options. That is, you can list parameters both in the parameters file and on the command line.

In fact, you can specify the same parameter in both places. The position of the PARFILE parameter and other parameters on the command line determines what parameters override others.

For example, assume the parameters file params.dat contains the parameter INDEXES=Y and Oracle Export is invoked with the following line:

exp system/manager PARFILE=params.dat INDEXES=N

In this case, because INDEXES=N occurs after PARFILE=params.dat, INDEXES=N overrides the value of the INDEXES parameter in the PARFILE.

You can specify the username and password in the parameter file, although, for security reasons, this is not recommended. If you omit the username/password combination, Oracle Export prompts you for it.

Return to Oracle DBA