oracle11g DMP文件导入记录
登录数据库创建单独的数据表空间和临时表空间，操作如下：
[oracle@rt1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 26 03:55:24 2020 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> create tablespace t_data datafile '+DATA' SIZE 10g; Tablespace created. SQL> desc dba_data_files Name Null? Type ----------------------------- -------- -------------------- FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7) SQL> SQL> set lines 200 SQL> column file_name format a50 SQL> select file_name,tablespace_name ,AUTOEXTENSIBLE from dba_data_files; FILE_NAME TABLESPACE_NAME AUT -------------------------------------------------- ------------------------------ --- +DATA/rac/datafile/users.259.1049335673 USERS YES +DATA/rac/datafile/undotbs1.258.1049335673 UNDOTBS1 YES +DATA/rac/datafile/sysaux.257.1049335673 SYSAUX YES +DATA/rac/datafile/system.256.1049335673 SYSTEM YES +DATA/rac/datafile/undotbs2.264.1049335797 UNDOTBS2 YES +DATA/rac/datafile/t_data.268.1049425139 T_DATA YES 6 rows selected. SQL> SQL> alter database datafile '+DATA/rac/datafile/t_data.268.1049425139' autoextend on next 10m maxsize unlimited; Database altered. SQL> alter database default tablespace t_data; Database altered. SQL> create temporary tablespace t_temp tempfile '+DATA' size 2g； Tablespace created. SQL> alter database default temporary tablespace t_temp; Database altered. SQL> select file_name,tablespace_name,autoextensible from dba_data_files; FILE_NAME TABLESPACE_NAME AUT --------------------------------------------- ------------------------------ --- +DATA/rac/datafile/users.259.1049335673 USERS YES +DATA/rac/datafile/undotbs1.258.1049335673 UNDOTBS1 YES +DATA/rac/datafile/sysaux.257.1049335673 SYSAUX YES +DATA/rac/datafile/system.256.1049335673 SYSTEM YES +DATA/rac/datafile/undotbs2.264.1049335797 UNDOTBS2 YES +DATA/rac/datafile/t_data.268.1049425139 T_DATA YES 6 rows selected. SQL> select file_name,tablespace_name,autoextensible from dba_temp_files; FILE_NAME TABLESPACE_NAME AUT --------------------------------------------- ------------------------------ --- +DATA/rac/tempfile/temp.263.1049335751 TEMP YES +DATA/rac/tempfile/t_temp.269.1049425619 T_TEMP YES SQL> desc dba_directories Name Null? Type ----------------------------------------------------------- --------- -------------- OWNER NOT NULL VARCHAR2(30) DIRECTORY_NAME NOT NULL VARCHAR2(30) DIRECTORY_PATH VARCHAR2(4000) SQL> column DIRECTORY_PATH format a90 SQL> select * from dba_directories OWNER DIRECTORY_NAME DIRECTORY_PATH --------------- ------------------------------ ------------------------------------------------------------ SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/rt1/state SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/db_1/ccr/state SQL>
上传dmp文件到DATA_PUMP_DIR 物理路径为 /u01/app/oracle/product/11.2.0/db_1/rdbms/log/
1 2 3 4 5 | [oracle@rt1 ~]$ ll -h /u01/app/oracle/product/11.2.0/db_1/rdbms/log/total 719M-rw-r----- 1 oracle asmadmin 116 Aug 25 02:09 dp.log-rw-r--r-- 1 oracle oinstall 719M Jul 10 2019 HMDCTEST20190711.DMP[oracle@rt1 ~]$ |
由于dmp文件表空间用户未知，先执行导入，看错误信息有哪些在更改导入命令，
impdp system/oracle dumpfile=HMDCTEST20190711.DMP directory=DATA_PUMP_DIR
执行导入命令错误信息如下：
;;; Import: Release 11.2.0.4.0 - Production on Wed Aug 26 04:07:16 2020 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=HMDCTEST20190711.DMP directory=DATA_PUMP_DIR Processing object type SCHEMA_EXPORT/USER ORA-39083: Object type USER failed to create with error: ORA-00959: tablespace 'HMDCTEST' does not exist Failing sql is: CREATE USER "HMDCTEST" IDENTIFIED BY VALUES 'S:3155506F12D017E3DFA0CBC27DB08A35851074E69995E320283DFF9C317C;4
73C406BB8AF347F'
DEFAULT TABLESPACE "HMDCTEST" TEMPORA RY TABLESPACE "TEMP" Processing object type SCHEMA_EXPORT/SYSTEM_GRANT ORA-39083: Object type SYSTEM_GRANT failed to create with error: ORA-01917: user or role 'HMDCTEST' does not exist Failing sql is: GRANT UNLIMITED TABLESPACE TO "HMDCTEST" WITH ADMIN OPTION Processing object type SCHEMA_EXPORT/ROLE_GRANT ORA-39083: Object type ROLE_GRANT failed to create with error: ORA-01917: user or role 'HMDCTEST' does not exist Failing sql is: GRANT "CONNECT" TO "HMDCTEST" WITH ADMIN OPTION ORA-39083: Object type ROLE_GRANT failed to create with error: ORA-01917: user or role 'HMDCTEST' does not exist Failing sql is:
用户为HMDCTEST，表空间HMDCTEST。
创建用户
[oracle@rt1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 26 04:30:02 2020 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, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> CREATE USER HMDCTEST IDENTIFIED BY 123 DEFAULT TABLESPACE T_DATA TEMPORARY TABLESPACE T_TEMP 2 / User created. SQL> GRANT DBA TO HMDCTEST; Grant succeeded. SQL>
执行导入，更改命令如下：
1 | impdp system/oracle dumpfile=HMDCTEST20190711.DMP directory=DATA_PUMP_DIR REMAP_TABLESPACE=HMDCTEST:T_DATA |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | [oracle@rt1 ~]$ impdp system/oracle dumpfile=HMDCTEST20190711.DMP directory=DATA_PUMP_DIR REMAP_TABLESPACE=HMDCTEST:T_DATAImport: Release 11.2.0.4.0 - Production on Wed Aug 26 04:33:00 2020Copyright (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 ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=HMDCTEST20190711.DMP <br>directory=DATA_PUMP_DIR REMAP_TABLESPACE=HMDCTEST:T_DATA Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"HMDCTEST" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "HMDCTEST"."FM_SOCKET_LOG" 517.4 MB 2320757 rows. . imported "HMDCTEST"."HMFULLCARINFO" 48.75 MB 260876 rows. . imported "HMDCTEST"."HMLIGHTCARINFO" 47.30 MB 260600 rows. . imported "HMDCTEST"."HMWEIGHTNOTE" 44.48 MB 261082 rows. . imported "HMDCTEST"."HMSAMPLINGINFO" 34.27 MB 227611 rows. . imported "HMDCTEST"."HMENTERINFO" 1.512 MB 9908 rows. . imported "HMDCTEST"."HMOUTINFO" 15.54 MB 134767 rows. . imported "HMDCTEST"."HMDARIYREPORT" 1.805 MB 16191 rows. . imported "HMDCTEST"."ANALYSISREPORT" 907.9 KB 3900 rows. . imported "HMDCTEST"."HMCARRFIDINFO" 589.4 KB 4271 rows. . imported "HMDCTEST"."DAYREPORT" 526.2 KB 3072 rows. . imported "HMDCTEST"."HMBARREL" 514.2 KB 4168 rows. . imported "HMDCTEST"."HMCARINFO" 416.6 KB 1850 rows. . imported "HMDCTEST"."HMMAKESAMPLE" 365.9 KB 3907 rows. . imported "HMDCTEST"."T_FIELD" 31.50 KB 285 rows. . imported "HMDCTEST"."MONTHREPORT" 100.4 KB 614 rows. . imported "HMDCTEST"."DOCUMENTUPLOAD" 13.39 KB 7 rows. . imported "HMDCTEST"."FURNACEREPORT" 18.38 KB 4 rows. . imported "HMDCTEST"."T_ROLEPROGLNK" 19.17 KB 273 rows. . imported "HMDCTEST"."ADDBUNKER" 39.92 KB 184 rows. . imported "HMDCTEST"."ASSAYREPORT" 57.70 KB 2 rows. . imported "HMDCTEST"."COALSTOCK" 19.69 KB 17 rows. . imported "HMDCTEST"."COALYARD" 12.15 KB 5 rows. . imported "HMDCTEST"."COLOR" 9.585 KB 4 rows. . imported "HMDCTEST"."COLORITEM" 9.437 KB 20 rows. . imported "HMDCTEST"."COMMODITYCOAL" 17.60 KB 13 rows. . imported "HMDCTEST"."COUNTYYCOALPRICE" 35.74 KB 120 rows. . imported "HMDCTEST"."FARELIANG" 39.17 KB 413 rows. . imported "HMDCTEST"."HMBARRELRECORD" 11.13 KB 16 rows. . imported "HMDCTEST"."HMBUCKLEBOTTLESWATER" 9.695 KB 11 rows. . imported "HMDCTEST"."HMCARRIER" 7.171 KB 2 rows. . imported "HMDCTEST"."HMPLATFORMSCALE" 10.34 KB 4 rows. . imported "HMDCTEST"."HMSAMOPERATION" 6.578 KB 8 rows. . imported "HMDCTEST"."HMSAMPLINGMACHINE" 9.210 KB 6 rows. . imported "HMDCTEST"."LOADINGASSAY" 26.48 KB 140 rows. . imported "HMDCTEST"."MONTHPLAN" 9.578 KB 20 rows. . imported "HMDCTEST"."NUMBERRULE" 10.87 KB 4 rows. . imported "HMDCTEST"."POWERASSAY" 23.63 KB 142 rows. . imported "HMDCTEST"."QUOTARULE" 13.39 KB 1 rows. . imported "HMDCTEST"."SECPRICECOAL" 15.65 KB 26 rows. . imported "HMDCTEST"."SERIALNUM" 6.343 KB 3 rows. . imported "HMDCTEST"."SETTLECOAL" 12.24 KB 1 rows. . imported "HMDCTEST"."STOCKDETAIL" 29.08 KB 55 rows. . imported "HMDCTEST"."SUPPLIER" 25.28 KB 69 rows. . imported "HMDCTEST"."SUPPLIERCOAL" 9.304 KB 2 rows. . imported "HMDCTEST"."THIRDASSAY" 26.10 KB 121 rows. . imported "HMDCTEST"."T_DICTIONARY" 7.867 KB 14 rows. . imported "HMDCTEST"."T_DICTIONARYLIN" 18.03 KB 320 rows. . imported "HMDCTEST"."T_FUNCTION" 9.164 KB 9 rows. . imported "HMDCTEST"."T_ORGAN" 10.78 KB 14 rows. . imported "HMDCTEST"."T_PROGRAM" 13.67 KB 67 rows. . imported "HMDCTEST"."T_ROLE" 10.5 KB 13 rows. . imported "HMDCTEST"."T_ROLEAUTHLNK" 5.835 KB 1 rows. . imported "HMDCTEST"."T_ROLEUSERLNK" 10.44 KB 24 rows. . imported "HMDCTEST"."T_STATEAUTH" 8.437 KB 4 rows. . imported "HMDCTEST"."T_STATEFIELDLNK" 8.890 KB 36 rows. . imported "HMDCTEST"."T_STATEFUNCLNK" 8.218 KB 8 rows. . imported "HMDCTEST"."T_SYSMENU" 12.59 KB 72 rows. . imported "HMDCTEST"."T_TABLE" 9.539 KB 29 rows. . imported "HMDCTEST"."T_USER" 13.32 KB 29 rows. . imported "HMDCTEST"."UNLOADMANAGE" 21.72 KB 20 rows. . imported "HMDCTEST"."COALPARTITON" 0 KB 0 rows. . imported "HMDCTEST"."HMDRIVER" 0 KB 0 rows. . imported "HMDCTEST"."HM_SOCKET_IN" 0 KB 0 rows. . imported "HMDCTEST"."HM_SOCKET_OUT" 0 KB 0 rows. . imported "HMDCTEST"."HUI" 0 KB 0 rows. . imported "HMDCTEST"."LIU" 0 KB 0 rows. . imported "HMDCTEST"."MONTHBLENDPLAN" 0 KB 0 rows. . imported "HMDCTEST"."ONROADMANAGER" 0 KB 0 rows. . imported "HMDCTEST"."RLXXHZ" 0 KB 0 rows. . imported "HMDCTEST"."SHUI" 0 KB 0 rows. . imported "HMDCTEST"."T_JSONTABLE" 0 KB 0 rows. . imported "HMDCTEST"."T_MODULE" 0 KB 0 rows. . imported "HMDCTEST"."T_ROLEFUNCLNK" 0 KB 0 rows. . imported "HMDCTEST"."T_SYSOPTION" 0 KB 0 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Wed Aug 26 04:33:58 2020 elapsed 0 00:00:57[oracle@rt1 ~]$ |
导入完毕
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· 什么是nginx的强缓存和协商缓存
· 一文读懂知识蒸馏
· Manus爆火，是硬核还是营销？