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/
[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>
执行导入，更改命令如下：
impdp system/oracle dumpfile=HMDCTEST20190711.DMP directory=DATA_PUMP_DIR REMAP_TABLESPACE=HMDCTEST:T_DATA
[oracle@rt1 ~]$ impdp system/oracle dumpfile=HMDCTEST20190711.DMP directory=DATA_PUMP_DIR REMAP_TABLESPACE=HMDCTEST:T_DATA Import: Release 11.2.0.4.0 - Production on Wed Aug 26 04:33:00 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 REMAP_TABLESPACE=HMDCTEST:T_DATA Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"HMDCTEST" already exists 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/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing 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 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/COMMENT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "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 ~]$
导入完毕
