Saturday, March 4, 2017

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

No comments:

Post a Comment

Some Most Popular Articles