ORACLE逻辑备份

导入导出模式

　　　　

　　i. 完全模式

exp 全库导出
[oracle@VM-4-17-centos ~]$ exp 'para_user/un1ware123' buffer=64000 file=/home/oracle/full.dmp full=y log=exp1.log

  删除账户数据

SQL> ALTER USER para_user ACCOUNT LOCK;

SQL> SELECT sid, serial# FROM v$session WHERE username = 'PARA_USER';

SQL> alter system kill session '145,46458';

SQL> DROP USER para_user CASCADE;

 SQL> SELECT * FROM dba_users WHERE username = 'PARA_USER';

　　

imp全库导入
[oracle@VM-4-17-centos exp]$ imp \'sys/change_on_install as sysdba \' buffer=64000 file=/home/oracle/exp/full.dmp log=exp1.log full=y ignore=y

　　ii. 用户模式

exp用户导出
[oracle@VM-4-17-centos exp]$ exp para_user/un1ware123 file=/home/oracle/para_user_data.dmp owner=para_user

imp 用户导入
[oracle@VM-4-17-centos ~]$ imp \'sys/change_on_install as sysdba \' file=/home/oracle/para_user_data.dmp log=exp2.log fromuser=para_user touser=para_user full=n ignore=y

　　iii表模式

[oracle@VM-4-17-centos exp]$ exp \'para_user/un1ware123 \' tables = EMPLOYEES query =\'where id = 1\' file=/home/oracle/exp/EMPLOYEESID.dmp log=exp3.log

 

数据泵导出数据 （推荐 -常用）

dpdum目录创建于更改（使用默认的也行）

SQL> Select * from dba_directories;

SQL> create directory dpdump_dir as '/home/oracle/exp';

SQL> SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DPDUMP_DIR';

SQL> grant read,write on directory dpdump_dir to para_user;   



全库导出

[oracle@VM-4-17-centos ~]$ expdp para_user/un1ware123 directory=dpdump_dir dumpfile=expdp_full.dmp full=y logfile=full.log

单个schemas导出

[oracle@VM-4-17-centos ~]$ expdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=para_user2.dmp schemas=para_user logfile=user1.log

多个schemas导出

[oracle@VM-4-17-centos ~]$ expdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=para_user.dmp schemas=(user1,user2,user3) logfile=user1.log



按表导出 ---单表

[oracle@VM-4-17-centos ~]$ expdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=table_B.dmp tables=para_user.EMPLOYEES logfile=EMPLOYEES.log

按表导出 ---多表

[oracle@VM-4-17-centos ~]$  expdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=table_3.dmp tables=para_user.EMPLOYEES,para_user.EMPLOYEES2 logfile=EMPLOYEES.log

表空间导出

expdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=tablespace_A.dmp logfile=tablespace_A.log tablespaces=TEST



全库导入

[oracle@VM-4-17-centos ~]$ impdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=full.dmp logfile=full.log full=y

单个scheams 导入

[oracle@VM-4-17-centos ~]$ impdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=para_user2.dmp schemas=para_user logfile=user1.log

多个scheams 导入

[oracle@VM-4-17-centos ~]$ impdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=para_user2.dmp schemas=(para_user,para_user2) logfile=user1.log

单表导入

[oracle@VM-4-17-centos ~]$ impdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=table_B.dmp tables=para_user.EMPLOYEES logfile=EMPLOYEES.log

多表导入

[oracle@VM-4-17-centos ~]$ impdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=table_B.dmp tables=para_user.EMPLOYEES,para_user.EMPLOYEES2 logfile=EMPLOYEES.log

表空间导入

[oracle@VM-4-17-centos ~]$ impdp \'para_user/un1ware123 \' directory=dpdump_dir dumpfile=tablespace_A.dmp logfile=tablespace_A.log tablespaces=TEST