expdp/impdp导入导出数据

Expdp导出数据

1：查看现有的逻辑备份目录

SQL> col DIRECTORY_PATH for a50

SQL> set linesize 300

SQL> select * from dba_directories;

 

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

------------------------------ ------------------------------ --------------------------------------------------

SYS                            DUMP_DIR                       /home/dump

SYS                            XMLDIR                         /u01/oracle/11g/rdbms/xml

SYS                            ORACLE_OCM_CONFIG_DIR          /u01/oracle/11g/ccr/hosts/dg-master/state

SYS                            DATA_PUMP_DIR                  /u01/oracle/admin/orcl/dpdump/

SYS                            ORACLE_OCM_CONFIG_DIR2         /u01/oracle/11g/ccr/state

 

2：自定导出的物理目录

[root@mail ~]# cd /home

[root@mail home]# mkdir backup

[root@mail home]# ls -l

total 72

drwxr-xr-x.  2 oracle  oinstall  4096 May  9 16:28 archivelog

drwxr-xr-x.  2 root    root      4096 Sep 21 11:48 backup

-rw-r--r--.  1 oracle  oinstall  1572 Jun 28 17:50 crs.txt

[root@mail home]# chown oracle:oinstall backup

 

[root@mail home]# ls -l

total 72

drwxr-xr-x.  2 oracle  oinstall  4096 May  9 16:28 archivelog

drwxr-xr-x.  2 oracle  oinstall  4096 Sep 21 11:48 backup

-rw-r--r--.  1 oracle  oinstall  1572 Jun 28 17:50 crs.txt

 

3：创建oracle导出的逻辑目录并给导出用户服务读写权限

SQL> create directory backup as '/home/backup';

 

Directory created.

 

SQL> grant write,read on directory backup to test;

 

Grant succeeded.

SQL> set linesize 300

SQL> col DIRECTORY_PATH for a50

SQL>select * from dba_directories;

 

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

------------------------------ ------------------------------ --------------------------------------------------

SYS                            BACKUP                         /home/backup

SYS                            DUMP_DIR                       /home/dump

SYS                            XMLDIR                         /u01/oracle/11g/rdbms/xml

SYS                            ORACLE_OCM_CONFIG_DIR          /u01/oracle/11g/ccr/hosts/dg-master/state

SYS                            DATA_PUMP_DIR                  /u01/oracle/admin/orcl/dpdump/

SYS                            ORACLE_OCM_CONFIG_DIR2         /u01/oracle/11g/ccr/state

 

4：用expdp导出test这个schema的数据

[oracle@mail ~]$ expdp test/test schemas=test directory=BACKUP dumpfile=test.dmp logfile=test.log

******************************************************************************

Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:

  /home/backup/test.dmp

Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Sep 21 11:59:08 2017 elapsed 0 00:01:00

查看备份文件

[root@mail backup]# ls

test.dmp  test.log

 

Impdp导入数据

1：创建导入目录和导入的目标用户

[root@mail home]# mkdir impdpdir

[root@mail impdpdir]# pwd

/home/impdpdir

[root@mail impdpdir]# chown oracle:oinstall /home/impdpdir

SQL> create directory imp_dir as '/home/impdpdir';

 

Directory created.

 

SQL> create user test2 identified by test2;

 

User created.

 

SQL> grant dba to test2;

 

Grant succeeded.

SQL> grant write,read on directory imp_dir to test2;

 

Grant succeeded.

 

2：查询源数据库test用户的表空间

SQL>  select tablespace_name from user_tablespaces;

 

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

TBS_ODS

 

6 rows selected.

 

4：计算表空间的使用大小（导入的库建立对应的表空间或者给remap的表空间添加对应大小的数据文件）

select b.file_id 文件ID号,

       b.tablespace_name 表空间名,

       b.bytes / 1024 / 1024 || 'M' 字节数,

       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 || 'M' 已使用,

       sum(nvl(a.bytes, 0)) / 1024 / 1024 || 'M' 剩余空间,

       100 - sum(nvl(a.bytes, 0)) / (b.bytes) * 100 占用百分比

  from dba_free_space a, dba_data_files b

 where a.file_id = b.file_id

 group by b.tablespace_name, b.file_id, b.bytes

 order by b.file_id;

 

 

3：imp导入数据（我这里使用remap_tablespace参数把所有的表重指定到users，因为一个users数据文件最大扩展到32G。所以在這没有给users添加数据文件）

[oracle@mail backup]$ cd /home/impdpdir/

[oracle@mail impdpdir]$ ls

test.dmp

[oracle@mail impdpdir]$ impdp test2/test2 remap_schema=test:test2 dumpfile=test.dmp directory=imp_dir logfile=imp.log

报错

ORA-39083: Object type JOB failed to create with error:

ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

 

Job导入失败（这是因为我测试本地导入本地另外一个用户）

 

跳过job导入

SQL> drop user test2 cascade;

 

User dropped.

 

SQL> create user test2 identified by test;

 

User created.

 

SQL>  grant write,read on directory imp_dir to test2;

 

Grant succeeded.

 

[oracle@mail impdpdir]$ impdp test2/test2 remap_schema=test:test2 dumpfile=test.dmp directory=imp_dir logfile=imp.log table_exists_action=replace exclude=job

 

ORA-31684: Object type USER:"TEST2" already exists(此问题可以忽略，因为我们在导入之前已经创建了test2用户。)

 

导入导出完毕。Expdp/impdp较之imp/exp的方式更为灵活，只是需要手工建数据文件或者表空间

 

重定向表空间为Remap_tablespaces=tbs1:users,tbs2:users,tbs3:users .....