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:

 

 

 

创建用户

[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 ~]$ 

 

导入完毕

 

oracle11g DMP文件导入记录

标签：socket   0 rows   clust   status   ott   dff   relative   ogr   import