实验环境:
1、导出环境:RedHat6.4+Oracle 11.2.0.4.0,利用数据库自带的scott示例用户进行试验测试。
Directory:wjqà/tmp/seiang_wjq
2、导入环境:CentOS7.1+Oracle 12.2.0.1.0 Oracle12c默认没有scott用户
Directory:imp_wjqà/tmp/imp_comsys
一、导出数据:
特别注意:如果后续要导入的数据库版本低,所有导出命令就需要在后面加一个version=指定版本。例如11g -> 10g,假设10g具体版本为10.2.0.1,那么就加一个版本的参数version=10.2.0.1。
1. 首先需要创建Directory
注意:目录在系统上需要真实存在(mkdir /tmp/seiang_wjq),且有访问的权限。
2. 使用expdp导出用户数据
2.1 只导出scott用户的元数据,且不包含统计信息;
[oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott content=metadata_onlyexclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log
Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:17:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=wjq schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/tmp/seiang_wjq/scott_meta.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 24 14:17:48 2017 elapsed 0 00:00:21
2.2 只导出scott用户的数据;
[oracle@seiangwjq ~]$expdp system directory=wjq schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log
Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:22:36 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=wjq schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS"