03.01 Oracle数据泵导出导出(expdp/impdp)命令举例(上)
Oracle数据泵导出导出(expdp/impdp)命令举例(上)
##实验目的： 使用数据泵迁移数据 ##实验环境： Oracle 11.2.0.4，利用数据库自带的scott示例用户进行试验测试
实验步骤概述：
1.首先需要创建Directory 2.使用expdp导出用户数据 2.1 只导出scott用户的元数据，且不包含统计信息； 2.2 只导出scott用户的数据； 2.3 只导出scott用户下的emp，dept表及数据； 2.4 只导出scott用户下的emp，dept表结构； 2.5 导出scott用户下所有的内容； 2.6 并行导出scott用户下所有的内容； 3.查询当前用户用到的表空间 ##特别注意： 如果后续要导入的数据库版本低，所有导出命令就需要在后面加一个version=指定版本。 例如11g -> 10g，假设10g具体版本为10.2.0.1，那么就 加一个版本的参数version=10.2.0.1。
实验步骤详述：
1. 首先需要创建Directory
这里目录名字定义为"eric"， 若是windows平台，假设对应系统目录为"E:\eric"; create or replace directory eric as 'E:\eric'; 若是Unix/Linux平台，假设对应系统目录为"/tmp/eric". create or replace directory eric as '/tmp/eric'; ##注意： 目录在系统上需要真实存在（mkdir -p /tmp/eric），且有访问的权限。 drwxr-xr-x 2 oracle oinstall 4096 Oct 7 20:04 eric
2. 使用expdp导出用户数据
2.1 只导出scott用户的元数据，且不包含统计信息；
expdp \'/ as sysdba\' directory=eric schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log [oracle@OraLinux6 ~]$ expdp \'/ as sysdba\' directory=eric schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log Export: Release 11.2.0.4.0 - Production on Sun Oct 7 20:08:22 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=eric 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 "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /tmp/eric/scott_meta.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Oct 7 20:08:29 2018 elapsed 0 00:00:07
2.2 只导出scott用户的数据；
expdp \'/ as sysdba\' directory=eric schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log [oracle@OraLinux6 ~]$ expdp \'/ as sysdba\' directory=eric schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log Export: Release 11.2.0.4.0 - Production on Sun Oct 7 20:10:19 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=eric 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" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /tmp/eric/scott_data.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Oct 7 20:10:21 2018 elapsed 0 00:00:02
2.3 只导出scott用户下的emp，dept表及数据；
##方法1：使用scott用户导出： 如果用scott用户导出，需要dba用户赋予scott用户read,write目录的权限。 即：grant read, write on directory eric to scott; sys@ZTDR> grant read, write on directory eric to scott; Grant succeeded. expdp scott directory=eric tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log [oracle@OraLinux6 ~]$ expdp scott directory=eric tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Export: Release 11.2.0.4.0 - Production on Sun Oct 7 20:19:45 2018 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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=eric tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/eric/scott_emp_dept.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 7 20:19:51 2018 elapsed 0 00:00:04 ##方法2：使用system用户导出（随后补充）：
2.4 只导出scott用户下的emp，dept表结构；
expdp scott directory=eric tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log [oracle@OraLinux6 ~]$ expdp scott directory=eric tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log Export: Release 11.2.0.4.0 - Production on Sun Oct 7 20:21:26 2018 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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=eric tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/eric/scott_emp_dept_meta.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sun Oct 7 20:21:32 2018 elapsed 0 00:00:03
2.5 导出scott用户下所有的内容；
expdp \'/ as sysdba\' directory=eric schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log [oracle@OraLinux6 ~]$ expdp \'/ as sysdba\' directory=eric schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log Export: Release 11.2.0.4.0 - Production on Sun Oct 7 20:13:18 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=eric schemas=scott dumpfile=scott_all.dmp logfile=scott_all.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB 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 . . 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" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /tmp/eric/scott_all.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Oct 7 20:13:25 2018 elapsed 0 00:00:06
2.6 并行导出scott用户下所有的内容；
expdp \'/ as sysdba\' directory=eric schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 [oracle@OraLinux6 ~]$ expdp \'/ as sysdba\' directory=eric schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 Export: Release 11.2.0.4.0 - Production on Sun Oct 7 20:12:09 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 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 "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=eric schemas=scott dumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 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" 0 KB 0 rows 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 "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /tmp/eric/scott_all01.dmp /tmp/eric/scott_all02.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Oct 7 20:12:15 2018 elapsed 0 00:00:06
3. 查询当前用户用到的表空间
select tablespace_name from user_tables union select tablespace_name from user_tab_partitions union select tablespace_name from user_indexes union select tablespace_name from user_ind_partitions;
