Export / Import
Let's consider some of the export/import examples for this month's tip.
What is Export & Import (excerpts from Oracle Server Utilities Guide)?
Export provides a simple way for you to transfer data objects between Oracle database. Export extracts the object definitions and table data from an Oracle database and stores them in an Oracle binary-format. Export dump file located typically on disk or tape.
Such files can then be FTPed or physically transported (in the case of tape) to a different site and used, with the Import utility, to transfer data between databases that are on machines not connected via a network or as backups in addition to normal backup procedures.
Export dump files can only be read by the Oracle utility, Import. Export files are stored in Oracle-binary format. Export files generated by Oracle8 Export cannot be read by utilities other than Oracle8 Import. Export files created by Oracle8 Export cannot be read by earlier versions of the Import utility.
exp help=y will provide this help screen (Oracle7).
Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL export entire file (N) BUFFER size of data buffer OWNER list of owner usernames FILE output file (EXPDAT.DMP) TABLES list of table names COMPRESS import into one extent (Y) RECORDLENGTH length of IO record GRANTS export grants (Y) INCTYPE incremental export type INDEXES export indexes (Y) RECORD track incr. export (Y) ROWS export data rows (Y) PARFILE parameter filename CONSTRAINTS export constraints (Y) CONSISTENT cross-table consistency LOG log file of screen output STATISTICS analyze objects (ESTIMATE) DIRECT direct path (N) FEEDBACK display progress every x rows (0) POINT_IN_TIME_RECOVER Tablespace Point-in-time Recovery (N) RECOVERY_TABLESPACES List of tablespace names to recover VOLSIZE number of bytes to write to each tape volume Similarly imp help=y will provide this help screen Keyword Description (Default) Keyword Description (Default) -------------------------------------------------------------------------- USERID username/password FULL import entire file (N) BUFFER size of data buffer FROMUSER list of owner usernames FILE input file (EXPDAT.DMP) TOUSER list of usernames SHOW just list file contents (N) TABLES list of table names IGNORE ignore create errors (N) RECORDLENGTH length of IO record GRANTS import grants (Y) INCTYPE incremental import type INDEXES import indexes (Y) COMMIT commit array insert (N) ROWS import data rows (Y) PARFILE parameter filename LOG log file of screen output DESTROY overwrite tablespace data file (N) INDEXFILE write table/index info to specified file CHARSET character set of export file (NLS_LANG) POINT_IN_TIME_RECOVER Tablespace Point-in-time Recovery (N) SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N) ANALYZE execute ANALYZE statements in dump file (Y) FEEDBACK display progress every x rows(0) VOLSIZE number of bytes in file on each volume of a file on tape
For detailed explanation of each parameter, refer to Oracle Server Utilities Guide.
The following case studies will help you understand how export / import can be used by DBAs to have their life easier.
CASE I: Export to backup data every day for small and medium databases
It is always good to have an export backup of data taken every night along with your other backups (cold / hot backups). From export backups, it is easier to restore a table or a couple of tables to the point when the export was taken. Also export helps to check data block corruption. Unix provides a utility called 'compress' to compress the size of the export dump file. If you are short on disk space, you might consider writing the export dump file compressed using a UNIX pipe. The following scripts will show how to accomplish this.
Script to export data to a compressed file:
Script to import data from a compressed export file:
where import.par and export.par have the parameters to be considered for the import and export respectively. For example, if you are doing full exports, the export.par file could be
To import one table from the above export file, the import.par file could be
CASE II: Export / Import to reduce fragmentation of table
Though experts say, it does not affect the performance to have many extents for a table or index, it still is better to reorganize tables and indexes if they have too many extents. For reorganizing the index, you can use the command "ALTER INDEX ... REBUILD TABLESPACE ... STORAGE (...);" To reorganize the table, you can export the table data, drop the table, recreate the table with new storage parameters and import data. Well, this is always not very easy to drop and recreate the table, if the table has lot of child tables (foreign key relationship to this table). To drop the parent table, you need to drop all the foreign key constraints first. So you need to save the script for re-creating all constraints. Also when you drop a table, all the privileges on that table to different roles/users also lost. I prefer not to drop the table, but to truncate the table after disabling the foreign keys and triggers on the table. So you have an INITIAL extent which you cannot make it bigger, but you can alter the table to have a larger NEXT extent value and do the import. The following script will disable all foreign keys from the table.
set feedback off verify off echo off lines 200 trims on pages 0
To disable the triggers, do
ALTER TABLE tablename DISABLE ALL TRIGGERS;
set pages 0 feedback off echo off lines 132
Case III: Import to recover data
If your users have messed up some data, or deleted some data accidentally, you can recover that from an export file, if you have one. To do so, you can truncate the table, disable foreign keys (child tables), disable triggers and import the data. The above scripts can be used here also. The import parameter file could be (here we import just the data, no indexes, grants...)
Case IV: Export / Import to move data between databases
You can also move data between one database to another or one machine to another using export and import. The export/import can be database level, user level or table level. To speed up exports use DIRECT=Y. To import data from an export file taken using DIRECT=Y, the database character set should be the same.
In Oracle, there is neat feature in EXP/IMP to show the progress of export/import - FEEDBACK=nn; where nn is the number of rows. A dot (.) will be displayed on the screen for each nn rows exported/imported.