Here in this article describes how to use the original export and import utilities, invoked with the exp and imp command, respectively.
What happens when we export data from a database?
When we export data from an oracle database then the export utility will simply prepare a set of sql select statements and execute them against the target database. the data that is returned by the select operation (definition and rows of the objects) will be written to a file known as the export dump(.dmp) file.
This .dmp file is platform independent and can be carried to any other oracle site. when required the DBA can import the objects from the .dmp file into any oracle database. Import operation reads the definitions from the dump file and creates the objects there after it executes insert statements from the dump file against the database and loads the data back into the database objects that were created from the dump file.
Advantages of oracle export and import:-
1. Using oracle export/import we can backup/restore a particular object (table). export is used to backup and import is used to restore a database object.
2. Exports can be used to copy objects from one oracle schema to another.
3. When performing migration of an oracle database from one o/s to another like from windows to linux then logical backups are the only way to migrate the data as export dump file is platform independent.
4. When we want to migrate database objects, from one database, to another database, when both source and target databases, are running on oracle then we use export and import.
5. Export can also help in performing a database re-org. when after using a database for a while, it gets fragmented and also certain segments may have too many extents which results in poor i/o performance. For this reason every 3-4 months of database usage we go for a full database re-org , which includes
1. Full DB exp
2. Drop the database
3. Create a brand new database
4. Full DB imp
At the end of the import all the segments will be back within single huge extents and we will get rid of all fragmentation.
6. Exports can be done at table level, schema level, tablespace level or full database.
7. While exporting a table we can mention the query option from 8i onwards, example export the emp table where the deptno=10.
8. If the database is too large the dump file can run into gb and this much space may not be available in a single file system, thus we can export the data to multiple files spanning across multiple file systems from 8i onwards.
9. The size of an export dump file is usually 6 to 10 times smaller than the size of the database. the reasons are
1. Control,redolog are not exported
2. Exp does not carry any base tables (objects owned by sys).
3. The temporary segments are ignored.
4. Undo and rollback segments are ignored as we are interested only in commited data.
5. Only table data goes into the dump file along with the table definions.
10. Time taken for exp vs imp is usually 1:6 ratio as export is only a select operation where as import is a ddl+dml operation and each line goes through the undo or rollback segments and also goes to logfiles.
11. Import operation will cause a lot of redo activity and hence log switching will occur.
Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter i.e. "exp help=yes" or "imp help=yes".
Following two articles for export and import commands.
> Useful Traditional EXP Commands
> Useful Traditional IMP Commands
Thats it. Hope this article will help you :)