服务器是Oracle 11g 11.2.0.4.0 我的 Oracle 的本地实例是"Oracle Database 18c Express Edition Release 18.0.0.0.0">
我想导出名为"AlphaTest"的架构及其所有相关的触发器、表、视图、包等
并将其导入我的本地实例。
我没有两个可用的数据库，所以我会尝试在我的本地 11gXE 上执行此操作。
首先，以特权用户 (SYS( 身份连接，检查我拥有哪些目录，并向我要导出和导入的用户授予所需的权限：
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> desc dba_directories
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
DIRECTORY_NAME NOT NULL VARCHAR2(30)
DIRECTORY_PATH VARCHAR2(4000)
SQL> col directory_name format a15
SQL> col directory_path format a60
SQL> select directory_name, directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
--------------- ------------------------------------------------------------
TEST_DIR c:
EXT_DIR c:temp
ORACLECLRDIR C:oraclexeapporacleproduct11.2.0serverbinclr
DATA_PUMP_DIR C:oraclexeapporacle/admin/xe/dpdump/
XMLDIR C:oraclexeapporacleproduct11.2.0serverrdbmsxml
ORACLE_OCM_CONF C:ADEaime_xe28oracle/ccr/state
IG_DIR
6 rows selected.
SQL> grant read, write on directory ext_dir to scott;
Grant succeeded.
SQL> grant read, write on directory ext_dir to mike;
Grant succeeded.
SQL>
如果我没有任何目录，我会将其创建为
SQL> create directory brisime_dir as 'c:temp';
Directory created.
SQL> grant read, write on directory brisime_Dir to scott;
Grant succeeded.
SQL>
并继续...
。出口：
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
C:Temp>expdp scott/tiger file=scott.dmp directory=ext_dir
Export: Release 11.2.0.2.0 - Production on Pon Vel 3 22:17:53 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=scott.dmp" Location: Command Line, Replaced with: "dumpfile=scott.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** dumpfile=scott.dmp directory=ext_dir reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."ABC" 6.867 KB 6 rows
. . exported "SCOTT"."DEPT" 5.929 KB 4 rows
. . exported "SCOTT"."DUMMY" 5.007 KB 1 rows
. . exported "SCOTT"."EMP" 8.562 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
C:TEMPSCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:18:02
C:Temp>
进口：
使用系统帐户执行它。REMAP_SCHEMA将为您创建一个new_user并执行导入：
C:Temp>impdp system/pwd file=scott.dmp directory=ext_dir remap_schema=scott:new_user
Import: Release 11.2.0.2.0 - Production on Pon Vel 3 22:25:38 2020
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=scott.dmp" Location: Command Line, Replaced with: "dumpfile=scott.dmp"
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=scott.dmp directory=ext_dir remap_schema=scott:new_user
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 "NEW_USER"."ABC" 6.867 KB 6 rows
. . imported "NEW_USER"."DEPT" 5.929 KB 4 rows
. . imported "NEW_USER"."DUMMY" 5.007 KB 1 rows
. . imported "NEW_USER"."EMP" 8.562 KB 14 rows
. . imported "NEW_USER"."SALGRADE" 5.859 KB 5 rows
. . imported "NEW_USER"."BONUS" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 22:25:41
C:Temp>
检查。。。
。是否一切都在那里：
C:Temp>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Pon Vel 3 22:30:14 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> alter user new_user identified by new_pwd;
User altered.
SQL> connect new_user/new_pwd
Connected.
SQL> select * From tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ABC TABLE
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE
V_EMP_DEPT VIEW
7 rows selected.
SQL>
似乎还可以。