Logical Database Backups Using Export and Import Utilities:

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 :)

Regards,
Chowdari

Useful Traditional IMP Commands

Here you can find some useful Import/IMP commands:

1) Command to imp full database:

[chow@hyddb exp_bkp]$ imp system/manager file=full.dmp log=full_imp.log full=y 

2) Command to imp all scotts objects into scott user

[chow@hyddb exp_bkp]$ imp system/manager file=scott.dmp log=scott_imp.log fromuser=scott touser=scott 

3) Command to imp scotts emp table:

[chow@hyddb exp_bkp]$ imp system/manager file=emp.dmp log=emp_imp.log fromuser=scott touser=scott tables=emp 

4) Command to imp scotts multiple tables

[chow@hyddb exp_bkp]$ imp system/manager file=full.dmp log=full_scott_imp.log fromuser=scott touser=scott tables=(emp,dept,salgrade) 

Thats it. Hope this article will help you :)

Regards,
Chowdari

Useful Traditional EXP Commands

Here you can find some useful Export/EXP commands:

1) Command to perform full database export:

[chow@hyddb exp_bkp]$ exp system/manager file=full.dmp log=full.log full=y 

2) Command to export scott user:

[chow@hyddb exp_bkp]$ exp scott/tiger file=scott.dmp log=scott.log

3) Command to export scott user by system/manager:

[chow@hyddb exp_bkp]$ exp system/manager file=scott_exp_by_system.dmp log=scott_exp_by_system.log owner=scott 

4) Command to export multiple users by system/manager:

[chow@hyddb exp_bkp]$ exp system/manager file=user_exp_by_system.dmp log=user_exp_by_system.log owner=(scott,demo)

5) Command to export single table from scott user:

[chow@hyddb exp_bkp]$ exp scott/tiger file=emp.dmp log=emp.log tables=emp

6) Command to export multiple tables:

[chow@hyddb exp_bkp]$ exp scott/tiger file=emp_dept.dmp log=emp_dept.log tables=(emp,dept) 

7) Command to exp multiple tables from system/manager:

[chow@hyddb exp_bkp]$ exp system/manager file=emp_dept_system.dmp log=emp_dept_system.log tables=(scott.emp,scott.dept)

8) Command to export multiple tables from multiple users:

[chow@hyddb exp_bkp]$ exp system/manager file=multi_user_tab_system.dmp log=multi_user_tab_system.log tables=(scott.emp,scott.dept,demo.emp,demo.dept)

9) Command to exp scott user without rows:

[chow@hyddb exp_bkp]$ exp system/manager file=scott_empty.dmp log=scott_empty.log owner=scott rows=n

10) Command to exp scott user without row's constraints,grants,triggers and indexes:

[chow@hyddb exp_bkp]$ exp system/manager file=scott_empty_cons.dmp log=scott_empty_cons.log owner=scott constraints=n rows=n grants=n indexes=n triggers=n

11) Following command to export using compress=y option:

[chow@hyddb exp_bkp]$ exp scott/tiger tables=(emp) file=emp_compress.dmp log=emp_compress.log compress=y

compress=y => when we export a table with compress=y then, the table, when ,imported back into the database will be brought into one single large extent, this will increase the i/o performance on the table.

12) Command to export a single tablespace with all its contents:

[chow@hyddb exp_bkp]$ exp system/manager tablespaces=(users) file=users_ts.dmp log=users_ts.log

13) Command to export multiple tablespaces:

[chow@hyddb exp_bkp]$ exp system/manager tablespaces=(users,userdata) file=users_userdata_ts.dmp log=users_userdata_ts.log 

14) Commands to specify a query:

[chow@hyddb exp_bkp]$ exp scott/tiger tables=emp query=\"where deptno=10\" file=query.dmp log=query.log
[chow@hyddb exp_bkp]$ exp scott/tiger file=emp_query.dmp query='"WHERE deptno = 10 AND sal > 1000"' tables=emp 

Thats it. Hope this article will help you :)

Regards,
Chowdari