Data export is a common task for an Oracle DBA, but not necessarily a straightforward one. DBAs have to know how to handle Oracle export errors, work with multiple tables and rows and export stored procedures and functions. In this guide, you'll learn best practices for exporting Oracle data, with step-by-step instructions and example scripts. Also, hear Oracle data export advice from Oracle experts Eli Leiba, Karen Morton and Brian Peasland, including how to:
- Export an Oracle table
- Export stored procedures in Oracle
- Perform a remote export of Oracle data
- Write an Oracle data export script
Solve Oracle export errors
Export an Oracle table
According to Oracle expert Karen Morton, exporting a single table -- even one with as many as 40,000 rows -- is a rather straightforward procedure. Here is her explanation:
"From your OS prompt, run the EXP utility either interactively or by putting all the parameters for the export on the command line. To run EXP interactively (i.e. to have it prompt you through the steps of the export), just type EXP username/password@instance and answer the questions when prompted. You can also just put all the parameters on the command line similar to the following:
EXP username/password@instance TABLES=(tablename)
Oracle expert Brian Peasland gives five easy steps for recreating the export on a different server (assuming the "myoracle" instance):
- Export the database on Server A with the FULL=Y parameter.
- FTP the export dump file to Server B. Make sure you FTP in BINARY mode, not the default ASCII mode!
- Create the database on Server B. You can create this database using the Oracle Database Configuration Assistant if you choose.
- If you directory structure for your tablespaces is different on Server B, then you'll have to create those tablespaces as well.
- Import the dump file with the FULL=Y parameter.
You may find yourself with the question: Where is the export data stored? Read Peasland's explanation of where this data -- also called a dump file -- ends up after an export. Morton adds that the export dump file is written in an Oracle binary format and can only be read by the import utility.
After you perform the Oracle data dump, you may realize that the dump is missing trigger statements. Here is Peasland's advice for avoiding this:
"The only way a dump, or export, will contain the triggers is to export the FULL contents of the database or export the entire schema OWNER. If you specify FULL=Y as a parameter to your export, then you will get all triggers, procedures, views, sequences, etc. If you specify OWNER=user_name as a parameter to your export, then you will get all objects that is owned by that user including triggers. The TABLES parameter will not export objects like triggers, views, sequences, etc.
If you want to verify if a trigger or other object is in an export file, use the SHOW=Y parameter for import. Make sure you log the output to a logfile. Then browse the logfile. All DDL statements in the export dump will be shown." To get a more detailed overview of I don't specifically know if export/import can handle comp-3 but I can't find anything to confirm that it does, so I'd say that it does not. I do know you can write a Pro*Cobol program and pass data from a number column to an indicator variable of type comp-3. Using a Pro*Cobol program may be an alternative way to write your own export/import."
Export stored procedures in Oracle
A stored procedure is a set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. (From a Whatis.com definition). Brian Peasland gives the following explanation on why it's possible to export stored procedures in Oracle -- and how to do so:
"Unfortunately, there is no export parameter like TABLE for procedures/function/packages. It would be nice to have a parameter for export like 'FUNCTION=my_function'. The main reason that this parameter doesn't exist is that many people just reverse engineer the proc/fn/pkg definition. There are many scripts out on the Web to reverse engineer the SQL statements to re-generate the proc/fn/pkg. There is no data involved with these objects, so no real export is necessary.
Also, Oracle 9i has the DBMS_METADATA package to reverse engineer objects for you.
If you do need to use export for proc/fn/pkg exports, your only option is a FULL export. Use the ROWS=N parameter so that no data is exported. You'll get all database objects this way! You can use the SHOW=Y parameter on import to see the SQL statements for a proc/fn/pkg."
Read another explanation on exporting stored procedures from Peasland in this expert response: Exporting user database from stored procedure.
Perform a remote export of Oracle data
In addition to exporting a database from a stored procedure, it's also possible to do an import and export of a target database from a remote machine. Here's expert Karen Morton's description of how to use import and export, which she says can be done as long as you can connect to the database and have the exp and imp utilities loaded on the local computer:
"Simply go to your OS prompt, type in either imp or exp and hit enter. It will prompt you to log in and after you successfully log in, it will step you through the options for your import or export."
Note: Doing an export/import when not directly connected to the database (i.e. actually connected at the server computer where the database is located) will be slow. If you can telnet into the database server remotely and then run the exp or imp utility that way, it'll be faster.
Here are some more resources for a remote export of Oracle data:
- Learn why one user is getting the IMP-00009 error during import to a remote machine. What does expert Brian Peasland say is the most likely cause of this Oracle error message?
- Read Morton's instructions for exporting a DB using Oracle Console.
- If you're working off an earlier version of Oracle, learn the procedure for getting data from a remote 8i database to 9i.
Read a Q & A with Oracle expert Dan Norton on migrating data to a remote server.
Write an Oracle data export script
Setting up an export script is a key part of performing a data export. Here are two different data export scenarios and an example script for each situation:
How to write the EXP script for a back-up operation on DOS:
Brian Peasland says: "For an export script, I'd use something similar to the following:
set ORACLE_SID=mysid set ORACLE_HOME=c:oracleora9i exp userid=system/manager file=c:exportsmysid.dmp log=c:exportsmysid.log full=y
Save those three lines in a text file. Make sure that the text file has a ".bat" or ".com" extension so that it will run as a program. Also make sure that you change the values for ORACLE_SID, ORACLE_HOME and any parameters for calling the exp utility. Now that this batch program is written, you can schedule it through Windows scheduler (or 'at')."
A script for performing an export on multiple tables (say 50) from a database running on Oracle 8i:
Karen Morton says: "Unless you write a dynamic script to build the export parameter file, you'll have to list all the tables individually. You could build a dynamic parameter file by using something like this:
set heading off select decode( rownum, 1, 'tables=(', ',' ), table_name from user_tables where table_name like 'E%' union all select ')', null from dual ;Would return...
tables=( E , EMP , EMP2 , EMPLOYEE , EMP_DEMO , EMP_DEPT , EMP_SNAPSHOT )
(spool that to a file) and use parfile=<name of your spooled file> on the EXP command line.
Note that in order for this to work, you'll need to modify your WHERE clause to pull the tables you want. In this example, I just pulled all tables that started with the letter E."
Oftentimes Unix scripts are used for Oracle data exports. With this example Unix script, learn how to create a script that will run via a crontab job. Peasland also explains how to run the script in its own shell.
Solve Oracle export errors
It's possible that one of the many common Oracle errors may occur when exporting a table or database. Here is an example of one export error involving the Oracle export utility (EXP-00002: error in writing to export file) and Peasland's solution for solving it:
When using Oracle's exp command, the data fails to export:
exp-00002: error in writing to export file exp-00000: Export terminated unsuccessfully
The database is Oracle 8.1.7 on Windows NT, and the export is being run from the Windows command.
Brian Peasland says: "This error is telling you that the Oracle export utility can no longer write to this disk device. This can be caused by a couple of things. The disk device could be full. Or the dump file could exceed a 2 GB file size limit. I've also seen this happen when people try to export to a network attached (mapped) drive and the network connection is not stable enough."
Here are some other data export errors and solutions recommended by our experts:
- ORA-1115 error during export: What is the ORA-1115 error telling this Oracle user?
- Error during export of database: This user is seeing the ORA- 6550 error when exporting a database. Learn about the most common cause of this error.
- ORA-01555 error when exporting table with BLOB column: What is this error indicating?
- Receiving error at export command: Read about the Oracle error that indicates incompatible versions of Oracle export utility and database version.