Exporting Oracle data
By Sara Cushman, Assistant Site Editor
02 Apr 2003 | SearchDatabase.com
Exporting data is a common task for an Oracle DBA, but not necessarily a straightforward one. In this compliation of questions and answers, SearchDatabase.com's resident Oracle experts Eli Leiba, Karen Morton and Brian Peasland explain how it's done, with step-by-step instructions and example scripts, and talk about what can and cannot be exported.
Can you tell what kind of format the dump file is written in?
Karen Morton: If you are asking about an export dump file, the format is an Oracle binary format and can only be read by the import utility.
I have a table with 40,000 rows, and I would like to export/import the data from this table. Can you please provide me with an example on how to do this?
Karen Morton: Exporting a single table is pretty straight forward. 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)
To get a more detailed overview of how to use the export utility, take a look at the Oracle Utilities document (you can find this on the http://technet.oracle.comsite).
Can you list clear steps to export a database dump and recreate the same database on another server? Assume "myoracle" instance.
Brian Peasland:
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.
How do you export a stored procedure/function/package? Is it possible?
Brian Peasland: Yes, it is possible. But 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.
How can we do an import and export of a target database from a remote machine?
Karen Morton: As long as you can connect to the database and have the exp and imp utilities loaded on the local computer, you can use import and export. 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.
What is the maximum size for export/import migrations--MB/GB/TB?
Eli Leiba: In my experience, export/import is efficient from MB to several GB (10GB) and not more. The export and import process in this case takes 1 to 3 hours, depending on the strength of the Oracle machine. Usually when you do export/import, you move your schema to production, and this process takes time. In cases where the database is bigger than several GB (Terabytes), don't use export/import, use cold backups.
I use Oracle 8i on Windows 2000 Advanced Server. Will I be able to take a .dmp backup using the EXP utility on my schema (all my tables and objects), while other users are using the database? Is this EXP command to be issued from the command prompt, or can it also be issued from svrmgr?
Brian Peasland: Yes, you can run EXP for a schema will others are signed on to the database. But to make sure that everything is consistent, set the parameter CONSISTENT=Y. You run EXP from the command line. But I wouldn't rely on EXP for my backup/recovery strategy. Instead, you will probably want to employ cold backups. Please refer to the Oracle Backup & Recovery Guide for complete information.
Can we take an incremental export of a table in 7.3.4.0.0?
Brian Peasland: From the Oracle documentation, "You can do incremental, cumulative, and complete exports only in full database mode". Therefore, you cannot do an incremental export of just a single table.
Could you please show me how to write the EXP script for a back-up operation on DOS?
Brian Peasland:
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').
How can I perform an export on multiple tables (say 50) from my database running on Oracle 8i? What is the script like?
Karen Morton: 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=
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.
I am wondering if it is possible using any version of Oracle to EXPORT from a view or EXPORT from a table using a WHERE clause. If not EXPORT, are there any other utilities available to perform this task?
Karen Morton: Since views are not "real" tables, there is no data associated with them... so you can't export a view. When you export a view, you're simply exporting the definition of the view (in other words, the SELECT statement). But, starting with 8i, there is a QUERY parameter you can use to export a table conditionally using a WHERE clause.
Here are the details from the Oracle documentation Utilities Guide: This parameter allows you to select a subset of rows from a set of tables when doing a table mode export. The value of the query parameter is a string that contains a WHERE clause for a SQL SELECT statement that will be applied to all tables (or table partitions) listed in the TABLE parameter. For example, if user SCOTT wants to export only those employees whose job title is SALESMAN and whose salary is greater than 1600, he could do the following (note that this example is UNIX-based):
exp scott/tiger tables=emp query="where job='SALESMAN' and sal<1600"
Note: Because the value of the QUERY parameter contains blanks, most operating systems require that the entire strings where job='SALESMAN' and sal<1600 be placed in double quotation marks or marked as a literal by some method. Operating system reserved characters also need to be preceded by an escape character. See your operating system-specific documentation for information about special and reserved characters on your system.
When executing this command, Export builds a SQL SELECT statement similar to this:
SELECT * FROM EMP where job='SALESMAN' and sal < 1600;
The QUERY is applied to all tables (or table partitions) listed in the TABLE parameter. For example, the following statement will unload rows in both EMP and BONUS that match the query:
exp scott/tiger tables=emp,bonus query="where job='SALESMAN' and
sal<1600"
Again, the SQL statements that Export executes are similar to these:
SELECT * FROM EMP where where job='SALESMAN' and sal < 1600;
SELECT * FROM BONUS where where job='SALESMAN' and sal < 1600;
If a table is missing the columns specified in the QUERY clause, an error message will be produced and no rows will be exported for the offending table.
Restrictions
The parameter QUERY cannot be specified for full, user, or transportable tablespace mode exports.
The parameter QUERY must be applicable to all specified tables.
The parameter QUERY cannot be specified in a direct path export (DIRECT=Y)
The parameter QUERY cannot be specified for tables with inner nested tables.
You will not be able to determine from the contents of the export file whether the data is the result of a QUERY export.
Can you tell me how to export a table to a text file in ebcdic character set and numeric columns in comp-3 format?
Karen Morton: 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.
I did an Oracle dump, but I'm missing some trigger statements. Why am I missing them, and how can I avoid this situation?
Brian Peasland: 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.
I am trying to export the data using Oracle's exp command, but it 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 I am running the export from the Windows command.
Brian Peasland: This error is telling you that the 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.
reff:http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci905148,00.html?Exclusive=True