The Server is Oracle 11g 11.2.0.4.0 The local instance of my Oracle is "Oracle Database 18c Express Edition Release 18.0.0.0.0"
I want to export the schema called "AlphaTest" and all it's associated triggers, tables, views, packages, etc
and import that to my local instance.
I don't have two databases available so I'll try to do it on my local 11gXE.
First, connect as privileged user (SYS), check which directories I have and grant required privileges to users I'll export & import:
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:\oraclexe\app\oracle\product\11.2.0\server\bin\clr
DATA_PUMP_DIR C:\oraclexe\app\oracle/admin/xe/dpdump/
XMLDIR C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml
ORACLE_OCM_CONF C:\ADE\aime_xe28\oracle/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>
If I didn't have any directory, I'd create it as
SQL> create directory brisime_dir as 'c:\temp';
Directory created.
SQL> grant read, write on directory brisime_Dir to scott;
Grant succeeded.
SQL>
and continue with ...
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:\TEMP\SCOTT.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:18:02
C:\Temp>
use system account to perform it. REMAP_SCHEMA will create a new_user for you and do the import:
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>
... whether everything is there:
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>
Seems to be OK.
