白天的时候，做了一个获取dmp文件的schema实验，特此记录一下。
参考文章：如何获取dmp文件的schema -- by 我的烟灰缸
http://oradb.cc/2017/07/10/%E5%A6%82%E4%BD%95%E8%8E%B7%E5%8F%96dmp%E6%96%87%E4%BB%B6%E7%9A%84schema/
示例：
第一步：导出hr用户下的数据信息
[oracle@localhost ~]$ expdp hr/12 directory=DATA_PUMP_DIR dumpfile=1.dmp logfile=1.log Export: Release 11.2.0.4.0 - Production on Sat Feb 10 22:12:27 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 "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=DATA_PUMP_DIR dumpfile=1.dmp logfile=1.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1.251 GB Processing object type SCHEMA_EXPORT/USER 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/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Processing object type SCHEMA_EXPORT/JOB Processing object type SCHEMA_EXPORT/REFRESH_GROUP . . exported "HR"."TEST121201" 153.4 MB 10000000 rows . . exported "HR"."TEST_3" 131.2 MB 9900001 rows . . exported "HR"."TEST0103" 113.4 MB 8000000 rows . . exported "HR"."TEST0123" 58.10 MB 3000000 rows . . exported "HR"."TABLE1" 5.984 KB 11 rows . . exported "HR"."TABLE2" 58.10 MB 2999998 rows . . exported "HR"."TEST0122" 41.92 MB 3000000 rows . . exported "HR"."T22" 19.22 MB 10000 rows . . exported "HR"."A" 16.35 MB 435819 rows . . exported "HR"."B" 16.35 MB 435820 rows . . exported "HR"."TEST_1" 14.61 MB 666666 rows . . exported "HR"."T1" 8.458 MB 87261 rows . . exported "HR"."TEST" 8.456 MB 87231 rows . . exported "HR"."TEST0309" 7.404 MB 87273 rows . . exported "HR"."TEST2" 8.456 MB 87231 rows . . exported "HR"."LIST_EXAMPLE_1":"PART01" 5.429 KB 1 rows . . exported "HR"."LIST_EXAMPLE_1":"PART03" 5.429 KB 1 rows . . exported "HR"."RANGE_EXAMPLE_1":"PART04" 5.851 KB 1 rows . . exported "HR"."T2":"Q1"."SYS_SUBP182" 385.6 KB 26785 rows . . exported "HR"."T2":"Q1"."SYS_SUBP183" 532.3 KB 37500 rows . . exported "HR"."T2":"Q2"."SYS_SUBP184" 523.8 KB 35715 rows . . exported "HR"."T2":"Q2"."SYS_SUBP185" 724.1 KB 50000 rows . . exported "HR"."TT_1" 6.717 MB 68467 rows . . exported "HR"."TEST012302" 4.187 MB 400000 rows . . exported "HR"."T" 3.633 MB 171270 rows . . exported "HR"."T_1" 4.132 MB 41779 rows . . exported "HR"."TEST1212" 2.209 MB 199998 rows . . exported "HR"."TEST_2" 1.460 MB 66666 rows . . exported "HR"."EMP_TEST" 582.0 KB 98 rows . . exported "HR"."TEST4" 152.1 KB 6666 rows . . exported "HR"."TEST1222" 151.7 KB 10000 rows . . exported "HR"."TEST3" 152.0 KB 6666 rows . . exported "HR"."EMPLOYEE" 102.8 KB 10000 rows . . exported "HR"."TEST1207" 5.468 KB 1 rows . . exported "HR"."BOOKS" 9.421 KB 1 rows . . exported "HR"."BUFFER_BUSY" 5.429 KB 2 rows . . exported "HR"."COUNTRIES" 6.367 KB 25 rows . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows . . exported "HR"."DEPT_80" 11.81 KB 34 rows . . exported "HR"."DEPT_80_1" 11.81 KB 34 rows . . exported "HR"."EMP" 8.523 KB 13 rows . . exported "HR"."EMPLOYEES" 16.81 KB 107 rows . . exported "HR"."EMP_DATA" 16.81 KB 107 rows . . exported "HR"."EMP_DATA2" 16.81 KB 107 rows . . exported "HR"."EMP_DATA_1130" 16.81 KB 107 rows . . exported "HR"."EXAMPLE_1" 5.453 KB 3 rows . . exported "HR"."EXAMPLE_2" 5.429 KB 1 rows . . exported "HR"."EXPTABLE" 18.00 KB 1000 rows . . exported "HR"."JOBS" 6.992 KB 19 rows . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows . . exported "HR"."JOIN_A_B_MV" 6.25 KB 29 rows . . exported "HR"."LOCATIONS" 8.273 KB 23 rows . . exported "HR"."MV1" 16.80 KB 107 rows . . exported "HR"."PERSONS" 9.312 KB 1 rows . . exported "HR"."PRINT_MEDIA" 5.453 KB 1 rows . . exported "HR"."REGIONS" 5.476 KB 4 rows . . exported "HR"."TEST012301" 6.109 KB 19 rows . . exported "HR"."TEST0301" 5.437 KB 1 rows . . exported "HR"."TEST0307" 20.93 KB 1000 rows . . exported "HR"."TEST1218" 7.859 KB 200 rows . . exported "HR"."TEST1219" 5.554 KB 12 rows . . exported "HR"."T_EMP" 8.554 KB 107 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q1_1998"."SYS_SUBP82" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q1_1999"."SYS_SUBP86" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q1_2000"."SYS_SUBP90" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_1998"."SYS_SUBP83" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_1999"."SYS_SUBP87" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP91" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP92" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP93" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP94" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP95" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP96" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP97" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q2_2000"."SYS_SUBP98" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_1998"."SYS_SUBP84" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_1999"."SYS_SUBP88" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_2000"."CH_C" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_2000"."CH_I" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_2000"."CH_P" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_2000"."CH_S" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q3_2000"."CH_T" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q4_1998"."SYS_SUBP85" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q4_1999"."SYS_SUBP89" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q4_2000"."SYS_SUBP100" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q4_2000"."SYS_SUBP101" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q4_2000"."SYS_SUBP102" 0 KB 0 rows . . exported "HR"."COMPOSITE_SALES":"SALES_Q4_2000"."SYS_SUBP99" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P1"."P1_EAST" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P1"."P1_OTHER" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P1"."P1_WEST" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P2"."P2_EAST" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P2"."P2_OTHER" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P2"."P2_WEST" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P3"."P3_EAST" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P3"."P3_OTHER" 0 KB 0 rows . . exported "HR"."CUSTOMERS_PART":"P3"."P3_WEST" 0 KB 0 rows . . exported "HR"."DEPENDENT" 0 KB 0 rows . . exported "HR"."EMPLOYEES_DEMO" 0 KB 0 rows . . exported "HR"."EXAMPLE_4" 0 KB 0 rows . . exported "HR"."EXM_1" 0 KB 0 rows . . exported "HR"."HASH_PRODUCTS":"SYS_P41" 0 KB 0 rows . . exported "HR"."HASH_PRODUCTS":"SYS_P42" 0 KB 0 rows . . exported "HR"."LATER" 0 KB 0 rows . . exported "HR"."LIST_1" 0 KB 0 rows . . exported "HR"."LIST_EXAMPLE":"PART02" 0 KB 0 rows . . exported "HR"."LIST_EXAMPLE_1":"PART02" 0 KB 0 rows . . exported "HR"."MLOG$_EMPLOYEES" 0 KB 0 rows . . exported "HR"."RANGE_EXAMPLE":"PART01" 0 KB 0 rows . . exported "HR"."RANGE_EXAMPLE":"PART02" 0 KB 0 rows . . exported "HR"."RANGE_EXAMPLE":"PART03" 0 KB 0 rows . . exported "HR"."RANGE_EXAMPLE_1":"PART01" 0 KB 0 rows . . exported "HR"."RANGE_EXAMPLE_1":"PART02" 0 KB 0 rows . . exported "HR"."RANGE_EXAMPLE_1":"PART03" 0 KB 0 rows . . exported "HR"."RECT_TAB" 0 KB 0 rows . . exported "HR"."TEST0108" 0 KB 0 rows . . exported "HR"."TEST0109" 0 KB 0 rows . . exported "HR"."TEST0110" 0 KB 0 rows . . exported "HR"."TEST0112" 0 KB 0 rows . . exported "HR"."TEST0226" 0 KB 0 rows . . exported "HR"."TEST120701" 0 KB 0 rows . . exported "HR"."TEST120702" 0 KB 0 rows . . exported "HR"."TEST1229" 0 KB 0 rows . . exported "HR"."TEST_1208" 0 KB 0 rows . . exported "HR"."TEST_1211" 0 KB 0 rows . . exported "HR"."TOMS" 0 KB 0 rows . . exported "HR"."TOMS1" 0 KB 0 rows . . exported "HR"."TT" 0 KB 0 rows Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HR.SYS_EXPORT_SCHEMA_01 is: /oracle/app/oracle/admin/mydb/dpdump/1.dmp Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Feb 10 22:15:16 2018 elapsed 0 00:02:45
第二步：获取dump文件的ddl和dml
这时的impdp不会真正的导入
[oracle@localhost ~]$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=1.dmp logfile=imp.log sqlfile=getsql.txt Import: Release 11.2.0.4.0 - Production on Sat Feb 10 22:16:04 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_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SYS"."SYS_SQL_FILE_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=1.dmp logfile=imp.log sqlfile=getsql.txt Processing object type SCHEMA_EXPORT/USER 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/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Processing object type SCHEMA_EXPORT/JOB Processing object type SCHEMA_EXPORT/REFRESH_GROUP Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Sat Feb 10 22:16:14 2018 elapsed 0 00:00:08
第三步：根据命令，查找出导出direction的路径，然后跳转至该路径：
SQL> col OWNER for a6 SQL> col DIRECTORY_NAME for a30 SQL> col DIRECTORY_PATH for a80 SQL> select * from dba_directories; OWNER DIRECTORY_NAME DIRECTORY_PATH ------ ------------------------------ -------------------------------------------------------------------------------- SYS SUBDIR /oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep SYS SS_OE_XMLDIR /oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/ SYS BFILE_DIR /oracle/test/ SYS LOG_FILE_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/ SYS MEDIA_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/ SYS DATA_FILE_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/ SYS XMLDIR /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/localhost.localdomain/state SYS DATA_PUMP_DIR /oracle/app/oracle/admin/mydb/dpdump/ SYS ORACLE_OCM_CONFIG_DIR2 /oracle/app/oracle/product/11.2.0/dbhome_1/ccr/state 10 rows selected. [oracle@localhost ~]$ cd /oracle/app/oracle/admin/mydb/dpdump/ [oracle@localhost dpdump]$ ls 1.dmp 1.log dp.log getsql.txt imp.log import.log
第四步、查看生成的txt
[oracle@localhost dpdump]$ more getsql.txt -- CONNECT SYS ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/USER -- CONNECT SYSTEM CREATE USER "HR" IDENTIFIED BY VALUES 'S:6589ECB449396797439193B98DA03EEA7517E9EE6CAB8DCA45DC94D97533;8811D693B708D1E5' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT GLOBAL QUERY REWRITE TO "HR"; GRANT QUERY REWRITE TO "HR"; GRANT CREATE MATERIALIZED VIEW TO "HR"; GRANT CREATE PUBLIC DATABASE LINK TO "HR"; GRANT CREATE DATABASE LINK TO "HR"; GRANT CREATE SEQUENCE TO "HR"; GRANT CREATE VIEW TO "HR"; GRANT CREATE SYNONYM TO "HR"; GRANT CREATE ANY TABLE TO "HR"; GRANT UNLIMITED TABLESPACE TO "HR"; GRANT ALTER SESSION TO "HR"; GRANT CREATE SESSION TO "HR" WITH ADMIN OPTION; -- new object type path: SCHEMA_EXPORT/ROLE_GRANT GRANT "RESOURCE" TO "HR"; GRANT "DBA" TO "HR"; -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE ALTER USER "HR" DEFAULT ROLE ALL; -- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA -- CONNECT HR BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'MYDB', inst_scn=>'13718681'); COMMIT; END; / -- new object type path: SCHEMA_EXPORT/TYPE/TYPE_SPEC -- CONNECT SYS ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE; CREATE TYPE "HR"."PERSON_T" OID '5EA32C5CD3A616C9E055000000000001' AS OBJECT (name VARCHAR2(100), ssn NUMBER) NOT FINAL; / ALTER TYPE "HR"."PERSON_T" COMPILE SPECIFICATION PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED PLSQL_DEBUG= FALSE PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE' / ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE; ………………………… 更多内容不展示在这
从create user "HR"可以看出，该dmp文件的schema为hr。
