Oracle Import/Export 1: EXP

Oracle comes with a pair of commands that are used for getting data out and then back into a database. The commands are called IMP and EXP. There are plenty of places on the net where you can find a full description of these commands and all their options, what I will do here is give you a couple of solid examples that you can use for your own purposes and will cover your most common needs.

If you wish to EXPORT data from your database then generally you want to export either *all* the data (including the structure, necessary for recreating the database on another computer) or *one* of the tables, maybe for backup or archive purposes.

To EXPORT the complete database, open a Command Shell and type the following:

EXP USERID=USERNAME/PASSWORD@DATABASENAME CONSISTENT=Y FEEDBACK=1000 FULL=Y INDEXES=Y STATISTICS=ESTIMATE ROWS=Y GRANTS=Y CONSTRAINTS=Y LOG=C:\EXPORT.LOG BUFFER = 10485670 FILE=C:\EXPORTFILE.DMP

This creates a export dump file called EXPORTFILE.DMP and a log file called EXPORT.LOG, in their designated directory locations. Using the EXPORTFILE.DMP you can recreate the database in full on an entirely separate machine. Useful for creating offsite backup databases or updating your DSS (Decision Support System) databases.

To EXPORT one database table, open a Command Shell and type the following:

EXP USERID=USERNAME/PASSWORD@DATABASENAME CONSISTENT=Y FEEDBACK=1000 TABLES=(USER.F_TABLENAME) INDEXES=Y STATISTICS=ESTIMATE ROWS=Y GRANTS=Y CONSTRAINTS=Y LOG=C:\EXPORT.LOG BUFFER = 10485670 FILE=C:\EXPORTSINGLEFILE.DMP

This creates a export dump file called EXPORTSINGLEFILE.DMP and a log file called EXPORT.LOG, in their designated directory locations. Using the EXPORTSINGLEFILE.DMP you can archive a database table in full, or transport it to an entirely separate machine. Useful for backing up a table before performing significant changes.

“Well, Bart, your Great Uncle Horace used to have a saying: ‘Shoot ’em all, and let God sort them out.’ Unfortunately, one day put his theory into practice. It took 75 federal marshalls to bring him down. Now let’s never speak of him again.” - Marge Simpson