The Database Design Resource Center

Oracle Export : Extracting data.

Oracle Export provides a simple way for you to transfer data objects between Oracle databases, even if they reside on platforms with different hardware and software configurations.

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

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.