问题描述： expdp 导出 dmp文件 ，但是导出操作时，dmp文件命名有误。 mv将导出后的dmp文件重命名。这样对 impdp 操作会有什么影响呢？
结论： 只要命令敲对，文件名写正确，impdp操作不受影响
一、模拟开始
scott用户下的表
点击(此处)折叠或打开
-
SQL> conn scott/tiger
- Connected.
- SQL>
- SQL> select count(*) from tab;
- COUNT(*)
- ———-
- 4
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- —————————— ——- ———-
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- SALGRADE TABLE
新建用户liuyaya下的表
点击(此处)折叠或打开
-
SQL> conn / as sysdba
- Connected.
- SQL>
- SQL>
- SQL> create user liuyaya identified by oracle account unlock;
- User created.
- SQL> SQL>
- SQL>
- SQL>
- SQL> grant connect,resource to liuyaya;
- Grant succeeded.
- SQL>
- SQL> conn liuyaya/oracle
- Connected.
- SQL> select count(1) from tab;
- COUNT(1)
- ———-
- 0
数据泵导出scott用户下的表
点击(此处)折叠或打开
-
SQL> conn sys/oracle as sysdba
- Connected.
- SQL> create or replace directory expdp_dir_scott01 as ‘/soft’;
- Directory created.
- SQL> grant read,write on directory expdp_dir_scott01 to scott;
- Grant succeeded.
- SQL> exit
点击(此处)折叠或打开
-
[oracle@redhat6 ~]$ expdp scott/tiger directory=expdp_dir_scott01 schemas=scott datafile=scott01.dmp logfile=scott01.log parallel=2;
- LRM–00101: unknown parameter name ‘datafile’
- [oracle@redhat6 ~]$ expdp scott/tiger directory=expdp_dir_scott01 schemas=scott dumpfile=scott01.dmp logfile=scott01.log parallel=2;
- Export: Release 11.2.0.4.0 – Production on Thu Jun 21 19:15:52 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 “SCOTT”.“SYS_EXPORT_SCHEMA_01”: scott/******** directory=expdp_dir_scott01 schemas=scott dumpfile=scott01.dmp logfile=scott01.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
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- . . 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/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/COMMENT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Master table “SCOTT”.“SYS_EXPORT_SCHEMA_01” successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
- /soft/scott01.dmp
- Job “SCOTT”.“SYS_EXPORT_SCHEMA_01” successfully completed at Thu Jun 21 19:16:13 2018 elapsed 0 00:00:20
修改dumpfile文件名
点击(此处)折叠或打开
-
[oracle@redhat6 ~]$ cd /soft
- [oracle@redhat6 soft]$ ls
- database scott01.dmp scott01.log zjgd_back
- [oracle@redhat6 soft]$ mv scott01.dmp liuyaya.dmp
- [oracle@redhat6 soft]$ ls
- database liuyaya.dmp scott01.log zjgd_back
导入到新建用户liuyaya
点击(此处)折叠或打开
-
[oracle@redhat6 soft]$ impdp \‘/ as sysdba\’ directory=expdp_dir_scott01 dumpfile=liuyaya.dmp remap_schema=scott:liuyaya logfile=liuyaya.log parallel=2
- Import: Release 11.2.0.4.0 – Production on Thu Jun 21 20:18:01 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
- Master table “SYS”.“SYS_IMPORT_FULL_01” successfully loaded/unloaded
- Starting “SYS”.“SYS_IMPORT_FULL_01”: “/******** AS SYSDBA” directory=expdp_dir_scott01 dumpfile=liuyaya.dmp remap_schema=scott:liuyaya logfile=liuyaya.log parallel=2
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- . . imported “LIUYAYA”.“DEPT” 5.929 KB 4 rows
- . . imported “LIUYAYA”.“EMP” 8.562 KB 14 rows
- . . imported “LIUYAYA”.“SALGRADE” 5.859 KB 5 rows
- . . imported “LIUYAYA”.“BONUS” 0 KB 0 rows
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- Job “SYS”.“SYS_IMPORT_FULL_01” successfully completed at Thu Jun 21 20:18:05 2018 elapsed 0 00:00:02
- [oracle@redhat6 soft]$ sqlplus liuyaya/oracle
- SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 21 20:18:18 2018
- Copyright (c) 1982, 2013, Oracle. 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
- SQL> select * from tab;
- TNAME TABTYPE CLUSTERID
- –––––––––––––––––––––––––––––– ––––––– ––––––––––
- BONUS TABLE
- DEPT TABLE
- EMP TABLE
- SALGRADE TABLE
二、 总结
将改了名字的dmp文件顺利导入到其他schema下，证明
数据库导出文件可以改名字，对导入操作无影响
