oracle数据库--------------数据泵

一、使用数据泵技术导入导出

1.1 数据导入

mdkir /orc/app/bak

SQL> conn sys/abc123 as sysdba
SQL> create directory dump_dir as ‘/orc/app/bak’;

SQL> create user c##scott
identified by scott123
default tablespace users
temporary tablespace temp
quota unlimited on users;
//创建用户

SQL> grant connect,resource,dba to c##scott;
//授权成功

用c##scott账户登录：
SQL> conn
c##scott

SQL> @/abc/sql/test.sql
//导入

SQL> select table_name from user_tables;
SQL> select * from emp;
SQL> desc emp;
SQL> quit

1.2 表导出备份

1.2.1 目录读写授权

sqlplus / as sysdba
SQL> grant read,write on directory dump_dir to c##scott;

1.2.2 查看当前用户的表空间

SQL> select table_name from user_tables;

1.2.3 创建测试用的用户

SQL> create user c##jack identified by abc123;

1.2.4 用户授权

SQL> grant connect,resource to c##jack;

1.2.5 目录、配额授权

SQL> grant read,write on directory dump_dir to c##jack;
SQL> grant unlimited tablespace to c##jack;

1.2.6 导出

SQL> quit
oracle用户：
expdp c##scott/abc123 directory=dump_dir dumpfile=scotttab.dmp tables=emp,dept
ls /orc/app/bak/
//生成导出日志和备份文件

1.3 模拟故障恢复

sqlplus
c##scott
SQL> drop table emp;
SQL> select table_name from user_tables;
SQL> quit

1.4 恢复数据

impdp c##scott/abc123 directory=dump_dir dumpfile=scotttab.dmp tables=emp
sqlplus
c##scott
SQL> select table_name from user_tables;
//发现emp恢复成功

1.5 数据迁移

1.5.1 将scott用户导出的备份表导入到jack用户下

impdp system/abc123 directory=dump_dir dumpfile=scotttab.dmp tables=c##scott.emp
remap_schema=c##scott:c##jack

sqlplus
c##jack
SQL> select table_name from user_tables;
//导入成功

1.5.2 导出c##scott用户模式

expdp c##scott/abc123 directory=dump_dir dumpfile=scottschema.dmp schemas=c##scott

1.5.3 导入c##scott用户模式

impdp c##scott/abc123 directory=dump_dir dumpfile=scottschema.dmp schemas=c##scott

1.5.4 scott中所有对象导入c##jack中

impdp c##scott/abc123 directory=dump_dir dumpfile=scottschema.dmp schemas=c##scott
remap_schema=c##scott:c##jack

1.5.5 导出users表空间

expdp system/abc123 directory=dump_dir dumpfile=tablespaceusers.dmp tablespace=users
remap_schema

1.5.6 导入users表空间

impdp system/abc123 directory=dump_dir dumpfile=tablespaceusers.dmp tablespace=users
remap_schema

1.5.7 导出整个数据库

expdp system/abc123 directory=dump_dir dumpfile=full.dmp full=y

1.5.8 导入整个数据库

impdp system/abc123 directory=dump_dir dumpfile=full.dmp full=y