Oracle data export guide

[IMAGE][IMAGE][IMAGE]

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: Oracle expert Brian Peasland gives five easy steps for recreating the export on a different server (assuming the "myoracle" instance):

Brian Peasland:

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 t

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database export, import and migration Solving common Oracle errors guide Oracle and SQL Server: BFF? Oracle as data integrator Importing from Oracle 8i to Oracle 10g Export schemas of users of database Exp/imp for complex cross-platform migration and upgrade How to export data by date in Oracle Large data migration from XML database to Oracle 10g IMP-00017 error message with Oracle import LOB tables missing with exp/imp move of schema

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

he 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 how to use the Oracle export utility,Morton suggests taking a look at the Oracle Utilities document on the Oracle site.

What about exporting a table to a text file in ebcdic character set and numeric columns in comp-3 format? Morton has this to say:

"Oracle will export in the character set given by user's session environment variable NLS_LANG. But the problem is, if your nls_lang is not the same or superset of the database character set, you will or may lose certain types of characters. So, the answer is to check the NLS_LANG variable and make sure the character sets in use by your session and by the database are related. If not, change the NLS_LANG variable for your session. See the Oracle Utilities Guide for more information.

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:

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:

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:

Would return... (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:

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: