数据泵导出文件更换名称,对导入操作无影响
问题描述: 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下,证明
数据库导出文件可以改名字,对导入操作无影响
当前文章:数据泵导出文件更换名称,对导入操作无影响
URL链接:
http://dzwzjz.com/article/ihichd.html