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