[Developer]

Oracle DBA FAQ - Loading and Exporting Data Tutorial

A collection of 27 FAQs on Oracle loading data and exporting data. Clear answers are provided with tutorial exercises on saving data as flat files, loading data from flat, exporting and importing database, schema and tables, creating external tables. Topics included in this FAQ are:

1. What Is the Simplest Tool to Run Commands on Oracle Servers?
2. What Is the Quickest Way to Export a Table to a Flat File?
3. How To Export Data with a Field Delimiter?
4. What Is SQL*Loader?
5. What Is a SQL*Loader Control File?
6. How To Load Data with SQL*Loader?
7. What Is an External Table?
8. How To Load Data through External Tables?
9. What Are the Restrictions on External Table Columns?
10. What Is a Directory Object?
11. How To Define an External Table with a Text File?
12. How To Run Queries on External Tables?
13. How To Load Data from External Tables to Regular Tables?
14. What Is the Data Pump Export Utility?
15. What Is the Data Pump Import Utility?
16. How To Invoke the Data Pump Export Utility?
17. How To Invoke the Data Pump Import Utitlity?
18. What Are Data Pump Export and Import Modes?
19. How To Estimate Disk Space Needed for an Export Job?
20. How To Do a Full Database Export?
21. Where Is the Export Dump File Located?
22. How To Export Your Own Schema?
23. How To Export Several Tables Together?
24. What Happens If the Imported Table Already Exists?
25. How To Import One Table Back from a Dump File?
26. What Are the Original Export and Import Utilities?
27. How To Invoke the Original Export Import Utilities?

Sample scripts used in this FAQ assumes that you are connected to the server with the HR user account on the default database instance XE. See other FAQ collections on how to connect to the server.
Some sample scripts may require database tables created by other samples in the beginning of the collection.
What Is the Simplest Tool to Run Commands on Oracle Servers?
The simplest tool to connect to an Oracle server and run commands to manage data is SQL*Plus. It is an Oracle database client tool that works as a command-line user interface to the database server. SQL*Plus allows you:

• Format, perform calculations on, store, and print from query results.
• Examine table and object definitions.
• Develop and run batch scripts.
• Perform database administration.

What Is the Quickest Way to Export a Table to a Flat File?
The quickest way to export a table to a flat file is probably to use the SQL*Plus SPOOL command. It allows you to record SELECT query result to a text file on the operating system. The following tutorial exercise shows you how control the output format, start the spooler, and dump all record from a table to a flat text file:
>mkdir \oraclexe\test
>sqlplus /nolog

SQL> connect HR/fyicenter

SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\employees.txt;
SQL> SELECT * FROM EMPLOYEES;
......
SQL> SPOOL OFF;
You should get all records in employees.txt with fixed length fields.
How To Export Data with a Field Delimiter?
The previous exercise allows you to export data with fixed field lengths. If you want export data with variable field lengths and field delimiters, you can concatenate your fields with an expression in the SELECT clause as shown in the tutorial exercise bellow:
SQL> SET HEADING OFF;
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 1000;
SQL> SPOOL \oraclexe\test\fyi_links.txt;
SQL> SELECT id ||','|| url ||','|| notes ||','|| counts
||','|| created FROM fyi_links;
......
SQL> SPOOL OFF;
You should see all records in fyi_links.txt with ',' delimited fields as shown here:
101,fyicenter.com,Session 1,,17-MAY-06
110,centerfyi.com,Session 1,,17-MAY-06
What Is SQL*Loader?
SQL*Loader is a database tool that allows to load data from external files into database tables. SQL*Loader is available as part of the free Oracle 10g Expression Edition. It has some interesting features as:

• Can load data from multiple data files into multiple tables in one load session.
• Can specify character set of the data.
• Can generate sophisticated error reports.
• Can load data directly to Oracle datafiles, bypassing normal record inserting process.

What Is a SQL*Loader Control File?
A SQL*Loader control file a text that defines how data files should be loaded into the database. It allows you to specify:

• Where is the input data file.
• The format of the input date file.
• The target table where the data should be loaded.
• How input data fields should be mapped to target table columns.
• Select criteria to select input records for loading.
• Where to output errors.